01_Feature_Generation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instacart\n",
    "\n",
    "This workbook about feature generation\n",
    "The generated fetaures are stored as csv files, so can be loaded from subseqent pages\n",
    "\n",
    "**TODO**\n",
    "Combine the feature generation for train and test.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.  Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loading prior\n",
      "loading train\n",
      "loading orders\n",
      "priors (32434489, 4): order_id, product_id, add_to_cart_order, reordered\n",
      "orders (3421083, 7): order_id, user_id, eval_set, order_number, order_dow, order_hour_of_day, days_since_prior_order\n",
      "train (1038783, 4): order_id, product_id, add_to_cart_order, reordered\n",
      "optimize memory\n"
     ]
    }
   ],
   "source": [
    "import numpy as np \n",
    "import pandas as pd \n",
    "import time\n",
    "from tqdm import tqdm\n",
    "\n",
    "import gc\n",
    "\n",
    "\n",
    "print('loading prior')\n",
    "priors = pd.read_csv('./data/order_products__prior.csv')\n",
    "print('loading train')\n",
    "\n",
    "train_all = pd.read_csv('./data/order_products__train.csv')\n",
    "## Have split the trian data into two sets, train and eval\n",
    "train = pd.read_csv('./data/train_new.csv')\n",
    "train_eval = pd.read_csv('./data/train_eval.csv')\n",
    "\n",
    "print('loading orders')\n",
    "orders = pd.read_csv(IDIR + 'orders.csv')\n",
    "\n",
    "print('priors {}: {}'.format(priors.shape, ', '.join(priors.columns)))\n",
    "print('orders {}: {}'.format(orders.shape, ', '.join(orders.columns)))\n",
    "print('train {}: {}'.format(train.shape, ', '.join(train.columns)))\n",
    "\n",
    "###\n",
    "# some memory measures for kaggle kernel\n",
    "print('optimize memory')\n",
    "orders.order_dow = orders.order_dow.astype(np.int8)\n",
    "orders.order_hour_of_day = orders.order_hour_of_day.astype(np.int8)\n",
    "orders.order_number = orders.order_number.astype(np.int16)\n",
    "orders.order_id = orders.order_id.astype(np.int32)\n",
    "orders.user_id = orders.user_id.astype(np.int32)\n",
    "orders.days_since_prior_order = orders.days_since_prior_order.astype(np.float32)\n",
    "\n",
    "\n",
    "train.reordered = train.reordered.astype(np.int8)\n",
    "train.add_to_cart_order = train.add_to_cart_order.astype(np.int16)\n",
    "\n",
    "train_eval.reordered = train.reordered.astype(np.int8)\n",
    "train_eval.add_to_cart_order = train.add_to_cart_order.astype(np.int16)\n",
    "\n",
    "train_all.reordered = train.reordered.astype(np.int8)\n",
    "train_all.add_to_cart_order = train.add_to_cart_order.astype(np.int16)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "priors.order_id = priors.order_id.astype(np.int32)\n",
    "priors.add_to_cart_order = priors.add_to_cart_order.astype(np.int16)\n",
    "priors.reordered = priors.reordered.astype(np.int8)\n",
    "priors.product_id = priors.product_id.astype(np.int32)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loading products\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print('loading products')\n",
    "\n",
    "\n",
    "products = pd.read_csv(IDIR + 'products.csv')\n",
    "\n",
    "products.drop(['product_name'], axis=1, inplace=True)\n",
    "products.aisle_id = products.aisle_id.astype(np.int8)\n",
    "products.department_id = products.department_id.astype(np.int8)\n",
    "products.product_id = products.product_id.astype(np.int32)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------------------------\n",
    "\n",
    "## 2. Prepare data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concat prior and train \n",
    "\n",
    "To be used for final predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(33473272, 4)\n",
      "concat successful\n"
     ]
    }
   ],
   "source": [
    "prior_train = pd.concat([priors, train_all], axis = 0)\n",
    "print prior_train.shape\n",
    "\n",
    "if ( priors.shape[0] + train.shape[0] ) ==  prior_train.shape[0]:\n",
    "    print \"concat successful\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join with orders table to get the user id\n",
    "\n",
    "This gets us the user_id and other order features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joining orders with prior_train\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>eval_set</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33120</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>202279</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28985</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>202279</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9327</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>202279</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45918</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>202279</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>30035</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>202279</td>\n",
       "      <td>prior</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id  add_to_cart_order  reordered  order_id  user_id eval_set  \\\n",
       "0       33120                  1          1         2   202279    prior   \n",
       "1       28985                  2          1         2   202279    prior   \n",
       "2        9327                  3          0         2   202279    prior   \n",
       "3       45918                  4          1         2   202279    prior   \n",
       "4       30035                  5          0         2   202279    prior   \n",
       "\n",
       "   order_number  order_dow  order_hour_of_day  days_since_prior_order  \n",
       "0             3          5                  9                     8.0  \n",
       "1             3          5                  9                     8.0  \n",
       "2             3          5                  9                     8.0  \n",
       "3             3          5                  9                     8.0  \n",
       "4             3          5                  9                     8.0  "
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## set the index, for the join\n",
    "orders.set_index('order_id', inplace=True, drop=False)\n",
    "\n",
    "## Join with prior_train\n",
    "print \"Joining orders with prior_train\"\n",
    "prior_train = prior_train.join(orders, on = 'order_id', how = 'left', lsuffix = '_')\n",
    "prior_train.drop('order_id_', inplace = True, axis = 1)\n",
    "\n",
    "## Repeat the same only for prior\n",
    "print \"Joining orders with priors\"\n",
    "priors = priors.join(orders, on = 'order_id', how = 'left', lsuffix = '_')\n",
    "priors.drop('order_id_', inplace = True, axis = 1)\n",
    "\n",
    "\n",
    "## Joining orders with train\n",
    "print \"Joining orders with train\"\n",
    "train = train.join(orders, on = 'order_id', how = 'left', lsuffix = '_')\n",
    "train.drop('order_id_', inplace = True, axis = 1)\n",
    "\n",
    "## Joining orders with train_eval\n",
    "print \"Joining orders with train_eval\"\n",
    "train_eval = train_eval.join(orders, on = 'order_id', how = 'left', lsuffix = '_')\n",
    "train_eval.drop('order_id_', inplace = True, axis = 1)\n",
    "\n",
    "\n",
    "## reset the order table index\n",
    "orders.reset_index(inplace=True, drop=True)\n",
    "orders.head()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make a data frame of user and previous product list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>prod_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>{17122, 196, 26405, 27845, 13032, 39657, 12427...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>{45066, 2573, 18961, 23, 32792, 22559, 12324, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>{17668, 39190, 44683, 21903, 14992, 21137, 324...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>{26576, 21573, 17769, 25623, 35469, 37646, 366...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>{11777, 40706, 48775, 20754, 28289, 6808, 1398...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id                                          prod_list\n",
       "0        1  {17122, 196, 26405, 27845, 13032, 39657, 12427...\n",
       "1        2  {45066, 2573, 18961, 23, 32792, 22559, 12324, ...\n",
       "2        3  {17668, 39190, 44683, 21903, 14992, 21137, 324...\n",
       "3        4  {26576, 21573, 17769, 25623, 35469, 37646, 366...\n",
       "4        5  {11777, 40706, 48775, 20754, 28289, 6808, 1398..."
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Using prior and train data\n",
    "users_prior_all = pd.DataFrame()\n",
    "users_prior_all['prod_list'] = prior_train.groupby('user_id')['product_id'].apply(set)\n",
    "users_prior_all.reset_index(inplace = True, drop = False)\n",
    "\n",
    "\n",
    "## Using only prior data\n",
    "users_prior = pd.DataFrame()\n",
    "users_prior['prod_list'] = priors.groupby('user_id')['product_id'].apply(set)\n",
    "users_prior.reset_index(inplace = True, drop = False)\n",
    "users_prior.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create train eval\n",
    "\n",
    "Keep 25% of the train data outside for cross validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nimport random\\ntrain.reset_index(drop = True, inplace = True)\\norder_ids = list(train['order_id'].unique())\\nsample_size = int(0.25 * len(order_ids))\\nsample_orders = random.sample(order_ids, sample_size)\\n\\ntrain_eval = train[train['order_id'].isin(sample_orders)]\\ntrain_new = train[~train['order_id'].isin(sample_orders)]\\n\\n\\ntrain_eval.to_csv('./data/train_eval.csv', index = False)\\ntrain_new.to_csv('./data/train_new.csv', index = False)\\n\\ndel train\\ntrain = train_new\\ndel train_new\\ntrain.head()\\n\\n\""
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "import random\n",
    "train.reset_index(drop = True, inplace = True)\n",
    "order_ids = list(train['order_id'].unique())\n",
    "sample_size = int(0.25 * len(order_ids))\n",
    "sample_orders = random.sample(order_ids, sample_size)\n",
    "\n",
    "train_eval = train[train['order_id'].isin(sample_orders)]\n",
    "train_new = train[~train['order_id'].isin(sample_orders)]\n",
    "\n",
    "\n",
    "train_eval.to_csv('./data/train_eval.csv', index = False)\n",
    "train_new.to_csv('./data/train_new.csv', index = False)\n",
    "\n",
    "del train\n",
    "train = train_new\n",
    "del train_new\n",
    "train.head()\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------------------------\n",
    "\n",
    "## 3.  Feature Generation\n",
    "\n",
    "*Features built *\n",
    "\n",
    "\n",
    "* Order features\n",
    "    * Current Order -  u'order_dow', u'order_hour_of_day', u'days_since_prior_order'\n",
    "    * Prev Order - u'prev_order_dow',u'prev_order_hour_of_day', u'prev_days_since_prior_order'\n",
    "\n",
    "* User Order Features\n",
    "    * u'avg_days_since_prior_order\n",
    "\n",
    "* Product Features\n",
    "    * u'aisle_id', u'department_id'\n",
    "    * u'p_orders', u'p_reorders', u'p_reorder_rate'\n",
    "\n",
    "* User Features\n",
    "    * u'tot_orders', u'tot_prods', u'avg_basket', u'avg_reorder', u'std_basket'\n",
    "\n",
    "* User Product Features\n",
    "    * u'up_orders', u'up_reorder', u'up_reorder_rate'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User Order features\n",
    "\n",
    "How much time on an average does a user leave between orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   user_id  avg_days_since_prior_order\n",
      "0        1                   20.259260\n",
      "1        2                   15.967033\n",
      "2        3                   11.487180\n",
      "3        4                   15.357142\n",
      "4        5                   14.500000\n",
      "\n",
      "   user_id  avg_days_since_prior_order\n",
      "0        1                   19.200001\n",
      "1        2                   18.009390\n",
      "2        3                   11.487180\n",
      "3        4                   15.357142\n",
      "4        5                   12.314285\n"
     ]
    }
   ],
   "source": [
    "## using prior stats\n",
    "\n",
    "user_order_prior = pd.DataFrame()\n",
    "user_order_prior['avg_days_since_prior_order'] = priors.groupby('user_id')['days_since_prior_order'].agg('mean')\n",
    "\n",
    "## using prior train stats\n",
    "user_order_all = pd.DataFrame()\n",
    "user_order_all['avg_days_since_prior_order'] = prior_train.groupby('user_id')['days_since_prior_order'].agg('mean')\n",
    "\n",
    "user_order_prior.reset_index(drop = False, inplace = True)\n",
    "user_order_all.reset_index(drop = False, inplace = True)\n",
    "\n",
    "print user_order_prior.head()\n",
    "print\n",
    "print user_order_all.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User id last order feature\n",
    "\n",
    "The last order of the user, fetaures of it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def last_order_features(priors):\n",
    "    \n",
    "    max_order = pd.DataFrame()\n",
    "    max_order['max_order'] = priors.groupby(['user_id'])['order_number'].agg('max')\n",
    "    max_order = max_order.rename(columns = {\"max_order\":\"order_number\"})\n",
    "    max_order.reset_index(inplace = True, drop = False)\n",
    "\n",
    "    max_order.set_index(['user_id','order_number'], drop = False, inplace = True)\n",
    "    priors.set_index(['user_id','order_number'], drop = False, inplace = True)\n",
    "\n",
    "    max_order = max_order.join(priors[['user_id', 'order_id', 'order_number','order_dow','order_hour_of_day','days_since_prior_order']], rsuffix=\"_\")\n",
    "\n",
    "    max_order.reset_index(drop =True, inplace = True)\n",
    "    priors.reset_index(drop = True, inplace = True)\n",
    "\n",
    "    max_order.drop('user_id_', inplace = True, axis =1)\n",
    "    max_order.drop('order_number_', inplace = True, axis = 1)\n",
    "    max_order.drop('order_number', inplace = True, axis = 1)\n",
    "\n",
    "\n",
    "    max_order = max_order.rename(columns = {\"order_id\":\"prev_order_id\",\"order_dow\":\"prev_order_dow\"\n",
    "                                           ,\"order_hour_of_day\":\"prev_order_hour_of_day\"\n",
    "                                           ,\"days_since_prior_order\":\"prev_days_since_prior_order\"})\n",
    "\n",
    "    max_order.drop_duplicates(inplace = True)\n",
    "    return max_order\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    user_id  prev_order_id  prev_order_dow  prev_order_hour_of_day  \\\n",
      "0         1        2550362               4                       8   \n",
      "9         2         839880               3                      10   \n",
      "25        3        1402502               1                      15   \n",
      "31        4        2557754               5                      13   \n",
      "34        5         157374               1                      18   \n",
      "\n",
      "    prev_days_since_prior_order  \n",
      "0                          30.0  \n",
      "9                          13.0  \n",
      "25                         15.0  \n",
      "31                          0.0  \n",
      "34                         19.0  \n",
      "\n",
      "    user_id  prev_order_id  prev_order_dow  prev_order_hour_of_day  \\\n",
      "0         1        1187899               4                       8   \n",
      "11        2        1492625               1                      11   \n",
      "42        3        1402502               1                      15   \n",
      "48        4        2557754               5                      13   \n",
      "51        5        2196797               0                      11   \n",
      "\n",
      "    prev_days_since_prior_order  \n",
      "0                          14.0  \n",
      "11                         30.0  \n",
      "42                         15.0  \n",
      "48                          0.0  \n",
      "51                          6.0  \n"
     ]
    }
   ],
   "source": [
    "## Stats from prior\n",
    "max_order = last_order_features(priors)\n",
    "print max_order.head()\n",
    "print\n",
    "## Stats from prior and train\n",
    "max_order_all = last_order_features(prior_train)\n",
    "print max_order_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Product Features\n",
    "\n",
    "Features of the product, including the number of orders in which the product has appeared, number of re-orders where it has appeared\n",
    "and reorder rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def product_features(priors):\n",
    "    \n",
    "    \n",
    "    prods = pd.DataFrame()\n",
    "    prods['p_orders'] = priors.groupby(priors.product_id).size().astype(np.float32)\n",
    "    prods['p_reorders'] = priors['reordered'].groupby(priors.product_id).sum().astype(np.float32)\n",
    "\n",
    "    prods['p_reorder_rate'] = (prods.p_reorders / prods.p_orders).astype(np.float32)\n",
    "\n",
    "    ## set the index for products\n",
    "    products.set_index('product_id', inplace = True, drop = False)\n",
    "    products_prior = products.join(prods, rsuffix=\"_\")\n",
    "    ## Reset the index\n",
    "    products_prior.reset_index(inplace = True, drop = True)\n",
    "    del prods\n",
    "    return products_prior\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   product_id  aisle_id  department_id  p_orders  p_reorders  p_reorder_rate\n",
      "0           1        61             19    1907.0      1171.0        0.614053\n",
      "1           2       104             13      93.0        13.0        0.139785\n",
      "2           3        94              7     282.0       208.0        0.737589\n",
      "3           4        38              1     344.0       157.0        0.456395\n",
      "4           5         5             13      16.0        10.0        0.625000\n",
      "\n",
      "   product_id  aisle_id  department_id  p_orders  p_reorders  p_reorder_rate\n",
      "0           1        61             19    1852.0      1136.0        0.613391\n",
      "1           2       104             13      90.0        12.0        0.133333\n",
      "2           3        94              7     277.0       203.0        0.732852\n",
      "3           4        38              1     329.0       147.0        0.446809\n",
      "4           5         5             13      15.0         9.0        0.600000\n"
     ]
    }
   ],
   "source": [
    "### Stats from prior and train\n",
    "products_all = product_features(prior_train)\n",
    "print products_all.head()\n",
    "print\n",
    "## Stats from prior\n",
    "products_prior = product_features(priors)\n",
    "print products_prior.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def user_features(priors):\n",
    "    prod_count_prior = pd.DataFrame()\n",
    "\n",
    "    prod_count_prior['basket_size'] = priors.groupby(['user_id','order_id'])['product_id'].size().astype(np.int32)\n",
    "    prod_count_prior['reorder_size'] = priors.groupby(['user_id','order_id'])['reordered'].agg('sum').astype(np.int32)\n",
    "\n",
    "    # reset / set index\n",
    "    prod_count_prior = prod_count_prior.reset_index()\n",
    "    prod_count_prior.set_index('user_id', inplace = True, drop =False)\n",
    "\n",
    "    prod_count_prior['tot_orders']  =  prod_count_prior.groupby(['user_id']).size().astype(np.int32)\n",
    "    prod_count_prior['tot_prods']   =  prod_count_prior.groupby(['user_id'])['basket_size'].agg(['sum'])\n",
    "    prod_count_prior['avg_basket']  =  prod_count_prior.groupby(['user_id'])['basket_size'].agg(['mean'])\n",
    "    prod_count_prior['avg_reorder'] =  prod_count_prior.groupby(['user_id'])['reorder_size'].agg(['mean'])\n",
    "    prod_count_prior['std_basket']  =  prod_count_prior.groupby(['user_id'])['basket_size'].agg(['std'])\n",
    "\n",
    "\n",
    "    prod_count_prior.drop(['order_id','basket_size','reorder_size'], inplace=True, axis=1)\n",
    "    prod_count_prior.drop_duplicates(inplace = True)\n",
    "    prod_count_prior = prod_count_prior.reset_index(level = 'user_id', drop = True)\n",
    "\n",
    "    return prod_count_prior\n",
    "  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Library/Python/2.7/site-packages/ipykernel-4.3.1-py2.7.egg/ipykernel/__main__.py:11: FutureWarning: 'user_id' is both a column name and an index level.\n",
      "Defaulting to column but this will raise an ambiguity error in a future version\n",
      "/Library/Python/2.7/site-packages/ipykernel-4.3.1-py2.7.egg/ipykernel/__main__.py:12: FutureWarning: 'user_id' is both a column name and an index level.\n",
      "Defaulting to column but this will raise an ambiguity error in a future version\n",
      "/Library/Python/2.7/site-packages/ipykernel-4.3.1-py2.7.egg/ipykernel/__main__.py:13: FutureWarning: 'user_id' is both a column name and an index level.\n",
      "Defaulting to column but this will raise an ambiguity error in a future version\n",
      "/Library/Python/2.7/site-packages/ipykernel-4.3.1-py2.7.egg/ipykernel/__main__.py:14: FutureWarning: 'user_id' is both a column name and an index level.\n",
      "Defaulting to column but this will raise an ambiguity error in a future version\n",
      "/Library/Python/2.7/site-packages/ipykernel-4.3.1-py2.7.egg/ipykernel/__main__.py:15: FutureWarning: 'user_id' is both a column name and an index level.\n",
      "Defaulting to column but this will raise an ambiguity error in a future version\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   user_id  tot_orders  tot_prods  avg_basket  avg_reorder  std_basket\n",
      "0        1          10         59    5.900000     4.100000    1.523884\n",
      "1        2          14        195   13.928571     6.642857    5.717238\n",
      "2        3          12         88    7.333333     4.583333    2.103388\n",
      "3        4           5         18    3.600000     0.200000    2.073644\n",
      "4        5           4         37    9.250000     3.500000    3.095696\n",
      "\n",
      "   user_id  tot_orders  tot_prods  avg_basket  avg_reorder  std_basket\n",
      "0        1          11         70    6.363636     4.636364    2.110579\n",
      "1        2          15        226   15.066667     7.000000    7.055562\n",
      "2        3          12         88    7.333333     4.583333    2.103388\n",
      "3        4           5         18    3.600000     0.200000    2.073644\n",
      "4        5           5         46    9.200000     3.600000    2.683282\n"
     ]
    }
   ],
   "source": [
    "## Stats from only prior\n",
    "prod_count_prior = user_features(priors)\n",
    "## Stats from all\n",
    "prod_count_all = user_features(prior_train)\n",
    "\n",
    "print prod_count_prior.head()\n",
    "print\n",
    "print prod_count_all.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User Product Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def user_prod_features(priors):\n",
    "    user_prod_prior = pd.DataFrame()\n",
    "\n",
    "    ## Number of user's order where product id is present\n",
    "    user_prod_prior['up_orders'] = priors.groupby(['user_id','product_id'])['order_id'].size()\n",
    "\n",
    "    user_prod_prior.reset_index(inplace = True, drop = False)\n",
    "    user_prod_prior.set_index(['user_id', 'product_id'], inplace = True, drop = False)\n",
    "\n",
    "    ## Number of times the product was re-ordered by the user\n",
    "    user_prod_prior['up_reorder']      = priors.groupby(['user_id', 'product_id'])['reordered'].agg(['sum'])\n",
    "    user_prod_prior['up_reorder_rate'] = user_prod_prior.up_reorder / user_prod_prior.up_orders\n",
    "\n",
    "    user_prod_prior.reset_index(drop = True, inplace= True)\n",
    "    return user_prod_prior\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   user_id  product_id  up_orders  up_reorder  up_reorder_rate\n",
      "0        1         196         10           9         0.900000\n",
      "1        1       10258          9           8         0.888889\n",
      "2        1       10326          1           0         0.000000\n",
      "3        1       12427         10           9         0.900000\n",
      "4        1       13032          3           2         0.666667\n",
      "\n",
      "   user_id  product_id  up_orders  up_reorder  up_reorder_rate\n",
      "0        1         196         11          10         0.909091\n",
      "1        1       10258         10           9         0.900000\n",
      "2        1       10326          1           0         0.000000\n",
      "3        1       12427         10           9         0.900000\n",
      "4        1       13032          4           3         0.750000\n"
     ]
    }
   ],
   "source": [
    "## Stats from prior\n",
    "\n",
    "user_prod_prior = user_prod_features(priors)\n",
    "user_prod_all   = user_prod_features(prior_train)\n",
    "\n",
    "\n",
    "print user_prod_prior.head()\n",
    "print\n",
    "print user_prod_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Product days since last ordered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a feature dict\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "feature_dict_prior = {}\n",
    "\n",
    "feature_dict_prior[1] = {\"name\":\"user_order_feature\",\"obj\":user_order_prior,\"index\":['user_id']}\n",
    "feature_dict_prior[2] = {\"name\":\"last_order_feature\",\"obj\":max_order,\"index\":['user_id']}\n",
    "feature_dict_prior[3] = {\"name\":\"product_feature\",\"obj\":products_prior,\"index\":['product_id']}\n",
    "feature_dict_prior[4] = {\"name\":\"user_feature\",\"obj\":prod_count_prior,\"index\":['user_id']}\n",
    "feature_dict_prior[5] = {\"name\":\"user_pro_feature\",\"obj\":user_prod_prior,\"index\":['user_id','product_id']}\n",
    "\n",
    "feature_dict_all = {}\n",
    "\n",
    "feature_dict_all[1] = {\"name\":\"user_order_feature\",\"obj\":user_order_all,\"index\":['user_id']}\n",
    "feature_dict_all[2] = {\"name\":\"last_order_feature\",\"obj\":max_order_all,\"index\":['user_id']}\n",
    "feature_dict_all[3] = {\"name\":\"product_feature\",\"obj\":products_all,\"index\":['product_id']}\n",
    "feature_dict_all[4] = {\"name\":\"user_feature\",\"obj\":prod_count_all,\"index\":['user_id']}\n",
    "feature_dict_all[5] = {\"name\":\"user_prod_feature\",\"obj\":user_prod_all,\"index\":['user_id','product_id']}\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----------------------------------------\n",
    "\n",
    "## 4. Join the features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def join_features(feature_dict, features):\n",
    "    \n",
    "    for k,v in feature_dict.items():\n",
    "        print \"Joining {} feature\".format(v['name'])\n",
    "        obj = v['obj']\n",
    "        index = v['index']\n",
    "\n",
    "        features.set_index(index, drop = False, inplace = True)\n",
    "        obj.set_index(index, drop = False, inplace = True)\n",
    "\n",
    "        features = features.join(obj ,on =index, rsuffix='_')\n",
    "        index_ = [idx + '_' for idx in index]\n",
    "        features.drop(index_, inplace = True, axis = 1)\n",
    "\n",
    "        features.reset_index(drop = True, inplace = True)\n",
    "        obj.reset_index(drop = True, inplace = True)\n",
    "        \n",
    "    features.drop( ['prev_order_id'], inplace = True, axis = 1 )\n",
    "\n",
    "\n",
    "    return features\n",
    "    \n",
    "        \n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>49302</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>11109</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10246</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>49683</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>43633</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  product_id  add_to_cart_order  reordered\n",
       "0         1       49302                  1          1\n",
       "1         1       11109                  2          1\n",
       "2         1       10246                  3          0\n",
       "3         1       49683                  4          0\n",
       "4         1       43633                  5          1"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Join train\n",
    "\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Prepare Y Variable from train\n",
    "\n",
    "\n",
    "* Here we use the train table as our y variable. We create a dataset with user_id, product_id, is_product_in_train columns.\n",
    "\n",
    "* We can then use this to merge with user, product and user/product features.\n",
    "\n",
    "* This will serve as our testing data before use actually run our model on real data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "features\n",
      "Finished 10000 users\n",
      "Finished 20000 users\n",
      "Finished 30000 users\n",
      "Finished 40000 users\n",
      "Finished 50000 users\n",
      "Finished 60000 users\n",
      "Finished 70000 users\n",
      "Finished 80000 users\n",
      "Finished 90000 users\n",
      "   in_next_order  order_id  product_id  user_id\n",
      "0              0   1187899       17122        1\n",
      "1              1   1187899         196        1\n",
      "2              1   1187899       26405        1\n",
      "3              1   1187899       13032        1\n",
      "4              1   1187899       39657        1\n"
     ]
    }
   ],
   "source": [
    "## This block needs to be run only once\n",
    "## Later the output of this block is stored in ./data/features.csv file\n",
    "## The next block reads the file, it enough to run the next block subsequently\n",
    "\n",
    "## We could have got this from order_id\n",
    "## However since we have separted our train into two sets\n",
    "## we need to interate the train_new aka train data we have created.\n",
    "\n",
    "\n",
    "train.reset_index(inplace = True, drop = True)\n",
    "\n",
    "train_list = pd.DataFrame()\n",
    "train_list['ignore'] = train.groupby(['user_id','order_id'], group_keys = True).size()\n",
    "train_list.reset_index(inplace = True, drop = False)\n",
    "\n",
    "\n",
    "\n",
    "train.set_index(['order_id', 'product_id'], inplace = True, drop = False)\n",
    "\n",
    "\n",
    "print \"features\"\n",
    "count = 0\n",
    "\n",
    "order_list = []\n",
    "product_list  = []\n",
    "user_list = []\n",
    "labels = []\n",
    "\n",
    "for user_record  in train_list.itertuples():\n",
    "    count+=1\n",
    "    if count%10000 == 0:\n",
    "        print \"Finished {} users\".format(count)\n",
    "\n",
    "    user_id  = user_record.user_id\n",
    "    order_id = user_record.order_id \n",
    "    prev_products = list(users_prior[users_prior.user_id == user_id]['prod_list'].values.tolist()[0])\n",
    "    product_list+= prev_products\n",
    "    order_list+=[order_id] * len(prev_products)\n",
    "    user_list+=[user_id] * len(prev_products)\n",
    "    labels+=[(order_id, product) in train.index for product in prev_products]\n",
    "    \n",
    "\n",
    "\n",
    "feature_df = pd.DataFrame({'user_id':user_list,'product_id':product_list,'order_id':order_list,'in_next_order':labels}, dtype=np.int32)\n",
    "\n",
    "print feature_df.head()\n",
    "\n",
    "feature_df.to_csv('./features/features.csv', index = False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>in_next_order</th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1187899</td>\n",
       "      <td>17122</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>196</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>26405</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>13032</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>39657</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   in_next_order  order_id  product_id  user_id\n",
       "0              0   1187899       17122        1\n",
       "1              1   1187899         196        1\n",
       "2              1   1187899       26405        1\n",
       "3              1   1187899       13032        1\n",
       "4              1   1187899       39657        1"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features = pd.read_csv('./features/features.csv')\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Order Features\n",
    "\n",
    "Attributes of the order we are about to predict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Order features\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>in_next_order</th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1187899</td>\n",
       "      <td>17122</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>196</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>26405</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>13032</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>39657</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   in_next_order  order_id  product_id  user_id  order_dow  order_hour_of_day  \\\n",
       "0              0   1187899       17122        1          4                  8   \n",
       "1              1   1187899         196        1          4                  8   \n",
       "2              1   1187899       26405        1          4                  8   \n",
       "3              1   1187899       13032        1          4                  8   \n",
       "4              1   1187899       39657        1          4                  8   \n",
       "\n",
       "   days_since_prior_order  \n",
       "0                    14.0  \n",
       "1                    14.0  \n",
       "2                    14.0  \n",
       "3                    14.0  \n",
       "4                    14.0  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print \"Order features\"\n",
    "\n",
    "features.set_index('order_id',inplace = True, drop = False)\n",
    "orders.set_index('order_id', inplace = True, drop = False)\n",
    "\n",
    "features = pd.merge(features, orders, left_on = 'order_id', right_on = 'order_id')\n",
    "\n",
    "#features.drop('order_id', inplace = True, axis =1)\n",
    "features.drop('eval_set', inplace = True, axis =1)\n",
    "features.drop('user_id_y', inplace = True, axis =1)\n",
    "features.drop('order_number', inplace = True, axis =1)\n",
    "\n",
    "\n",
    "\n",
    "features = features.rename(columns={\"user_id_x\":\"user_id\"})\n",
    "\n",
    "\n",
    "features.reset_index(drop = True, inplace= True)\n",
    "train.reset_index(drop = True, inplace = True)\n",
    "\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joining user_order_feature feature\n",
      "Joining last_order_feature feature\n",
      "Joining product_feature feature\n",
      "Joining user_feature feature\n",
      "Joining user_pro_feature feature\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>in_next_order</th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>avg_days_since_prior_order</th>\n",
       "      <th>prev_order_dow</th>\n",
       "      <th>prev_order_hour_of_day</th>\n",
       "      <th>...</th>\n",
       "      <th>p_reorders</th>\n",
       "      <th>p_reorder_rate</th>\n",
       "      <th>tot_orders</th>\n",
       "      <th>tot_prods</th>\n",
       "      <th>avg_basket</th>\n",
       "      <th>avg_reorder</th>\n",
       "      <th>std_basket</th>\n",
       "      <th>up_orders</th>\n",
       "      <th>up_reorder</th>\n",
       "      <th>up_reorder_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1187899</td>\n",
       "      <td>17122</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.25926</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>9377.0</td>\n",
       "      <td>0.675576</td>\n",
       "      <td>10</td>\n",
       "      <td>59</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.523884</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>196</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.25926</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>27791.0</td>\n",
       "      <td>0.776480</td>\n",
       "      <td>10</td>\n",
       "      <td>59</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.523884</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>0.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>26405</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.25926</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>536.0</td>\n",
       "      <td>0.441516</td>\n",
       "      <td>10</td>\n",
       "      <td>59</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.523884</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>13032</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.25926</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>2465.0</td>\n",
       "      <td>0.657158</td>\n",
       "      <td>10</td>\n",
       "      <td>59</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.523884</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1187899</td>\n",
       "      <td>39657</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>20.25926</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>3846.0</td>\n",
       "      <td>0.766288</td>\n",
       "      <td>10</td>\n",
       "      <td>59</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>1.523884</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   in_next_order  order_id  product_id  user_id  order_dow  order_hour_of_day  \\\n",
       "0              0   1187899       17122        1          4                  8   \n",
       "1              1   1187899         196        1          4                  8   \n",
       "2              1   1187899       26405        1          4                  8   \n",
       "3              1   1187899       13032        1          4                  8   \n",
       "4              1   1187899       39657        1          4                  8   \n",
       "\n",
       "   days_since_prior_order  avg_days_since_prior_order  prev_order_dow  \\\n",
       "0                    14.0                    20.25926               4   \n",
       "1                    14.0                    20.25926               4   \n",
       "2                    14.0                    20.25926               4   \n",
       "3                    14.0                    20.25926               4   \n",
       "4                    14.0                    20.25926               4   \n",
       "\n",
       "   prev_order_hour_of_day       ...         p_reorders  p_reorder_rate  \\\n",
       "0                       8       ...             9377.0        0.675576   \n",
       "1                       8       ...            27791.0        0.776480   \n",
       "2                       8       ...              536.0        0.441516   \n",
       "3                       8       ...             2465.0        0.657158   \n",
       "4                       8       ...             3846.0        0.766288   \n",
       "\n",
       "   tot_orders  tot_prods  avg_basket  avg_reorder  std_basket  up_orders  \\\n",
       "0          10         59         5.9          4.1    1.523884          1   \n",
       "1          10         59         5.9          4.1    1.523884         10   \n",
       "2          10         59         5.9          4.1    1.523884          2   \n",
       "3          10         59         5.9          4.1    1.523884          3   \n",
       "4          10         59         5.9          4.1    1.523884          1   \n",
       "\n",
       "   up_reorder  up_reorder_rate  \n",
       "0           0         0.000000  \n",
       "1           9         0.900000  \n",
       "2           1         0.500000  \n",
       "3           2         0.666667  \n",
       "4           0         0.000000  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features = join_features(feature_dict_prior, features)\n",
    "features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "features.to_csv('./features/features_train.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare y variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_y(test_list, users_prior):\n",
    "    \n",
    "    feature = []\n",
    "    count = 0\n",
    "    for user_record in (test_list.itertuples()):\n",
    "        count+=1\n",
    "        if count%10000 == 0:\n",
    "            print \"Finished {} users\".format(count)\n",
    "        user_id = user_record.user_id\n",
    "        order_id = user_record.order_id \n",
    "        prev_products = list(users_prior[users_prior.user_id == user_id]['prod_list'].values.tolist()[0])\n",
    "\n",
    "        for p_p in prev_products:\n",
    "            feature.append((order_id, user_id ,p_p))\n",
    "\n",
    "    test_df = pd.DataFrame(data = feature, columns =['order_id','user_id','product_id'])\n",
    "    \n",
    "    \n",
    "    return test_df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>eval_set</th>\n",
       "      <th>order_number</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>27104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21174</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>41860</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38273</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47209</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>prior</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id  add_to_cart_order  reordered  order_id  user_id eval_set  \\\n",
       "0       27104                  1          1   2298068       13    prior   \n",
       "1       21174                  2          1   2298068       13    prior   \n",
       "2       41860                  3          0   2298068       13    prior   \n",
       "3       38273                  4          0   2298068       13    prior   \n",
       "4       47209                  5          1   2298068       13    prior   \n",
       "\n",
       "   order_number  order_dow  order_hour_of_day  days_since_prior_order  \n",
       "0            10          6                 22                     7.0  \n",
       "1            10          6                 22                     7.0  \n",
       "2            10          6                 22                     7.0  \n",
       "3            10          6                 22                     7.0  \n",
       "4            10          6                 22                     7.0  "
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_eval.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare Y Varible from eval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished 10000 users\n",
      "Finished 20000 users\n",
      "Finished 30000 users\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>41351</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>41480</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>37385</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>31372</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>42125</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  user_id  product_id  order_dow  order_hour_of_day  \\\n",
       "0   2298068       13       41351          6                 22   \n",
       "1   2298068       13       41480          6                 22   \n",
       "2   2298068       13       37385          6                 22   \n",
       "3   2298068       13       31372          6                 22   \n",
       "4   2298068       13       42125          6                 22   \n",
       "\n",
       "   days_since_prior_order  \n",
       "0                     7.0  \n",
       "1                     7.0  \n",
       "2                     7.0  \n",
       "3                     7.0  \n",
       "4                     7.0  "
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_eval.reset_index(inplace = True, drop = True)\n",
    "\n",
    "train_eval_list = pd.DataFrame()\n",
    "train_eval_list['ignore'] = train_eval.groupby(['user_id','order_id'], group_keys = True).size()\n",
    "train_eval_list.reset_index(inplace = True, drop = False)\n",
    "\n",
    "\n",
    "test_df = get_y(train_eval_list, users_prior, orders)\n",
    "test_df.head()\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joining user_order_feature feature\n",
      "Joining last_order_feature feature\n",
      "Joining product_feature feature\n",
      "Joining user_feature feature\n",
      "Joining user_pro_feature feature\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>avg_days_since_prior_order</th>\n",
       "      <th>prev_order_dow</th>\n",
       "      <th>prev_order_hour_of_day</th>\n",
       "      <th>prev_days_since_prior_order</th>\n",
       "      <th>...</th>\n",
       "      <th>p_reorders</th>\n",
       "      <th>p_reorder_rate</th>\n",
       "      <th>tot_orders</th>\n",
       "      <th>tot_prods</th>\n",
       "      <th>avg_basket</th>\n",
       "      <th>avg_reorder</th>\n",
       "      <th>std_basket</th>\n",
       "      <th>up_orders</th>\n",
       "      <th>up_reorder</th>\n",
       "      <th>up_reorder_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>41351</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.381579</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2074.0</td>\n",
       "      <td>0.520844</td>\n",
       "      <td>12</td>\n",
       "      <td>81</td>\n",
       "      <td>6.75</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>2.005674</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>41480</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.381579</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>519.0</td>\n",
       "      <td>0.428571</td>\n",
       "      <td>12</td>\n",
       "      <td>81</td>\n",
       "      <td>6.75</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>2.005674</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>37385</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.381579</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>451.0</td>\n",
       "      <td>0.513083</td>\n",
       "      <td>12</td>\n",
       "      <td>81</td>\n",
       "      <td>6.75</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>2.005674</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>31372</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.381579</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>541.0</td>\n",
       "      <td>0.242601</td>\n",
       "      <td>12</td>\n",
       "      <td>81</td>\n",
       "      <td>6.75</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>2.005674</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2298068</td>\n",
       "      <td>13</td>\n",
       "      <td>42125</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.381579</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.468085</td>\n",
       "      <td>12</td>\n",
       "      <td>81</td>\n",
       "      <td>6.75</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>2.005674</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  user_id  product_id  order_dow  order_hour_of_day  \\\n",
       "0   2298068       13       41351          6                 22   \n",
       "1   2298068       13       41480          6                 22   \n",
       "2   2298068       13       37385          6                 22   \n",
       "3   2298068       13       31372          6                 22   \n",
       "4   2298068       13       42125          6                 22   \n",
       "\n",
       "   days_since_prior_order  avg_days_since_prior_order  prev_order_dow  \\\n",
       "0                     7.0                    7.381579               6   \n",
       "1                     7.0                    7.381579               6   \n",
       "2                     7.0                    7.381579               6   \n",
       "3                     7.0                    7.381579               6   \n",
       "4                     7.0                    7.381579               6   \n",
       "\n",
       "   prev_order_hour_of_day  prev_days_since_prior_order       ...         \\\n",
       "0                      13                          6.0       ...          \n",
       "1                      13                          6.0       ...          \n",
       "2                      13                          6.0       ...          \n",
       "3                      13                          6.0       ...          \n",
       "4                      13                          6.0       ...          \n",
       "\n",
       "   p_reorders  p_reorder_rate  tot_orders  tot_prods  avg_basket  avg_reorder  \\\n",
       "0      2074.0        0.520844          12         81        6.75     4.333333   \n",
       "1       519.0        0.428571          12         81        6.75     4.333333   \n",
       "2       451.0        0.513083          12         81        6.75     4.333333   \n",
       "3       541.0        0.242601          12         81        6.75     4.333333   \n",
       "4        22.0        0.468085          12         81        6.75     4.333333   \n",
       "\n",
       "   std_basket  up_orders  up_reorder  up_reorder_rate  \n",
       "0    2.005674          2           1              0.5  \n",
       "1    2.005674          1           0              0.0  \n",
       "2    2.005674          1           0              0.0  \n",
       "3    2.005674          1           0              0.0  \n",
       "4    2.005674          1           0              0.0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df = join_features(feature_dict_prior, test_df)\n",
    "test_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_df.to_csv('./features/features_eval.csv',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Prepare Y Variable from test\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished 10000 users\n",
      "Finished 20000 users\n",
      "Finished 30000 users\n",
      "Finished 40000 users\n",
      "Finished 50000 users\n",
      "Finished 60000 users\n",
      "Finished 70000 users\n",
      "   order_id  user_id  product_id\n",
      "0   2774568        3       17668\n",
      "1   2774568        3       39190\n",
      "2   2774568        3       44683\n",
      "3   2774568        3       21903\n",
      "4   2774568        3       14992\n"
     ]
    }
   ],
   "source": [
    "test_list = orders[orders.eval_set == 'test']\n",
    "\n",
    "feature = []\n",
    "count = 0\n",
    "for user_record in (test_list.itertuples()):\n",
    "\tcount+=1\n",
    "\tif count%10000 == 0:\n",
    "\t\tprint \"Finished {} users\".format(count)\n",
    "\tuser_id = user_record.user_id\n",
    "\torder_id = user_record.order_id \n",
    "\tprev_products = list(users_prior[users_prior.user_id == user_id]['prod_list'].values.tolist()[0])\n",
    "\n",
    "\tfor p_p in prev_products:\n",
    "\t\tfeature.append((order_id, user_id ,p_p))\n",
    "\n",
    "test_df = pd.DataFrame(data = feature, columns =['order_id','user_id','product_id'])\n",
    "\n",
    "print test_df.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Order Feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Order features\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>17668</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>39190</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>44683</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>21903</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>14992</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  user_id  product_id  order_dow  order_hour_of_day  \\\n",
       "0   2774568        3       17668          5                 15   \n",
       "1   2774568        3       39190          5                 15   \n",
       "2   2774568        3       44683          5                 15   \n",
       "3   2774568        3       21903          5                 15   \n",
       "4   2774568        3       14992          5                 15   \n",
       "\n",
       "   days_since_prior_order  \n",
       "0                    11.0  \n",
       "1                    11.0  \n",
       "2                    11.0  \n",
       "3                    11.0  \n",
       "4                    11.0  "
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Order features\n",
    "\n",
    "print \"Order features\"\n",
    "\n",
    "test_df.set_index('order_id',inplace = True, drop = False)\n",
    "orders.set_index('order_id', inplace = True, drop = False)\n",
    "\n",
    "test_df = pd.merge(test_df, orders, left_on = 'order_id', right_on = 'order_id')\n",
    "\n",
    "test_df.drop('eval_set', inplace = True, axis =1)\n",
    "test_df.drop('user_id_y', inplace = True, axis =1)\n",
    "test_df.drop('order_number', inplace = True, axis =1)\n",
    "\n",
    "\n",
    "\n",
    "test_df = test_df.rename(columns={\"user_id_x\":\"user_id\"})\n",
    "\n",
    "\n",
    "test_df.reset_index(drop = True, inplace= True)\n",
    "train.reset_index(drop = True, inplace = True)\n",
    "\n",
    "test_df.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Joining user_order_feature feature\n",
      "Joining last_order_feature feature\n",
      "Joining product_feature feature\n",
      "Joining user_feature feature\n",
      "Joining user_prod_feature feature\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>avg_days_since_prior_order</th>\n",
       "      <th>prev_order_dow</th>\n",
       "      <th>prev_order_hour_of_day</th>\n",
       "      <th>prev_days_since_prior_order</th>\n",
       "      <th>...</th>\n",
       "      <th>p_reorders</th>\n",
       "      <th>p_reorder_rate</th>\n",
       "      <th>tot_orders</th>\n",
       "      <th>tot_prods</th>\n",
       "      <th>avg_basket</th>\n",
       "      <th>avg_reorder</th>\n",
       "      <th>std_basket</th>\n",
       "      <th>up_orders</th>\n",
       "      <th>up_reorder</th>\n",
       "      <th>up_reorder_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>17668</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.48718</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1269.0</td>\n",
       "      <td>0.577869</td>\n",
       "      <td>12</td>\n",
       "      <td>88</td>\n",
       "      <td>7.333333</td>\n",
       "      <td>4.583333</td>\n",
       "      <td>2.103388</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>39190</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.48718</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6626.0</td>\n",
       "      <td>0.576776</td>\n",
       "      <td>12</td>\n",
       "      <td>88</td>\n",
       "      <td>7.333333</td>\n",
       "      <td>4.583333</td>\n",
       "      <td>2.103388</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>0.900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>44683</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.48718</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12631.0</td>\n",
       "      <td>0.539233</td>\n",
       "      <td>12</td>\n",
       "      <td>88</td>\n",
       "      <td>7.333333</td>\n",
       "      <td>4.583333</td>\n",
       "      <td>2.103388</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>21903</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.48718</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>194939.0</td>\n",
       "      <td>0.774474</td>\n",
       "      <td>12</td>\n",
       "      <td>88</td>\n",
       "      <td>7.333333</td>\n",
       "      <td>4.583333</td>\n",
       "      <td>2.103388</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>0.875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>14992</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.48718</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>...</td>\n",
       "      <td>17639.0</td>\n",
       "      <td>0.583513</td>\n",
       "      <td>12</td>\n",
       "      <td>88</td>\n",
       "      <td>7.333333</td>\n",
       "      <td>4.583333</td>\n",
       "      <td>2.103388</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  user_id  product_id  order_dow  order_hour_of_day  \\\n",
       "0   2774568        3       17668          5                 15   \n",
       "1   2774568        3       39190          5                 15   \n",
       "2   2774568        3       44683          5                 15   \n",
       "3   2774568        3       21903          5                 15   \n",
       "4   2774568        3       14992          5                 15   \n",
       "\n",
       "   days_since_prior_order  avg_days_since_prior_order  prev_order_dow  \\\n",
       "0                    11.0                    11.48718               1   \n",
       "1                    11.0                    11.48718               1   \n",
       "2                    11.0                    11.48718               1   \n",
       "3                    11.0                    11.48718               1   \n",
       "4                    11.0                    11.48718               1   \n",
       "\n",
       "   prev_order_hour_of_day  prev_days_since_prior_order       ...         \\\n",
       "0                      15                         15.0       ...          \n",
       "1                      15                         15.0       ...          \n",
       "2                      15                         15.0       ...          \n",
       "3                      15                         15.0       ...          \n",
       "4                      15                         15.0       ...          \n",
       "\n",
       "   p_reorders  p_reorder_rate  tot_orders  tot_prods  avg_basket  avg_reorder  \\\n",
       "0      1269.0        0.577869          12         88    7.333333     4.583333   \n",
       "1      6626.0        0.576776          12         88    7.333333     4.583333   \n",
       "2     12631.0        0.539233          12         88    7.333333     4.583333   \n",
       "3    194939.0        0.774474          12         88    7.333333     4.583333   \n",
       "4     17639.0        0.583513          12         88    7.333333     4.583333   \n",
       "\n",
       "   std_basket  up_orders  up_reorder  up_reorder_rate  \n",
       "0    2.103388          5           4            0.800  \n",
       "1    2.103388         10           9            0.900  \n",
       "2    2.103388          2           1            0.500  \n",
       "3    2.103388          8           7            0.875  \n",
       "4    2.103388          2           1            0.500  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df = join_features(feature_dict_all, test_df)\n",
    "test_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_df.to_csv('./features/features_test.csv',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Vowpalwabbit - Experimental"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "## Numpy savetxt is extremely slow\n",
    "#VW_train = np.column_stack((y_train, X_train))\n",
    "#print \"Save\"\n",
    "#np.savetxt('./data/vw_train.csv', VW_train)\n",
    "#print \"done\"\n",
    "\n",
    "VW_train = pd.concat([Y, X],axis =1 )\n",
    "print VW_train.shape\n",
    "VW_train.head()\n",
    "\n",
    "print \"VW_train\"\n",
    "VW_train.to_csv('./data/vw_train.csv', index = False)\n",
    "\n",
    "#python csv2vw.py ./data/vw_train.csv ./data/vw_train.txt 0 1\n",
    "#python csv2vw.py ./data/vw_test.csv ./data/vw_test.txt 0 1\n",
    "\n",
    "### Vowpal wabbit baseline model\n",
    "\n",
    "#time vw ./data/vw_train.txt --predictions vwpred_train.out\n",
    "\n",
    "\n",
    "vw_pred_train = pd.read_csv('vwpred_train.out', names=['y_p'])\n",
    "vw_pred_train['y_pp']= vw_pred_train['y_p'].apply(lambda x: 1.0 if x > 0.35 else 0.0)\n",
    "y_p3 = vw_pred_train['y_pp'].values\n",
    "print \"Vowpal wabbit accuracy {0:.2f}, precision {0:.2f}, recall {0:.2f}, f1-score {0:.2f}\".format(\n",
    "             accuracy_score(y_train, y_p3), \n",
    "             precision_score(y_train, y_p3),\n",
    "             recall_score(y_train, y_p3),\n",
    "             f1_score(y_train, y_p3))\n",
    "\n",
    "print confusion_matrix(y_train, y_p3)\n",
    "\"\"\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}