Feature_Engineering.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#loading libraries\n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "import pandas as pd\n",
    "#overriding default setting of pandas\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.width', 1000)\n",
    "\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load all data files\n",
    "aisles = pd.read_csv('aisles.csv')\n",
    "departments = pd.read_csv('departments.csv')\n",
    "prior_order = pd.read_csv('order_products__prior.csv')\n",
    "train_order = pd.read_csv('order_products__train.csv')\n",
    "orders = pd.read_csv('orders.csv')\n",
    "products = pd.read_csv('products.csv')\n",
    "\n",
    "#imputation \n",
    "orders.fillna(value = 0, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge all the files together using the order_id as a key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of prior_orders_data : (32434489, 15)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>product_name</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle</th>\n",
       "      <th>department</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>1</td>\n",
       "      <td>49302</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Bulgarian Yogurt</td>\n",
       "      <td>120</td>\n",
       "      <td>16</td>\n",
       "      <td>yogurt</td>\n",
       "      <td>dairy eggs</td>\n",
       "      <td>112108</td>\n",
       "      <td>train</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>49683</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>Cucumber Kirby</td>\n",
       "      <td>83</td>\n",
       "      <td>4</td>\n",
       "      <td>fresh vegetables</td>\n",
       "      <td>produce</td>\n",
       "      <td>112108</td>\n",
       "      <td>train</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>13176</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>Bag of Organic Bananas</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>fresh fruits</td>\n",
       "      <td>produce</td>\n",
       "      <td>112108</td>\n",
       "      <td>train</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>43633</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Lightly Smoked Sardines in Olive Oil</td>\n",
       "      <td>95</td>\n",
       "      <td>15</td>\n",
       "      <td>canned meat seafood</td>\n",
       "      <td>canned goods</td>\n",
       "      <td>112108</td>\n",
       "      <td>train</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>10246</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Organic Celery Hearts</td>\n",
       "      <td>83</td>\n",
       "      <td>4</td>\n",
       "      <td>fresh vegetables</td>\n",
       "      <td>produce</td>\n",
       "      <td>112108</td>\n",
       "      <td>train</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  product_id  add_to_cart_order  reordered                          product_name  aisle_id  department_id                aisle    department  user_id eval_set  order_number  order_dow  order_hour_of_day  days_since_prior_order\n",
       "0         1       49302                  1          1                      Bulgarian Yogurt       120             16               yogurt    dairy eggs   112108    train             4          4                 10                     9.0\n",
       "1         1       49683                  4          0                        Cucumber Kirby        83              4     fresh vegetables       produce   112108    train             4          4                 10                     9.0\n",
       "2         1       13176                  6          0                Bag of Organic Bananas        24              4         fresh fruits       produce   112108    train             4          4                 10                     9.0\n",
       "3         1       43633                  5          1  Lightly Smoked Sardines in Olive Oil        95             15  canned meat seafood  canned goods   112108    train             4          4                 10                     9.0\n",
       "4         1       10246                  3          0                 Organic Celery Hearts        83              4     fresh vegetables       produce   112108    train             4          4                 10                     9.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prior_orders_data = prior_order.sort_values(by=['order_id'])\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = products,\n",
    "                             left_on='product_id', right_on='product_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = aisles,\n",
    "                             left_on='aisle_id', right_on='aisle_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = departments,\n",
    "                             left_on='department_id', right_on='department_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = orders,\n",
    "                             left_on='order_id', right_on='order_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "\n",
    "col_order = ['user_id',\n",
    " 'order_id',\n",
    " 'product_id',\n",
    "  'aisle_id',\n",
    " 'department_id',\n",
    " 'add_to_cart_order',\n",
    " 'reordered',\n",
    " 'product_name',\n",
    " 'aisle',\n",
    " 'department',\n",
    " 'eval_set',\n",
    " 'order_number',\n",
    " 'order_dow',\n",
    " 'order_hour_of_day',\n",
    " 'days_since_prior_order']\n",
    "\n",
    "prior_orders_data = prior_orders_data[col_order]\n",
    "print(\"Shape of prior_orders_data :\", prior_orders_data.shape)\n",
    "prior_orders_data.head()\n",
    "\n",
    "prior_orders_data = pd.concat([prior_order, train_order]).sort_values(by=['order_id'])\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = products,\n",
    "                            left_on='product_id', right_on='product_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = aisles,\n",
    "                            left_on='aisle_id', right_on='aisle_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = departments,\n",
    "                        left_on='department_id', right_on='department_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "prior_orders_data = pd.merge(left = prior_orders_data, right = orders,\n",
    "                             left_on='order_id', right_on='order_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "\n",
    "prior_orders_data.head()\n",
    "\n",
    "prior_orders_data.groupby([\"user_id\",\"product_id\"]).size().shape[0]/prior_orders_data.shape[0]\n",
    "\n",
    "prior_orders_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocessing Strategy: \n",
    "\n",
    "**Generate Training Data (using prior order data and train order data)**\n",
    "- Build features on prior order data\n",
    "- Order from train order data for every user will be used to label the dependent variable i.e. reordered.\n",
    "            For every orders in prior_orders_data, get last order_id for every user, say take n-1 order for feature engineering.\n",
    "        nth order of every user will used to label the dependent variable i.e. reordered.\n",
    "\n",
    "        example : --\n",
    "\n",
    "        let, user A have 90 orders in prior_orders_data.\n",
    "\n",
    "          - build features using 89 orders.\n",
    "          - based on these we will label the data with reordered(0/1) if any of the products he brought in 89 orders\n",
    "          appeared in his 90th order\n",
    "- Predict the product reorder probability using trained model to give accuracy.\n",
    "- Pick top probable products whose probability of reordering was high\n",
    "\n",
    "**Generate Test Data ( using order.eval =='test')**\n",
    "- Add features built on training data , based on orders and users\n",
    "- For every order and product predict if it is reordered(0/1)\n",
    "- Then we will pick top probable products whose probability of reordering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Product Based Features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Features:\n",
    "\n",
    "    feat_1 : product_reorder_rate       : How Frequently the product was reordered ?\n",
    "    feat_2 : avg_pos_incart             : Average position of product in the cart ?\n",
    "    \n",
    "    next 3 values are calculated based product being (these are the ailes or departments to which the product belongs) \n",
    "        - organic, \n",
    "        - isYogurt - aisle\n",
    "        - produce  - department\n",
    "        - isFrozen  - department \n",
    "        - isdairy  - department\n",
    "        - isbreakfast  - department \n",
    "        - issnack  - department\n",
    "        - isbeverage  - department\n",
    "        \n",
    "    Because there are a lot of departments/aisles, these values are then reduced to 3 columns using Non-Negative Matrix Factorization, to reduce sparsity. NMF is used to reduce the dimensions. \n",
    "        \n",
    "    feat_3 : p_reduced_feat_1             : column 1 from NMF output\n",
    "    feat_4 : p_reduced_feat_2             : column 2 from NMF output\n",
    "    feat_5 : p_reduced_feat_3             : column 3 from NMF output\n",
    "    \n",
    "    feat_6 : aisle_reorder_rate           : How frequently a product is reordered from the aisle to which this product belongs\n",
    "    feat_7 : department_reorder_rate      : How frequently a product is reordered from the department to which this product belongs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def generate_product_features(prior_data = None):\n",
    "    #create an empty dataframe\n",
    "    product_features = pd.DataFrame(columns=['product_id'])\n",
    "\n",
    "    #add product_name\n",
    "    product_features['product_id'] = prior_data['product_id'].sort_values().unique()\n",
    "\n",
    "    #get reorder_rate for each product\n",
    "    #reorder_rate = reorders / total orders\n",
    "    df = pd.DataFrame({'reorder_rate': prior_data.groupby(['product_id','reordered'])['reordered'].\\\n",
    "                                                       count().groupby(level=0).\\\n",
    "                                                       apply(lambda x: x / float(x.sum()))}).reset_index()\n",
    "\n",
    "    #get data of reordered products only\n",
    "    new_df = df[df['reordered']==1]\n",
    "    new_df['reorder_rate'] = new_df['reorder_rate'] * new_df['reordered']\n",
    "\n",
    "    #handling for products which were never reordered, hence reorder_rate = 0.0\n",
    "    new_df_1 = df[(df['reordered']==0) & (df['reorder_rate']==float(1.0))]\n",
    "    new_df_1['reorder_rate'] = new_df_1['reorder_rate'] * new_df_1['reordered']\n",
    "    new_df = new_df.append(new_df_1)\n",
    "\n",
    "    #drop other columns of the new_df and sort values by product name to align with product features dataframe\n",
    "    new_df.drop('reordered', axis = 1, inplace = True)\n",
    "    new_df.sort_values(by='product_id', inplace =  True)   \n",
    "    new_df = new_df.reset_index(drop = True)\n",
    "\n",
    "    #add to feat_1 of product_features dataframe\n",
    "    product_features['product_reorder_rate'] = new_df['reorder_rate']\n",
    "\n",
    "    #get mean position of product in the cart, sort by product_name and add to feat_2 of product_features\n",
    "    mean_position = prior_data.groupby('product_id')['add_to_cart_order'].mean().reset_index(name = 'mean_position')\n",
    "    mean_position.sort_values(by = 'product_id', inplace = True)\n",
    "    product_features['avg_pos_incart'] = mean_position['mean_position']\n",
    "\n",
    "\n",
    "\n",
    "    #generate boolean values if product belongs to below categories\n",
    "    products['organic'] = products['product_name'].apply(lambda x: 'organic' in x.lower()).astype(int)\n",
    "    products['isYogurt'] = products['aisle_id'].apply(lambda x: x==120).astype(int)\n",
    "    products['isProduce'] = products['department_id'].apply(lambda x: x==4).astype(int)\n",
    "    products['isFrozen'] = products['department_id'].apply(lambda x: x==1).astype(int)\n",
    "    products['isdairy'] = products['department_id'].apply(lambda x: x==16).astype(int)\n",
    "    products['isbreakfast'] = products['department_id'].apply(lambda x: x==14).astype(int)\n",
    "    products['issnack'] = products['department_id'].apply(lambda x: x==19).astype(int)\n",
    "    products['isbeverage'] = products['department_id'].apply(lambda x: x==7).astype(int)\n",
    "\n",
    "    new_product_feat = products[['organic', 'isYogurt', 'isProduce', 'isFrozen', 'isdairy', 'isbreakfast', 'issnack', 'isbeverage']]\n",
    "    \n",
    "    #reduce sparsity using NMF\n",
    "    \n",
    "#I found this and copied it from: https://www.kaggle.com/themissingsock/matrix-decomposition-with-buyer-data\n",
    "    from sklearn.decomposition import NMF\n",
    "    from sklearn.preprocessing import normalize\n",
    "\n",
    "    nmf = NMF(n_components = 3)\n",
    "    model = nmf.fit(new_product_feat)\n",
    "    W = model.transform(new_product_feat)\n",
    "    prod_data = pd.DataFrame(normalize(W))\n",
    "\n",
    "    prod_data.columns = ['p_reduced_feat_1', 'p_reduced_feat_2','p_reduced_feat_3']\n",
    "    products.drop(['organic', 'isYogurt', 'isProduce', 'isFrozen', 'isdairy', 'isbreakfast', 'issnack', 'isbeverage'], axis = 1, inplace =True)\n",
    "\n",
    "    product_features['p_reduced_feat_1'] = prod_data['p_reduced_feat_1']\n",
    "    product_features['p_reduced_feat_2'] = prod_data['p_reduced_feat_2']\n",
    "    product_features['p_reduced_feat_3'] = prod_data['p_reduced_feat_3']\n",
    "    \n",
    "\n",
    "    #Get aisle reorder rate\n",
    "    df = prior_data.groupby(['aisle']).size().reset_index(name='order_count')\n",
    "    aisle_reorder_rate = prior_data[prior_data['reordered']==1].groupby(['aisle']).size().reset_index(name='reorder_rate')\n",
    "    df['aisle_reorder_rate'] = aisle_reorder_rate['reorder_rate']/df['order_count']\n",
    "    df.drop(['order_count'], axis = 1, inplace = True)\n",
    "    new_df = pd.merge(prior_data, df, on = 'aisle')\n",
    "    \n",
    "    \n",
    "    #Get dept reorder rate\n",
    "    df = prior_data.groupby(['department']).size().reset_index(name='order_count')\n",
    "    aisle_reorder_rate = prior_data[prior_data['reordered']==1].groupby(['aisle']).size().reset_index(name='reorder_rate')\n",
    "    df['dept_reorder_rate'] = aisle_reorder_rate['reorder_rate']/df['order_count']\n",
    "    df.drop(['order_count'], axis = 1, inplace = True)\n",
    "    new_df = pd.merge(new_df, df, on = 'department')\n",
    "    \n",
    "    new_df = new_df[['product_id','aisle_id','department_id','aisle_reorder_rate','dept_reorder_rate']]\n",
    "    new_df.drop_duplicates(keep='first', inplace = True)\n",
    "\n",
    "    #merge dept_reorder_rate and aisle_reorder_rate to existing product features\n",
    "    product_features = pd.merge(product_features, new_df , on='product_id', how = 'inner')\n",
    "    \n",
    "    del df, new_df, new_df_1, new_product_feat, model, prod_data\n",
    "    return product_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-6-5767f667a2aa>:16: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  new_df['reorder_rate'] = new_df['reorder_rate'] * new_df['reordered']\n",
      "<ipython-input-6-5767f667a2aa>:20: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  new_df_1['reorder_rate'] = new_df_1['reorder_rate'] * new_df_1['reordered']\n"
     ]
    }
   ],
   "source": [
    "product_features = generate_product_features(prior_data = prior_orders_data )\n",
    "product_features.to_csv(\"product_features_v6.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_reorder_rate</th>\n",
       "      <th>avg_pos_incart</th>\n",
       "      <th>p_reduced_feat_1</th>\n",
       "      <th>p_reduced_feat_2</th>\n",
       "      <th>p_reduced_feat_3</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle_reorder_rate</th>\n",
       "      <th>dept_reorder_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.614627</td>\n",
       "      <td>5.845954</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>61</td>\n",
       "      <td>19</td>\n",
       "      <td>0.549341</td>\n",
       "      <td>0.043527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.138298</td>\n",
       "      <td>10.138298</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>104</td>\n",
       "      <td>13</td>\n",
       "      <td>0.152933</td>\n",
       "      <td>0.032061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.738516</td>\n",
       "      <td>6.374558</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>94</td>\n",
       "      <td>7</td>\n",
       "      <td>0.527619</td>\n",
       "      <td>0.000936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>0.458689</td>\n",
       "      <td>9.472934</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>0.558656</td>\n",
       "      <td>0.006066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>6.375000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>0.281312</td>\n",
       "      <td>0.032061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49680</th>\n",
       "      <td>49684</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>124</td>\n",
       "      <td>5</td>\n",
       "      <td>0.572844</td>\n",
       "      <td>0.040805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49681</th>\n",
       "      <td>49685</td>\n",
       "      <td>0.122449</td>\n",
       "      <td>9.571429</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>42</td>\n",
       "      <td>1</td>\n",
       "      <td>0.544497</td>\n",
       "      <td>0.006066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49682</th>\n",
       "      <td>49686</td>\n",
       "      <td>0.700787</td>\n",
       "      <td>7.433071</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>112</td>\n",
       "      <td>3</td>\n",
       "      <td>0.670552</td>\n",
       "      <td>0.003811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49683</th>\n",
       "      <td>49687</td>\n",
       "      <td>0.428571</td>\n",
       "      <td>7.071429</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41</td>\n",
       "      <td>8</td>\n",
       "      <td>0.622251</td>\n",
       "      <td>1.748985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49684</th>\n",
       "      <td>49688</td>\n",
       "      <td>0.172043</td>\n",
       "      <td>9.989247</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>73</td>\n",
       "      <td>11</td>\n",
       "      <td>0.318467</td>\n",
       "      <td>0.326152</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49685 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       product_id  product_reorder_rate  avg_pos_incart  p_reduced_feat_1  p_reduced_feat_2  p_reduced_feat_3  aisle_id  department_id  aisle_reorder_rate  dept_reorder_rate\n",
       "0               1              0.614627        5.845954               1.0               0.0               0.0        61             19            0.549341           0.043527\n",
       "1               2              0.138298       10.138298               0.0               0.0               0.0       104             13            0.152933           0.032061\n",
       "2               3              0.738516        6.374558               0.0               0.0               1.0        94              7            0.527619           0.000936\n",
       "3               4              0.458689        9.472934               0.0               1.0               0.0        38              1            0.558656           0.006066\n",
       "4               5              0.625000        6.375000               0.0               0.0               0.0         5             13            0.281312           0.032061\n",
       "...           ...                   ...             ...               ...               ...               ...       ...            ...                 ...                ...\n",
       "49680       49684              0.111111        4.333333               0.0               1.0               0.0       124              5            0.572844           0.040805\n",
       "49681       49685              0.122449        9.571429               0.0               0.0               1.0        42              1            0.544497           0.006066\n",
       "49682       49686              0.700787        7.433071               0.0               1.0               0.0       112              3            0.670552           0.003811\n",
       "49683       49687              0.428571        7.071429               0.0               0.0               0.0        41              8            0.622251           1.748985\n",
       "49684       49688              0.172043        9.989247               0.0               1.0               0.0        73             11            0.318467           0.326152\n",
       "\n",
       "[49685 rows x 10 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product_features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# User Features \n",
    "\n",
    "Now, I'll generate user based features and return it as dataframe object.\n",
    "    merge it with the product features afterwards\n",
    "    \n",
    "   **Features:** \n",
    "    \n",
    "    feat_1 : user_reorder_rate              : Average reorder rate on orders placed?\n",
    "    \n",
    "           **Get mean reorder rate for every user** \n",
    "\n",
    "             Process :\n",
    "              1. Count reorders for every user_id using reordered feature of prior_train_orders\n",
    "              2. Perform Groupby on this output using user_id and get prob of reorder == 0 and reorder == 1\n",
    "              3. Seperate reorder == 0 and reorder == 1 using pivot and fill 0 , where reorder==1 is NA ( indicating no reorders)\n",
    "              4. Add reorder == 1 column to feat_1 \n",
    "   \n",
    "    feat_2 : user_unique_products           : Distinct Products ordered ?\n",
    "    feat_3 : user_total_products            : Total products ordered ?\n",
    "    feat_4 : user_avg_cart_size             : mean Products per order ? = average cart size ?\n",
    "    feat_5 : user_avg_days_between_orders   : Average days between orders ?\n",
    "    feat_6 : user_reordered_products_ratio  : user product reorder ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_user_features(prior_data = None):\n",
    "    \n",
    "    #create an empty dataframe\n",
    "    user_features = pd.DataFrame(columns=['user_id'])\n",
    "    \n",
    "    #Add user_id\n",
    "    user_features['user_id'] = prior_data['user_id'].sort_values().unique()\n",
    "    \n",
    "    user_reorder_rate = prior_data.groupby([\"user_id\",\"reordered\"])['reordered'].count().groupby(level = 0).apply(lambda x: x / float(x.sum())).reset_index(name='reorder_rate')\n",
    "    user_reorder_rate = user_reorder_rate.pivot(index ='user_id', columns ='reordered', values =['reorder_rate']) \n",
    "    user_reorder_rate = pd.DataFrame(user_reorder_rate.to_records())\n",
    "    user_reorder_rate.columns = ['user_id','0', '1']\n",
    "    user_reorder_rate.set_index(\"user_id\", inplace = True)\n",
    "    user_reorder_rate.fillna(0, inplace = True)\n",
    "    user_reorder_rate.reset_index(inplace = True)\n",
    "    user_features['user_reorder_rate'] = user_reorder_rate['1']\n",
    "    \n",
    "    #Get count of all unique products for every user\n",
    "    user_features['user_unique_products'] = prior_data.groupby([\"user_id\"])['product_name'].nunique().reset_index(name = 'unique')['unique']\n",
    "    \n",
    "    #Get count of all products ordered by user\n",
    "    user_features['user_total_products'] = prior_data.groupby([\"user_id\"])['product_name'].size().reset_index(name = 'count')['count']\n",
    "    \n",
    "    #Get mean products per user = Average cart size of user\n",
    "    df = prior_data.groupby([\"user_id\",\"order_id\"])['add_to_cart_order'].count().reset_index(name='cart_size')\\\n",
    "                                                                .groupby('user_id')['cart_size'].mean().reset_index()\n",
    "    user_features['user_avg_cart_size'] = df['cart_size']\n",
    "    \n",
    "    #Get average days between 2 orders for every user\n",
    "    df = prior_data.groupby([\"user_id\",\"order_id\"])['days_since_prior_order'].max().reset_index(name='mean_days_between_orders')\\\n",
    "                                                                .groupby('user_id')['mean_days_between_orders'].mean().reset_index()\n",
    "    user_features['user_avg_days_between_orders'] = df['mean_days_between_orders']\n",
    "    \n",
    "    \n",
    "    #get user product reorder ratio \n",
    "    # number of unique products reordered / number of unique products ordered\n",
    "    df['user_id'] = prior_data['user_id'].sort_values().unique()\n",
    "    df['user_unique_products'] = prior_data.groupby([\"user_id\"])['product_name'].nunique().reset_index(name = 'unique')['unique']\n",
    "    df['user_reordered_products'] = prior_data[prior_data['reordered']==1].groupby([\"user_id\"])['product_name'].nunique().reset_index(name = 'reordered_unique')['reordered_unique']\n",
    "    df.fillna(0, inplace = True)\n",
    "    user_features['user_reordered_products_ratio'] = df['user_reordered_products'] / df['user_unique_products']\n",
    "    \n",
    "    del df\n",
    "    return user_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_features = generate_user_features(prior_data = prior_orders_data)\n",
    "user_features.to_csv(\"user_features_v6.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# User Product Interaction\n",
    "\n",
    "Generate User-Product interaction features and return it as dataframe object, and merge this later too... \n",
    "    \n",
    "  **Features**\n",
    "    \n",
    "    feat_1 : u_p_order_rate             :  How frequently user ordered the product ?\n",
    "    feat_2 : u_p_reorder_rate           :  How frequently user reordered the product ?\n",
    "    feat_3 : u_p_avg_position           :  Average position of product in the cart on orders placed by user ?\n",
    "    feat_4 : u_p_orders_since_last      :  Number of orders placed since the product was last ordered ?\n",
    "    feat_5 : max_streak                 :  Number of orders where user continuously brought a product without missing... counting the streak...\n",
    "        \n",
    "           I used stackoverflow to do the max streak calculation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def max_streak(row):\n",
    "    _max = 0\n",
    "    _sum = 0\n",
    "    for i in row:\n",
    "        if i==1:\n",
    "            _sum += 1\n",
    "        else:\n",
    "            if _sum > _max:\n",
    "                _max = _sum\n",
    "            _sum = 0 \n",
    "    return _max"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_user_product_features(prior_data = None):\n",
    "\n",
    "    #create an empty dataframe\n",
    "    user_product_features = pd.DataFrame(columns=['user_id','product_id'])\n",
    "    \n",
    "    #get unique user-product pairs ( total data is reduced by 60 %)\n",
    "    #prior_train_orders.groupby([\"user_id\",\"product_id\"]).size().shape[0]/prior_train_orders.shape[0]  - 0.409\n",
    "    #add user and product to dataframe\n",
    "    u_p = prior_data.groupby([\"user_id\",\"product_id\"]).size().reset_index()\n",
    "    user_product_features[\"user_id\"] = u_p[\"user_id\"]\n",
    "    user_product_features[\"product_id\"] = u_p[\"product_id\"]\n",
    "    \n",
    "    #How frequently user ordered the product ?\n",
    "    # #times user ordered the product/ #times user placed an order\n",
    "    df = prior_data.groupby([\"user_id\",\"product_id\"])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"user_id\"]).size()\n",
    "    df = df.reset_index(name = 'order_rate')\n",
    "    df.fillna(0. , inplace = True)\n",
    "    user_product_features[\"u_p_order_rate\"] = df[\"order_rate\"]\n",
    "    \n",
    "    #How frequently user reordered the product ?\n",
    "    # #times user reordered the product/ #times user ordered the product\n",
    "    df = prior_data[prior_data[\"reordered\"]==1].groupby([\"user_id\",\"product_id\"])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"user_id\",\"product_id\"]).size()\n",
    "    df = df.reset_index(name = 'reorder_rate')\n",
    "    df.fillna(0. , inplace = True)\n",
    "    user_product_features[\"u_p_reorder_rate\"] = df[\"reorder_rate\"]\n",
    "    \n",
    "    #Average position of product in the cart on orders placed by user ?\n",
    "    \n",
    "    df = prior_data.groupby([\"user_id\",\"product_id\"])['add_to_cart_order'].mean().reset_index(name = 'mean_position')\n",
    "    user_product_features['u_p_avg_position'] = df['mean_position']\n",
    "\n",
    "    \n",
    "    #Number of orders placed since the product was last ordered ?\n",
    "    # Get last order_number placed by user , subtract with last order_number with the product in cart \n",
    "    \n",
    "    df = prior_data.groupby([\"user_id\",\"product_id\"])['order_number'].max().reset_index()\n",
    "    df_2 = prior_data.groupby([\"user_id\"])['order_number'].max().reset_index()\n",
    "    new_df = pd.merge(df, df_2,  how='outer', left_on=['user_id'], right_on = ['user_id'])        \n",
    "    new_df['order_diff'] = new_df['order_number_y'] - new_df['order_number_x']\n",
    "    user_product_features['u_p_orders_since_last'] = new_df['order_diff']\n",
    "    \n",
    "    #max_streak\n",
    "    df = prior_data.groupby([\"user_id\",\"product_id\"])['reordered'].apply(list).reset_index(name = 'max_streak')\n",
    "    df['max_streak'] = df['max_streak'].apply(max_streak)\n",
    "    user_product_features = pd.merge(user_product_features, df, on= [\"user_id\",\"product_id\"])\n",
    "    #user_features[\"max_streak\"] = df['reorder_summary'].apply(max_streak) \n",
    "    \n",
    "    \n",
    "    del df, new_df, df_2\n",
    "    return user_product_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_product_features = generate_user_product_features(prior_data = prior_orders_data)\n",
    "user_product_features.to_csv(\"user_product_features_prior_data_v6.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# More features for Product Time, inspired from EDA: \n",
    "     **features**\n",
    "- product_time: How frequently product was reordered on any given hour ?\n",
    "- product_day: How frequently product was reordered on any given day ?\n",
    "- product_days_since_prior: How frequently a product was reordered  given that difference between 2 orders containing product in days ?\n",
    "- user_days_since_prior: How frequently user reordered any product given difference between 2 orders in days ?\n",
    "- u_p_days_since_prior: How frequently user reordered a particular product given difference between 2 orders in days ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def product_time(prior_data = None):\n",
    "    df = prior_data.groupby(['product_id','order_hour_of_day'])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"product_id\"]).size()\n",
    "    df = df.reset_index(name = 'hour_reorder_rate')\n",
    "    return df\n",
    "#construct dataframe\n",
    "hour_reorder_rate = product_time(prior_orders_data)\n",
    "#save these files\n",
    "hour_reorder_rate.to_csv(\"hour_reorder_rate.csv\", index = False)\n",
    "\n",
    "def product_day(prior_data = None):\n",
    "    df = prior_data.groupby(['product_id','order_dow'])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"product_id\"]).size()\n",
    "    df = df.reset_index(name = 'day_reorder_rate')\n",
    "    return df\n",
    "day_reorder_rate = product_day(prior_orders_data)\n",
    "day_reorder_rate.to_csv(\"day_reorder_rate.csv\", index = False)\n",
    "\n",
    "\n",
    "def product_days_since_prior(prior_data = None):\n",
    "    df = prior_data.groupby(['product_id','days_since_prior_order'])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"product_id\"]).size()\n",
    "    df = df.reset_index(name = 'p_days_since_prior_order_reorder_rate')\n",
    "    return df\n",
    "p_days_since_prior_order_reorder_rate = product_days_since_prior(prior_orders_data)\n",
    "p_days_since_prior_order_reorder_rate.to_csv(\"p_days_since_prior_order_reorder_rate.csv\", index = False)\n",
    "\n",
    "\n",
    "\n",
    "def user_days_since_prior(prior_data = None):\n",
    "    df = prior_data.groupby(['user_id','days_since_prior_order'])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"user_id\"]).size()\n",
    "    df = df.reset_index(name = 'u_days_since_prior_order_reorder_rate')\n",
    "    return df\n",
    "u_days_since_prior_order_reorder_rate = user_days_since_prior(prior_orders_data)\n",
    "u_days_since_prior_order_reorder_rate.to_csv(\"u_days_since_prior_order_reorder_rate.csv\", index = False)\n",
    "\n",
    "\n",
    "def u_p_days_since_prior(prior_data = None):\n",
    "    df = prior_data.groupby([\"user_id\",\"product_id\",\"days_since_prior_order\"])[\"reordered\"].size()\n",
    "    df = df/prior_data.groupby([\"user_id\",\"product_id\"]).size()\n",
    "    df = df.reset_index(name = 'days_since_prior_reorder_rate')\n",
    "    return df\n",
    "days_since_prior_reorder_rate = u_p_days_since_prior(prior_orders_data)\n",
    "days_since_prior_reorder_rate.to_csv(\"days_since_prior_reorder_rate.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Call and Merge the data and Prepare the Training Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "product_features = pd.read_csv(\"product_features_v6.csv\")\n",
    "user_features = pd.read_csv(\"user_features_v6.csv\")\n",
    "user_product_features = pd.read_csv(\"user_product_features_prior_data_v6.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df = pd.merge(user_product_features, user_features,  how='outer', left_on=['user_id'], right_on = ['user_id'])\n",
    "merged_df = pd.merge(merged_df, product_features,  how='outer', left_on=['product_id'], right_on = ['product_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(         order_id  user_id eval_set  order_number  order_dow  order_hour_of_day  days_since_prior_order\n",
       " 10        1187899        1    train            11          4                  8                    14.0\n",
       " 25        1492625        2    train            15          1                 11                    30.0\n",
       " 49        2196797        5    train             5          0                 11                     6.0\n",
       " 74         525192        7    train            21          2                 11                     6.0\n",
       " 78         880375        8    train             4          1                 14                    10.0\n",
       " ...           ...      ...      ...           ...        ...                ...                     ...\n",
       " 3420838   2585586   206199    train            20          2                 16                    30.0\n",
       " 3420862    943915   206200    train            24          6                 19                     6.0\n",
       " 3420924   2371631   206203    train             6          4                 19                    30.0\n",
       " 3420933   1716008   206205    train             4          1                 16                    10.0\n",
       " 3421082    272231   206209    train            14          6                 14                    30.0\n",
       " \n",
       " [131209 rows x 7 columns],\n",
       "          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\n",
       " ...           ...         ...                ...        ...\n",
       " 1384612   3421063       14233                  3          1\n",
       " 1384613   3421063       35548                  4          1\n",
       " 1384614   3421070       35951                  1          1\n",
       " 1384615   3421070       16953                  2          1\n",
       " 1384616   3421070        4724                  3          1\n",
       " \n",
       " [1384617 rows x 4 columns])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_details_train = orders[orders['eval_set'] == 'train']\n",
    "order_details_train, train_order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge all the others product time features: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape of train_order_data : (1384617, 15)\n"
     ]
    }
   ],
   "source": [
    "train_order_data = train_order.sort_values(by=['order_id'])\n",
    "\n",
    "train_order_data = pd.merge(left = train_order_data, right = products,\n",
    "                             left_on='product_id', right_on='product_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "train_order_data = pd.merge(left = train_order_data, right = aisles,\n",
    "                             left_on='aisle_id', right_on='aisle_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "train_order_data = pd.merge(left = train_order_data, right = departments,\n",
    "                             left_on='department_id', right_on='department_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "train_order_data = pd.merge(left = train_order_data, right = orders,\n",
    "                             left_on='order_id', right_on='order_id').sort_values(by=['order_id']).reset_index(drop=True)\n",
    "\n",
    "col_order = ['user_id',\n",
    " 'order_id',\n",
    " 'product_id',\n",
    "  'aisle_id',\n",
    " 'department_id',\n",
    " 'add_to_cart_order',\n",
    " 'reordered',\n",
    " 'product_name',\n",
    " 'aisle',\n",
    " 'department',\n",
    " 'eval_set',\n",
    " 'order_number',\n",
    " 'order_dow',\n",
    " 'order_hour_of_day',\n",
    " 'days_since_prior_order']\n",
    "\n",
    "train_order_data = train_order_data[col_order]\n",
    "print(\"Shape of train_order_data :\", train_order_data.shape)"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>u_p_order_rate</th>\n",
       "      <th>u_p_reorder_rate</th>\n",
       "      <th>u_p_avg_position</th>\n",
       "      <th>u_p_orders_since_last</th>\n",
       "      <th>max_streak</th>\n",
       "      <th>user_reorder_rate</th>\n",
       "      <th>user_unique_products</th>\n",
       "      <th>user_total_products</th>\n",
       "      <th>user_avg_cart_size</th>\n",
       "      <th>user_avg_days_between_orders</th>\n",
       "      <th>user_reordered_products_ratio</th>\n",
       "      <th>product_reorder_rate</th>\n",
       "      <th>avg_pos_incart</th>\n",
       "      <th>p_reduced_feat_1</th>\n",
       "      <th>p_reduced_feat_2</th>\n",
       "      <th>p_reduced_feat_3</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle_reorder_rate</th>\n",
       "      <th>dept_reorder_rate</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>hour_reorder_rate</th>\n",
       "      <th>day_reorder_rate</th>\n",
       "      <th>p_days_since_prior_order_reorder_rate</th>\n",
       "      <th>u_days_since_prior_order_reorder_rate</th>\n",
       "      <th>days_since_prior_reorder_rate</th>\n",
       "      <th>order_id</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>196</td>\n",
       "      <td>0.157143</td>\n",
       "      <td>0.909091</td>\n",
       "      <td>1.363636</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.777843</td>\n",
       "      <td>3.732399</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>77</td>\n",
       "      <td>7</td>\n",
       "      <td>0.639301</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.059092</td>\n",
       "      <td>0.152448</td>\n",
       "      <td>0.038340</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10258</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.600000</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.715610</td>\n",
       "      <td>4.268293</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.061951</td>\n",
       "      <td>0.140488</td>\n",
       "      <td>0.040976</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10326</td>\n",
       "      <td>0.014286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.653439</td>\n",
       "      <td>4.227846</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>0.718823</td>\n",
       "      <td>0.005192</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.067765</td>\n",
       "      <td>0.124264</td>\n",
       "      <td>0.035648</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>12427</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.740182</td>\n",
       "      <td>4.781245</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>0.592129</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.066149</td>\n",
       "      <td>0.130208</td>\n",
       "      <td>0.042108</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>13032</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.661117</td>\n",
       "      <td>5.632172</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121</td>\n",
       "      <td>14</td>\n",
       "      <td>0.572233</td>\n",
       "      <td>0.315318</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.063781</td>\n",
       "      <td>0.142418</td>\n",
       "      <td>0.033811</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030449</th>\n",
       "      <td>120516</td>\n",
       "      <td>40072</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1.125000</td>\n",
       "      <td>14.250000</td>\n",
       "      <td>13.666667</td>\n",
       "      <td>0.448276</td>\n",
       "      <td>6.172414</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>88</td>\n",
       "      <td>13</td>\n",
       "      <td>0.487940</td>\n",
       "      <td>0.032061</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>3264957</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030450</th>\n",
       "      <td>164455</td>\n",
       "      <td>16838</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1.166667</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>0.450980</td>\n",
       "      <td>3.516340</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>11</td>\n",
       "      <td>0.445901</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.071895</td>\n",
       "      <td>0.124183</td>\n",
       "      <td>0.300654</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>2432273</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030451</th>\n",
       "      <td>164455</td>\n",
       "      <td>28952</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1.166667</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>0.255556</td>\n",
       "      <td>3.677778</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47</td>\n",
       "      <td>11</td>\n",
       "      <td>0.312176</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.155556</td>\n",
       "      <td>0.288889</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2432273</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030452</th>\n",
       "      <td>178074</td>\n",
       "      <td>19318</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.153846</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>0.759563</td>\n",
       "      <td>9.278689</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>0.305445</td>\n",
       "      <td>0.032061</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.081967</td>\n",
       "      <td>0.196721</td>\n",
       "      <td>0.071038</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>2250575</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030453</th>\n",
       "      <td>11534</td>\n",
       "      <td>42387</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.769231</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.753049</td>\n",
       "      <td>7.835366</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115</td>\n",
       "      <td>7</td>\n",
       "      <td>0.729930</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.067073</td>\n",
       "      <td>0.228659</td>\n",
       "      <td>0.094512</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>1528524</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9030454 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id  product_id  u_p_order_rate  u_p_reorder_rate  u_p_avg_position  u_p_orders_since_last  max_streak  user_reorder_rate  user_unique_products  user_total_products  user_avg_cart_size  user_avg_days_between_orders  user_reordered_products_ratio  product_reorder_rate  avg_pos_incart  p_reduced_feat_1  p_reduced_feat_2  p_reduced_feat_3  aisle_id  department_id  aisle_reorder_rate  dept_reorder_rate  order_dow  order_hour_of_day  days_since_prior_order  hour_reorder_rate  day_reorder_rate  p_days_since_prior_order_reorder_rate  u_days_since_prior_order_reorder_rate  days_since_prior_reorder_rate  order_id  reordered\n",
       "0              1         196        0.157143          0.909091          1.363636                      0           7           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.777843        3.732399               0.0               0.0               1.0        77              7            0.639301           0.000936          4                  8                    14.0           0.059092          0.152448                               0.038340                               0.242857                       0.181818   1187899        1.0\n",
       "1              1       10258        0.142857          0.900000          3.600000                      0           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.715610        4.268293               0.0               0.0               0.0       117             19            0.519358           0.043527          4                  8                    14.0           0.061951          0.140488                               0.040976                               0.242857                       0.200000   1187899        1.0\n",
       "2              1       10326        0.014286          0.000000          5.000000                      6           0           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.653439        4.227846               0.0               0.0               0.0        24              4            0.718823           0.005192          4                  8                    14.0           0.067765          0.124264                               0.035648                               0.242857                       0.000000   1187899        0.0\n",
       "3              1       12427        0.142857          0.900000          3.300000                      1           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.740182        4.781245               0.0               0.0               0.0        23             19            0.592129           0.043527          4                  8                    14.0           0.066149          0.130208                               0.042108                               0.242857                       0.100000   1187899        0.0\n",
       "4              1       13032        0.057143          0.750000          6.500000                      0           2           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.661117        5.632172               0.0               0.0               0.0       121             14            0.572233           0.315318          4                  8                    14.0           0.063781          0.142418                               0.033811                               0.242857                       0.250000   1187899        1.0\n",
       "...          ...         ...             ...               ...               ...                    ...         ...                ...                   ...                  ...                 ...                           ...                            ...                   ...             ...               ...               ...               ...       ...            ...                 ...                ...        ...                ...                     ...                ...               ...                                    ...                                    ...                            ...       ...        ...\n",
       "9030449   120516       40072        0.333333          0.666667          1.000000                      0           2           0.666667                     3                    9            1.125000                     14.250000                      13.666667              0.448276        6.172414               0.0               0.0               1.0        88             13            0.487940           0.032061          2                  9                    14.0           0.172414          0.137931                               0.103448                               0.222222                       0.333333   3264957        1.0\n",
       "9030450   164455       16838        0.857143          0.833333          1.000000                      0           1           0.714286                     2                    7            1.166667                     25.000000                      37.000000              0.450980        3.516340               0.0               0.0               0.0        70             11            0.445901           0.326152          1                 15                    30.0           0.071895          0.124183                               0.300654                               0.857143                       0.833333   2432273        1.0\n",
       "9030451   164455       28952        0.142857          0.000000          2.000000                      0           0           0.714286                     2                    7            1.166667                     25.000000                      37.000000              0.255556        3.677778               1.0               0.0               0.0        47             11            0.312176           0.326152          1                 15                    30.0           0.066667          0.155556                               0.288889                               0.857143                       1.000000   2432273        0.0\n",
       "9030452   178074       19318        1.000000          0.923077          1.000000                      0           2           0.923077                     1                   13            1.000000                      5.153846                      55.000000              0.759563        9.278689               0.0               0.0               0.0        17             13            0.305445           0.032061          5                 11                     7.0           0.081967          0.196721                               0.071038                               0.153846                       0.153846   2250575        1.0\n",
       "9030453    11534       42387        1.000000          0.923077          1.000000                      0           1           0.923077                     1                   13            1.000000                      5.769231                      10.000000              0.753049        7.835366               0.0               1.0               0.0       115              7            0.729930           0.000936          1                 12                     7.0           0.067073          0.228659                               0.094512                               0.461538                       0.461538   1528524        1.0\n",
       "\n",
       "[9030454 rows x 32 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "upd_train_orders = train_order_data[['user_id','order_id','product_id','reordered']]\n",
    "last_orders = upd_train_orders.groupby(['user_id'])['order_id'].max().reset_index(name = 'new_order_id')\n",
    "order_details = train_order_data[['order_id','order_dow','order_hour_of_day','days_since_prior_order']]\n",
    "order_details = order_details.drop_duplicates()\n",
    "\n",
    "#merge latest orders (prior_last_orders) with above user and product feat\n",
    "train_orders_merged_df = pd.merge(merged_df, upd_train_orders,  how='left', left_on=['user_id','product_id'], right_on = ['user_id','product_id'])\n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, last_orders, on = 'user_id')\n",
    "train_orders_merged_df.drop(\"order_id\", axis = 1, inplace = True)\n",
    "train_orders_merged_df.rename(columns = {'new_order_id':'order_id'}, inplace = True) \n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, order_details, on = 'order_id')\n",
    "train_orders_merged_df[['reordered']]= train_orders_merged_df[['reordered']].fillna(value=0.0)\n",
    "\n",
    "#merge with all the prodcuct time features\n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, hour_reorder_rate, on=['product_id','order_hour_of_day'], how = 'left')\n",
    "train_orders_merged_df[['hour_reorder_rate']]= train_orders_merged_df[['hour_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, day_reorder_rate, on=['product_id','order_dow'], how = 'left')\n",
    "train_orders_merged_df[['day_reorder_rate']]= train_orders_merged_df[['day_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, p_days_since_prior_order_reorder_rate, on=['product_id','days_since_prior_order'], how = 'left')\n",
    "train_orders_merged_df[['p_days_since_prior_order_reorder_rate']]= train_orders_merged_df[['p_days_since_prior_order_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, u_days_since_prior_order_reorder_rate, on=['user_id','days_since_prior_order'], how = 'left')\n",
    "train_orders_merged_df[['u_days_since_prior_order_reorder_rate']]= train_orders_merged_df[['u_days_since_prior_order_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "train_orders_merged_df = pd.merge(train_orders_merged_df, days_since_prior_reorder_rate, on=[\"user_id\",\"product_id\",'days_since_prior_order'], how = 'left')\n",
    "train_orders_merged_df[['days_since_prior_reorder_rate']]= train_orders_merged_df[['days_since_prior_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "\n",
    "train_orders_merged_df = train_orders_merged_df[['user_id', 'product_id', 'u_p_order_rate', 'u_p_reorder_rate', 'u_p_avg_position', 'u_p_orders_since_last', 'max_streak', 'user_reorder_rate', 'user_unique_products', 'user_total_products', 'user_avg_cart_size', 'user_avg_days_between_orders', 'user_reordered_products_ratio', 'product_reorder_rate', 'avg_pos_incart', 'p_reduced_feat_1', 'p_reduced_feat_2', 'p_reduced_feat_3', 'aisle_id', 'department_id', 'aisle_reorder_rate', 'dept_reorder_rate', 'order_dow', 'order_hour_of_day', 'days_since_prior_order', 'hour_reorder_rate', 'day_reorder_rate', 'p_days_since_prior_order_reorder_rate', 'u_days_since_prior_order_reorder_rate', 'days_since_prior_reorder_rate', 'order_id','reordered']]\n",
    "\n",
    "train_orders_merged_df"
   ]
  },
  {
   "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>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>u_p_order_rate</th>\n",
       "      <th>u_p_reorder_rate</th>\n",
       "      <th>u_p_avg_position</th>\n",
       "      <th>u_p_orders_since_last</th>\n",
       "      <th>max_streak</th>\n",
       "      <th>user_reorder_rate</th>\n",
       "      <th>user_unique_products</th>\n",
       "      <th>user_total_products</th>\n",
       "      <th>user_avg_cart_size</th>\n",
       "      <th>user_avg_days_between_orders</th>\n",
       "      <th>user_reordered_products_ratio</th>\n",
       "      <th>product_reorder_rate</th>\n",
       "      <th>avg_pos_incart</th>\n",
       "      <th>p_reduced_feat_1</th>\n",
       "      <th>p_reduced_feat_2</th>\n",
       "      <th>p_reduced_feat_3</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle_reorder_rate</th>\n",
       "      <th>dept_reorder_rate</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>hour_reorder_rate</th>\n",
       "      <th>day_reorder_rate</th>\n",
       "      <th>p_days_since_prior_order_reorder_rate</th>\n",
       "      <th>u_days_since_prior_order_reorder_rate</th>\n",
       "      <th>days_since_prior_reorder_rate</th>\n",
       "      <th>order_id</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>196</td>\n",
       "      <td>0.157143</td>\n",
       "      <td>0.909091</td>\n",
       "      <td>1.363636</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.777843</td>\n",
       "      <td>3.732399</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>77</td>\n",
       "      <td>7</td>\n",
       "      <td>0.639301</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.059092</td>\n",
       "      <td>0.152448</td>\n",
       "      <td>0.038340</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10258</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.600000</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.715610</td>\n",
       "      <td>4.268293</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.061951</td>\n",
       "      <td>0.140488</td>\n",
       "      <td>0.040976</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10326</td>\n",
       "      <td>0.014286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.653439</td>\n",
       "      <td>4.227846</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>0.718823</td>\n",
       "      <td>0.005192</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.067765</td>\n",
       "      <td>0.124264</td>\n",
       "      <td>0.035648</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>12427</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.740182</td>\n",
       "      <td>4.781245</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>0.592129</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.066149</td>\n",
       "      <td>0.130208</td>\n",
       "      <td>0.042108</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>13032</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.661117</td>\n",
       "      <td>5.632172</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121</td>\n",
       "      <td>14</td>\n",
       "      <td>0.572233</td>\n",
       "      <td>0.315318</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.063781</td>\n",
       "      <td>0.142418</td>\n",
       "      <td>0.033811</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030449</th>\n",
       "      <td>120516</td>\n",
       "      <td>40072</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1.125000</td>\n",
       "      <td>14.250000</td>\n",
       "      <td>13.666667</td>\n",
       "      <td>0.448276</td>\n",
       "      <td>6.172414</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>88</td>\n",
       "      <td>13</td>\n",
       "      <td>0.487940</td>\n",
       "      <td>0.032061</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>3264957</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030450</th>\n",
       "      <td>164455</td>\n",
       "      <td>16838</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1.166667</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>0.450980</td>\n",
       "      <td>3.516340</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>11</td>\n",
       "      <td>0.445901</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.071895</td>\n",
       "      <td>0.124183</td>\n",
       "      <td>0.300654</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>2432273</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030451</th>\n",
       "      <td>164455</td>\n",
       "      <td>28952</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1.166667</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>0.255556</td>\n",
       "      <td>3.677778</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47</td>\n",
       "      <td>11</td>\n",
       "      <td>0.312176</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.155556</td>\n",
       "      <td>0.288889</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2432273</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030452</th>\n",
       "      <td>178074</td>\n",
       "      <td>19318</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.153846</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>0.759563</td>\n",
       "      <td>9.278689</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>0.305445</td>\n",
       "      <td>0.032061</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.081967</td>\n",
       "      <td>0.196721</td>\n",
       "      <td>0.071038</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>2250575</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030453</th>\n",
       "      <td>11534</td>\n",
       "      <td>42387</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.769231</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.753049</td>\n",
       "      <td>7.835366</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115</td>\n",
       "      <td>7</td>\n",
       "      <td>0.729930</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.067073</td>\n",
       "      <td>0.228659</td>\n",
       "      <td>0.094512</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>1528524</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9030454 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id  product_id  u_p_order_rate  u_p_reorder_rate  u_p_avg_position  u_p_orders_since_last  max_streak  user_reorder_rate  user_unique_products  user_total_products  user_avg_cart_size  user_avg_days_between_orders  user_reordered_products_ratio  product_reorder_rate  avg_pos_incart  p_reduced_feat_1  p_reduced_feat_2  p_reduced_feat_3  aisle_id  department_id  aisle_reorder_rate  dept_reorder_rate  order_dow  order_hour_of_day  days_since_prior_order  hour_reorder_rate  day_reorder_rate  p_days_since_prior_order_reorder_rate  u_days_since_prior_order_reorder_rate  days_since_prior_reorder_rate  order_id  reordered\n",
       "0              1         196        0.157143          0.909091          1.363636                      0           7           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.777843        3.732399               0.0               0.0               1.0        77              7            0.639301           0.000936          4                  8                    14.0           0.059092          0.152448                               0.038340                               0.242857                       0.181818   1187899        1.0\n",
       "1              1       10258        0.142857          0.900000          3.600000                      0           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.715610        4.268293               0.0               0.0               0.0       117             19            0.519358           0.043527          4                  8                    14.0           0.061951          0.140488                               0.040976                               0.242857                       0.200000   1187899        1.0\n",
       "2              1       10326        0.014286          0.000000          5.000000                      6           0           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.653439        4.227846               0.0               0.0               0.0        24              4            0.718823           0.005192          4                  8                    14.0           0.067765          0.124264                               0.035648                               0.242857                       0.000000   1187899        0.0\n",
       "3              1       12427        0.142857          0.900000          3.300000                      1           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.740182        4.781245               0.0               0.0               0.0        23             19            0.592129           0.043527          4                  8                    14.0           0.066149          0.130208                               0.042108                               0.242857                       0.100000   1187899        0.0\n",
       "4              1       13032        0.057143          0.750000          6.500000                      0           2           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.661117        5.632172               0.0               0.0               0.0       121             14            0.572233           0.315318          4                  8                    14.0           0.063781          0.142418                               0.033811                               0.242857                       0.250000   1187899        1.0\n",
       "...          ...         ...             ...               ...               ...                    ...         ...                ...                   ...                  ...                 ...                           ...                            ...                   ...             ...               ...               ...               ...       ...            ...                 ...                ...        ...                ...                     ...                ...               ...                                    ...                                    ...                            ...       ...        ...\n",
       "9030449   120516       40072        0.333333          0.666667          1.000000                      0           2           0.666667                     3                    9            1.125000                     14.250000                      13.666667              0.448276        6.172414               0.0               0.0               1.0        88             13            0.487940           0.032061          2                  9                    14.0           0.172414          0.137931                               0.103448                               0.222222                       0.333333   3264957        1.0\n",
       "9030450   164455       16838        0.857143          0.833333          1.000000                      0           1           0.714286                     2                    7            1.166667                     25.000000                      37.000000              0.450980        3.516340               0.0               0.0               0.0        70             11            0.445901           0.326152          1                 15                    30.0           0.071895          0.124183                               0.300654                               0.857143                       0.833333   2432273        1.0\n",
       "9030451   164455       28952        0.142857          0.000000          2.000000                      0           0           0.714286                     2                    7            1.166667                     25.000000                      37.000000              0.255556        3.677778               1.0               0.0               0.0        47             11            0.312176           0.326152          1                 15                    30.0           0.066667          0.155556                               0.288889                               0.857143                       1.000000   2432273        0.0\n",
       "9030452   178074       19318        1.000000          0.923077          1.000000                      0           2           0.923077                     1                   13            1.000000                      5.153846                      55.000000              0.759563        9.278689               0.0               0.0               0.0        17             13            0.305445           0.032061          5                 11                     7.0           0.081967          0.196721                               0.071038                               0.153846                       0.153846   2250575        1.0\n",
       "9030453    11534       42387        1.000000          0.923077          1.000000                      0           1           0.923077                     1                   13            1.000000                      5.769231                      10.000000              0.753049        7.835366               0.0               1.0               0.0       115              7            0.729930           0.000936          1                 12                     7.0           0.067073          0.228659                               0.094512                               0.461538                       0.461538   1528524        1.0\n",
       "\n",
       "[9030454 rows x 32 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_orders_merged_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting time to a categorical variable (3 sections)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#https://learnersdictionary.com/qa/parts-of-the-day-early-morning-late-morning-etc\n",
    "\n",
    "# For ease, change the time to a categorical variable\n",
    "\n",
    "def hour_tocategorical(time):\n",
    "    if time > 5 and time < 12:\n",
    "            return 0\n",
    "    elif time > 12 and time < 17:\n",
    "        return 1\n",
    "    elif time > 17 and time < 21:\n",
    "        return 2\n",
    "    else:\n",
    "        return 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>u_p_order_rate</th>\n",
       "      <th>u_p_reorder_rate</th>\n",
       "      <th>u_p_avg_position</th>\n",
       "      <th>u_p_orders_since_last</th>\n",
       "      <th>max_streak</th>\n",
       "      <th>user_reorder_rate</th>\n",
       "      <th>user_unique_products</th>\n",
       "      <th>user_total_products</th>\n",
       "      <th>user_avg_cart_size</th>\n",
       "      <th>user_avg_days_between_orders</th>\n",
       "      <th>user_reordered_products_ratio</th>\n",
       "      <th>product_reorder_rate</th>\n",
       "      <th>avg_pos_incart</th>\n",
       "      <th>p_reduced_feat_1</th>\n",
       "      <th>p_reduced_feat_2</th>\n",
       "      <th>p_reduced_feat_3</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle_reorder_rate</th>\n",
       "      <th>dept_reorder_rate</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>hour_reorder_rate</th>\n",
       "      <th>day_reorder_rate</th>\n",
       "      <th>p_days_since_prior_order_reorder_rate</th>\n",
       "      <th>u_days_since_prior_order_reorder_rate</th>\n",
       "      <th>days_since_prior_reorder_rate</th>\n",
       "      <th>order_id</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>196</td>\n",
       "      <td>0.157143</td>\n",
       "      <td>0.909091</td>\n",
       "      <td>1.363636</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.777843</td>\n",
       "      <td>3.732399</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>77</td>\n",
       "      <td>7</td>\n",
       "      <td>0.639301</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.059092</td>\n",
       "      <td>0.152448</td>\n",
       "      <td>0.038340</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10258</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.600000</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.715610</td>\n",
       "      <td>4.268293</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.061951</td>\n",
       "      <td>0.140488</td>\n",
       "      <td>0.040976</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10326</td>\n",
       "      <td>0.014286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.653439</td>\n",
       "      <td>4.227846</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>0.718823</td>\n",
       "      <td>0.005192</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.067765</td>\n",
       "      <td>0.124264</td>\n",
       "      <td>0.035648</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>12427</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.740182</td>\n",
       "      <td>4.781245</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>0.592129</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.066149</td>\n",
       "      <td>0.130208</td>\n",
       "      <td>0.042108</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>13032</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.661117</td>\n",
       "      <td>5.632172</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121</td>\n",
       "      <td>14</td>\n",
       "      <td>0.572233</td>\n",
       "      <td>0.315318</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.063781</td>\n",
       "      <td>0.142418</td>\n",
       "      <td>0.033811</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030449</th>\n",
       "      <td>120516</td>\n",
       "      <td>40072</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>1.125000</td>\n",
       "      <td>14.250000</td>\n",
       "      <td>13.666667</td>\n",
       "      <td>0.448276</td>\n",
       "      <td>6.172414</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>88</td>\n",
       "      <td>13</td>\n",
       "      <td>0.487940</td>\n",
       "      <td>0.032061</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.172414</td>\n",
       "      <td>0.137931</td>\n",
       "      <td>0.103448</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>3264957</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030450</th>\n",
       "      <td>164455</td>\n",
       "      <td>16838</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1.166667</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>0.450980</td>\n",
       "      <td>3.516340</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>11</td>\n",
       "      <td>0.445901</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.071895</td>\n",
       "      <td>0.124183</td>\n",
       "      <td>0.300654</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>2432273</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030451</th>\n",
       "      <td>164455</td>\n",
       "      <td>28952</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.714286</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1.166667</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>37.000000</td>\n",
       "      <td>0.255556</td>\n",
       "      <td>3.677778</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47</td>\n",
       "      <td>11</td>\n",
       "      <td>0.312176</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>0.155556</td>\n",
       "      <td>0.288889</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2432273</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030452</th>\n",
       "      <td>178074</td>\n",
       "      <td>19318</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.153846</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>0.759563</td>\n",
       "      <td>9.278689</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17</td>\n",
       "      <td>13</td>\n",
       "      <td>0.305445</td>\n",
       "      <td>0.032061</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.081967</td>\n",
       "      <td>0.196721</td>\n",
       "      <td>0.071038</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>2250575</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9030453</th>\n",
       "      <td>11534</td>\n",
       "      <td>42387</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.923077</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.769231</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.753049</td>\n",
       "      <td>7.835366</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115</td>\n",
       "      <td>7</td>\n",
       "      <td>0.729930</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.067073</td>\n",
       "      <td>0.228659</td>\n",
       "      <td>0.094512</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>1528524</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9030454 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id  product_id  u_p_order_rate  u_p_reorder_rate  u_p_avg_position  u_p_orders_since_last  max_streak  user_reorder_rate  user_unique_products  user_total_products  user_avg_cart_size  user_avg_days_between_orders  user_reordered_products_ratio  product_reorder_rate  avg_pos_incart  p_reduced_feat_1  p_reduced_feat_2  p_reduced_feat_3  aisle_id  department_id  aisle_reorder_rate  dept_reorder_rate  order_dow  order_hour_of_day  days_since_prior_order  hour_reorder_rate  day_reorder_rate  p_days_since_prior_order_reorder_rate  u_days_since_prior_order_reorder_rate  days_since_prior_reorder_rate  order_id  reordered\n",
       "0              1         196        0.157143          0.909091          1.363636                      0           7           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.777843        3.732399               0.0               0.0               1.0        77              7            0.639301           0.000936          4                  0                    14.0           0.059092          0.152448                               0.038340                               0.242857                       0.181818   1187899        1.0\n",
       "1              1       10258        0.142857          0.900000          3.600000                      0           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.715610        4.268293               0.0               0.0               0.0       117             19            0.519358           0.043527          4                  0                    14.0           0.061951          0.140488                               0.040976                               0.242857                       0.200000   1187899        1.0\n",
       "2              1       10326        0.014286          0.000000          5.000000                      6           0           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.653439        4.227846               0.0               0.0               0.0        24              4            0.718823           0.005192          4                  0                    14.0           0.067765          0.124264                               0.035648                               0.242857                       0.000000   1187899        0.0\n",
       "3              1       12427        0.142857          0.900000          3.300000                      1           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.740182        4.781245               0.0               0.0               0.0        23             19            0.592129           0.043527          4                  0                    14.0           0.066149          0.130208                               0.042108                               0.242857                       0.100000   1187899        0.0\n",
       "4              1       13032        0.057143          0.750000          6.500000                      0           2           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.661117        5.632172               0.0               0.0               0.0       121             14            0.572233           0.315318          4                  0                    14.0           0.063781          0.142418                               0.033811                               0.242857                       0.250000   1187899        1.0\n",
       "...          ...         ...             ...               ...               ...                    ...         ...                ...                   ...                  ...                 ...                           ...                            ...                   ...             ...               ...               ...               ...       ...            ...                 ...                ...        ...                ...                     ...                ...               ...                                    ...                                    ...                            ...       ...        ...\n",
       "9030449   120516       40072        0.333333          0.666667          1.000000                      0           2           0.666667                     3                    9            1.125000                     14.250000                      13.666667              0.448276        6.172414               0.0               0.0               1.0        88             13            0.487940           0.032061          2                  0                    14.0           0.172414          0.137931                               0.103448                               0.222222                       0.333333   3264957        1.0\n",
       "9030450   164455       16838        0.857143          0.833333          1.000000                      0           1           0.714286                     2                    7            1.166667                     25.000000                      37.000000              0.450980        3.516340               0.0               0.0               0.0        70             11            0.445901           0.326152          1                  1                    30.0           0.071895          0.124183                               0.300654                               0.857143                       0.833333   2432273        1.0\n",
       "9030451   164455       28952        0.142857          0.000000          2.000000                      0           0           0.714286                     2                    7            1.166667                     25.000000                      37.000000              0.255556        3.677778               1.0               0.0               0.0        47             11            0.312176           0.326152          1                  1                    30.0           0.066667          0.155556                               0.288889                               0.857143                       1.000000   2432273        0.0\n",
       "9030452   178074       19318        1.000000          0.923077          1.000000                      0           2           0.923077                     1                   13            1.000000                      5.153846                      55.000000              0.759563        9.278689               0.0               0.0               0.0        17             13            0.305445           0.032061          5                  0                     7.0           0.081967          0.196721                               0.071038                               0.153846                       0.153846   2250575        1.0\n",
       "9030453    11534       42387        1.000000          0.923077          1.000000                      0           1           0.923077                     1                   13            1.000000                      5.769231                      10.000000              0.753049        7.835366               0.0               1.0               0.0       115              7            0.729930           0.000936          1                  3                     7.0           0.067073          0.228659                               0.094512                               0.461538                       0.461538   1528524        1.0\n",
       "\n",
       "[9030454 rows x 32 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert order_hour_of_day to categorical feature \n",
    "train_orders_merged_df['order_hour_of_day'] = train_orders_merged_df['order_hour_of_day'].apply(hour_tocategorical)\n",
    "train_orders_merged_df.reordered.value_counts()\n",
    "train_orders_merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id                                 -0.000561\n",
       "product_id                               0.002784\n",
       "u_p_order_rate                           0.332684\n",
       "u_p_reorder_rate                         0.418406\n",
       "u_p_avg_position                        -0.049759\n",
       "u_p_orders_since_last                   -0.228218\n",
       "max_streak                               0.272472\n",
       "user_reorder_rate                        0.043667\n",
       "user_unique_products                    -0.083579\n",
       "user_total_products                     -0.043050\n",
       "user_avg_cart_size                       0.059473\n",
       "user_avg_days_between_orders             0.051378\n",
       "user_reordered_products_ratio            0.047842\n",
       "product_reorder_rate                     0.164459\n",
       "avg_pos_incart                          -0.125572\n",
       "p_reduced_feat_1                        -0.006936\n",
       "p_reduced_feat_2                        -0.001246\n",
       "p_reduced_feat_3                         0.014745\n",
       "aisle_id                                 0.002038\n",
       "department_id                           -0.032566\n",
       "aisle_reorder_rate                       0.126891\n",
       "dept_reorder_rate                       -0.043223\n",
       "order_dow                               -0.011260\n",
       "order_hour_of_day                       -0.003341\n",
       "days_since_prior_order                   0.032276\n",
       "hour_reorder_rate                        0.011242\n",
       "day_reorder_rate                         0.021537\n",
       "p_days_since_prior_order_reorder_rate    0.003545\n",
       "u_days_since_prior_order_reorder_rate    0.064579\n",
       "days_since_prior_reorder_rate            0.174729\n",
       "order_id                                 0.001168\n",
       "reordered                                1.000000\n",
       "Name: reordered, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#check correlation between variables\n",
    "train_orders_merged_df[train_orders_merged_df.columns[:]].corr()['reordered'][:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe size: 2.22 GB\n"
     ]
    }
   ],
   "source": [
    "from sys import getsizeof\n",
    "print('Dataframe size: %2.2f GB'%(getsizeof(train_orders_merged_df)/(1024.0**3))) # change it into GB format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_id                                  0\n",
       "product_id                               0\n",
       "u_p_order_rate                           0\n",
       "u_p_reorder_rate                         0\n",
       "u_p_avg_position                         0\n",
       "u_p_orders_since_last                    0\n",
       "max_streak                               0\n",
       "user_reorder_rate                        0\n",
       "user_unique_products                     0\n",
       "user_total_products                      0\n",
       "user_avg_cart_size                       0\n",
       "user_avg_days_between_orders             0\n",
       "user_reordered_products_ratio            0\n",
       "product_reorder_rate                     0\n",
       "avg_pos_incart                           0\n",
       "p_reduced_feat_1                         0\n",
       "p_reduced_feat_2                         0\n",
       "p_reduced_feat_3                         0\n",
       "aisle_id                                 0\n",
       "department_id                            0\n",
       "aisle_reorder_rate                       0\n",
       "dept_reorder_rate                        0\n",
       "order_dow                                0\n",
       "order_hour_of_day                        0\n",
       "days_since_prior_order                   0\n",
       "hour_reorder_rate                        0\n",
       "day_reorder_rate                         0\n",
       "p_days_since_prior_order_reorder_rate    0\n",
       "u_days_since_prior_order_reorder_rate    0\n",
       "days_since_prior_reorder_rate            0\n",
       "order_id                                 0\n",
       "reordered                                0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_orders_merged_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_orders_merged_df.to_csv(\"train_orders_merged_df_v6.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare the Testing Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/amenti_kenea/opt/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py:4308: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  return super().drop(\n"
     ]
    }
   ],
   "source": [
    "#get test order details\n",
    "order_details_test = orders[orders['eval_set'] == 'test']\n",
    "order_details_test.drop(['eval_set'], axis = 1, inplace = True)\n",
    "\n",
    "# merge on user_product features, to get features on this order\n",
    "test_orders_merge_df = pd.merge(order_details_test, user_product_features, on = ['user_id'], how = 'outer')\n",
    "test_orders_merge_df.dropna(inplace =True)\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, user_features, on = ['user_id'])\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, product_features, on = ['product_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>order_id</th>\n",
       "      <th>user_id</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>38</th>\n",
       "      <td>2774568</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>15</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>329954</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1528013</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>1376945</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>1356845</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3420918</th>\n",
       "      <td>2728930</td>\n",
       "      <td>206202</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3420929</th>\n",
       "      <td>350108</td>\n",
       "      <td>206204</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421001</th>\n",
       "      <td>1043943</td>\n",
       "      <td>206206</td>\n",
       "      <td>68</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421018</th>\n",
       "      <td>2821651</td>\n",
       "      <td>206207</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3421068</th>\n",
       "      <td>803273</td>\n",
       "      <td>206208</td>\n",
       "      <td>50</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         order_id  user_id  order_number  order_dow  order_hour_of_day  days_since_prior_order\n",
       "38        2774568        3            13          5                 15                    11.0\n",
       "44         329954        4             6          3                 12                    30.0\n",
       "53        1528013        6             4          3                 16                    22.0\n",
       "96        1376945       11             8          6                 11                     8.0\n",
       "102       1356845       12             6          1                 20                    30.0\n",
       "...           ...      ...           ...        ...                ...                     ...\n",
       "3420918   2728930   206202            23          2                 17                     6.0\n",
       "3420929    350108   206204             5          4                 14                    14.0\n",
       "3421001   1043943   206206            68          0                 20                     0.0\n",
       "3421018   2821651   206207            17          2                 13                    14.0\n",
       "3421068    803273   206208            50          5                 11                     4.0\n",
       "\n",
       "[75000 rows x 6 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "order_details_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge on user_product features, to get features on this order\n",
    "\n",
    "test_orders_merge_df = pd.merge(order_details_test, user_product_features, on = ['user_id'], how = 'outer')\n",
    "test_orders_merge_df.dropna(inplace =True)\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, user_features, on = ['user_id'])\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, product_features, on = ['product_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge with misc features\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, hour_reorder_rate, on=['product_id','order_hour_of_day'], how = 'left')\n",
    "test_orders_merge_df[['hour_reorder_rate']]= test_orders_merge_df[['hour_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, day_reorder_rate, on=['product_id','order_dow'], how = 'left')\n",
    "test_orders_merge_df[['day_reorder_rate']]= test_orders_merge_df[['day_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, p_days_since_prior_order_reorder_rate, on=['product_id','days_since_prior_order'], how = 'left')\n",
    "test_orders_merge_df[['p_days_since_prior_order_reorder_rate']]= test_orders_merge_df[['p_days_since_prior_order_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, u_days_since_prior_order_reorder_rate, on=['user_id','days_since_prior_order'], how = 'left')\n",
    "test_orders_merge_df[['u_days_since_prior_order_reorder_rate']]= test_orders_merge_df[['u_days_since_prior_order_reorder_rate']].fillna(value=0.0)\n",
    "\n",
    "test_orders_merge_df = pd.merge(test_orders_merge_df, days_since_prior_reorder_rate, on=[\"user_id\",\"product_id\",'days_since_prior_order'], how = 'left')\n",
    "test_orders_merge_df[['days_since_prior_reorder_rate']]= test_orders_merge_df[['days_since_prior_reorder_rate']].fillna(value=0.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>u_p_order_rate</th>\n",
       "      <th>u_p_reorder_rate</th>\n",
       "      <th>u_p_avg_position</th>\n",
       "      <th>u_p_orders_since_last</th>\n",
       "      <th>max_streak</th>\n",
       "      <th>user_reorder_rate</th>\n",
       "      <th>user_unique_products</th>\n",
       "      <th>user_total_products</th>\n",
       "      <th>user_avg_cart_size</th>\n",
       "      <th>user_avg_days_between_orders</th>\n",
       "      <th>user_reordered_products_ratio</th>\n",
       "      <th>product_reorder_rate</th>\n",
       "      <th>avg_pos_incart</th>\n",
       "      <th>p_reduced_feat_1</th>\n",
       "      <th>p_reduced_feat_2</th>\n",
       "      <th>p_reduced_feat_3</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle_reorder_rate</th>\n",
       "      <th>dept_reorder_rate</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>hour_reorder_rate</th>\n",
       "      <th>day_reorder_rate</th>\n",
       "      <th>p_days_since_prior_order_reorder_rate</th>\n",
       "      <th>u_days_since_prior_order_reorder_rate</th>\n",
       "      <th>days_since_prior_reorder_rate</th>\n",
       "      <th>order_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>248</td>\n",
       "      <td>0.011364</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>33</td>\n",
       "      <td>88</td>\n",
       "      <td>7.333333</td>\n",
       "      <td>11.083333</td>\n",
       "      <td>0.575758</td>\n",
       "      <td>0.401366</td>\n",
       "      <td>10.637178</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.088316</td>\n",
       "      <td>0.123824</td>\n",
       "      <td>0.022003</td>\n",
       "      <td>0.056818</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2774568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>248</td>\n",
       "      <td>0.013514</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.175676</td>\n",
       "      <td>61</td>\n",
       "      <td>74</td>\n",
       "      <td>14.800000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>0.147541</td>\n",
       "      <td>0.401366</td>\n",
       "      <td>10.637178</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.036419</td>\n",
       "      <td>0.172838</td>\n",
       "      <td>0.102124</td>\n",
       "      <td>0.527027</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1356845.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>418</td>\n",
       "      <td>248</td>\n",
       "      <td>0.005988</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.359281</td>\n",
       "      <td>107</td>\n",
       "      <td>167</td>\n",
       "      <td>20.875000</td>\n",
       "      <td>20.625000</td>\n",
       "      <td>0.130841</td>\n",
       "      <td>0.401366</td>\n",
       "      <td>10.637178</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.051593</td>\n",
       "      <td>0.145068</td>\n",
       "      <td>0.025948</td>\n",
       "      <td>0.137725</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3073553.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>503</td>\n",
       "      <td>248</td>\n",
       "      <td>0.002994</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>0.613772</td>\n",
       "      <td>129</td>\n",
       "      <td>334</td>\n",
       "      <td>9.277778</td>\n",
       "      <td>9.777778</td>\n",
       "      <td>0.054264</td>\n",
       "      <td>0.401366</td>\n",
       "      <td>10.637178</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.075873</td>\n",
       "      <td>0.172838</td>\n",
       "      <td>0.061760</td>\n",
       "      <td>0.176647</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1490499.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>720</td>\n",
       "      <td>248</td>\n",
       "      <td>0.009709</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.618123</td>\n",
       "      <td>118</td>\n",
       "      <td>309</td>\n",
       "      <td>13.434783</td>\n",
       "      <td>13.782609</td>\n",
       "      <td>0.093220</td>\n",
       "      <td>0.401366</td>\n",
       "      <td>10.637178</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.081032</td>\n",
       "      <td>0.172838</td>\n",
       "      <td>0.009712</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>391588.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4833287</th>\n",
       "      <td>205877</td>\n",
       "      <td>27049</td>\n",
       "      <td>0.001684</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>32.000000</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.501684</td>\n",
       "      <td>296</td>\n",
       "      <td>594</td>\n",
       "      <td>13.500000</td>\n",
       "      <td>7.795455</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.363636</td>\n",
       "      <td>10.090909</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>40</td>\n",
       "      <td>8</td>\n",
       "      <td>0.566198</td>\n",
       "      <td>1.748985</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>913357.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4833288</th>\n",
       "      <td>205880</td>\n",
       "      <td>25635</td>\n",
       "      <td>0.014286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>53</td>\n",
       "      <td>70</td>\n",
       "      <td>5.384615</td>\n",
       "      <td>3.230769</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>4.333333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>33</td>\n",
       "      <td>6</td>\n",
       "      <td>0.398439</td>\n",
       "      <td>0.603735</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2268059.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4833289</th>\n",
       "      <td>205893</td>\n",
       "      <td>11873</td>\n",
       "      <td>0.002786</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.543175</td>\n",
       "      <td>164</td>\n",
       "      <td>359</td>\n",
       "      <td>22.437500</td>\n",
       "      <td>17.125000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>13.833333</td>\n",
       "      <td>0.002308</td>\n",
       "      <td>0.999987</td>\n",
       "      <td>0.004505</td>\n",
       "      <td>80</td>\n",
       "      <td>11</td>\n",
       "      <td>0.238818</td>\n",
       "      <td>0.326152</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2072901.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4833290</th>\n",
       "      <td>206131</td>\n",
       "      <td>12653</td>\n",
       "      <td>0.013245</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.549669</td>\n",
       "      <td>68</td>\n",
       "      <td>151</td>\n",
       "      <td>12.583333</td>\n",
       "      <td>17.166667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>10.750000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>37</td>\n",
       "      <td>1</td>\n",
       "      <td>0.491640</td>\n",
       "      <td>0.006066</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2964171.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4833291</th>\n",
       "      <td>206201</td>\n",
       "      <td>46640</td>\n",
       "      <td>0.012376</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>11.200000</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>0.569307</td>\n",
       "      <td>174</td>\n",
       "      <td>404</td>\n",
       "      <td>12.625000</td>\n",
       "      <td>10.375000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.774194</td>\n",
       "      <td>8.838710</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>40</td>\n",
       "      <td>8</td>\n",
       "      <td>0.566198</td>\n",
       "      <td>1.748985</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.129032</td>\n",
       "      <td>0.193548</td>\n",
       "      <td>0.064516</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>309211.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4833292 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id  product_id  u_p_order_rate  u_p_reorder_rate  u_p_avg_position  u_p_orders_since_last  max_streak  user_reorder_rate  user_unique_products  user_total_products  user_avg_cart_size  user_avg_days_between_orders  user_reordered_products_ratio  product_reorder_rate  avg_pos_incart  p_reduced_feat_1  p_reduced_feat_2  p_reduced_feat_3  aisle_id  department_id  aisle_reorder_rate  dept_reorder_rate  order_dow  order_hour_of_day  days_since_prior_order  hour_reorder_rate  day_reorder_rate  p_days_since_prior_order_reorder_rate  u_days_since_prior_order_reorder_rate  days_since_prior_reorder_rate   order_id\n",
       "0              3         248        0.011364          0.000000          3.000000                     10           0           0.625000                    33                   88            7.333333                     11.083333                       0.575758              0.401366       10.637178          1.000000          0.000000          0.000000       117             19            0.519358           0.043527        5.0                  1                    11.0           0.088316          0.123824                               0.022003                               0.056818                            0.0  2774568.0\n",
       "1             12         248        0.013514          0.000000         22.000000                      0           0           0.175676                    61                   74           14.800000                     20.000000                       0.147541              0.401366       10.637178          1.000000          0.000000          0.000000       117             19            0.519358           0.043527        1.0                  2                    30.0           0.036419          0.172838                               0.102124                               0.527027                            1.0  1356845.0\n",
       "2            418         248        0.005988          0.000000          3.000000                      3           0           0.359281                   107                  167           20.875000                     20.625000                       0.130841              0.401366       10.637178          1.000000          0.000000          0.000000       117             19            0.519358           0.043527        6.0                  3                    13.0           0.051593          0.145068                               0.025948                               0.137725                            0.0  3073553.0\n",
       "3            503         248        0.002994          0.000000         13.000000                     22           0           0.613772                   129                  334            9.277778                      9.777778                       0.054264              0.401366       10.637178          1.000000          0.000000          0.000000       117             19            0.519358           0.043527        1.0                  1                     8.0           0.075873          0.172838                               0.061760                               0.176647                            1.0  1490499.0\n",
       "4            720         248        0.009709          0.666667          4.333333                      2           1           0.618123                   118                  309           13.434783                     13.782609                       0.093220              0.401366       10.637178          1.000000          0.000000          0.000000       117             19            0.519358           0.043527        1.0                  1                    19.0           0.081032          0.172838                               0.009712                               0.000000                            0.0   391588.0\n",
       "...          ...         ...             ...               ...               ...                    ...         ...                ...                   ...                  ...                 ...                           ...                            ...                   ...             ...               ...               ...               ...       ...            ...                 ...                ...        ...                ...                     ...                ...               ...                                    ...                                    ...                            ...        ...\n",
       "4833287   205877       27049        0.001684          0.000000         32.000000                      5           0           0.501684                   296                  594           13.500000                      7.795455                       0.000000              0.363636       10.090909          0.000000          0.000000          0.000000        40              8            0.566198           1.748985        3.0                  0                    21.0           0.090909          0.181818                               0.000000                               0.000000                            0.0   913357.0\n",
       "4833288   205880       25635        0.014286          0.000000          1.000000                     10           0           0.242857                    53                   70            5.384615                      3.230769                       0.000000              0.666667        4.333333          0.000000          0.000000          0.000000        33              6            0.398439           0.603735        3.0                  3                     1.0           0.111111          0.000000                               0.000000                               0.000000                            0.0  2268059.0\n",
       "4833289   205893       11873        0.002786          0.000000         34.000000                      7           0           0.543175                   164                  359           22.437500                     17.125000                       0.000000              0.166667       13.833333          0.002308          0.999987          0.004505        80             11            0.238818           0.326152        0.0                  0                    21.0           0.000000          0.166667                               0.000000                               0.000000                            0.0  2072901.0\n",
       "4833290   206131       12653        0.013245          0.500000          7.000000                      0           0           0.549669                    68                  151           12.583333                     17.166667                       0.000000              0.250000       10.750000          0.000000          1.000000          0.000000        37              1            0.491640           0.006066        1.0                  1                    13.0           0.250000          0.000000                               0.000000                               0.000000                            0.0  2964171.0\n",
       "4833291   206201       46640        0.012376          0.800000         11.200000                     18           1           0.569307                   174                  404           12.625000                     10.375000                       0.000000              0.774194        8.838710          0.000000          0.000000          0.000000        40              8            0.566198           1.748985        0.0                  3                    11.0           0.129032          0.193548                               0.064516                               0.000000                            0.0   309211.0\n",
       "\n",
       "[4833292 rows x 31 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_orders_merge_df['order_hour_of_day'] = test_orders_merge_df['order_hour_of_day'].apply(hour_tocategorical)\n",
    "test_orders_merge_df = test_orders_merge_df[['user_id', 'product_id', 'u_p_order_rate', 'u_p_reorder_rate', 'u_p_avg_position', 'u_p_orders_since_last', 'max_streak', 'user_reorder_rate', 'user_unique_products', 'user_total_products', 'user_avg_cart_size', 'user_avg_days_between_orders', 'user_reordered_products_ratio', 'product_reorder_rate', 'avg_pos_incart', 'p_reduced_feat_1', 'p_reduced_feat_2', 'p_reduced_feat_3', 'aisle_id', 'department_id', 'aisle_reorder_rate', 'dept_reorder_rate', 'order_dow', 'order_hour_of_day', 'days_since_prior_order', 'hour_reorder_rate', 'day_reorder_rate', 'p_days_since_prior_order_reorder_rate', 'u_days_since_prior_order_reorder_rate', 'days_since_prior_reorder_rate', 'order_id']]\n",
    "test_orders_merge_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataframe size: 1.15 GB\n"
     ]
    }
   ],
   "source": [
    "from sys import getsizeof\n",
    "print('Dataframe size: %2.2f GB'%(getsizeof(test_orders_merge_df)/(1024.0**3)))\n",
    "test_orders_merge_df.to_csv(\"test_orders_merge_df_v6.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>user_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>u_p_order_rate</th>\n",
       "      <th>u_p_reorder_rate</th>\n",
       "      <th>u_p_avg_position</th>\n",
       "      <th>u_p_orders_since_last</th>\n",
       "      <th>max_streak</th>\n",
       "      <th>user_reorder_rate</th>\n",
       "      <th>user_unique_products</th>\n",
       "      <th>user_total_products</th>\n",
       "      <th>user_avg_cart_size</th>\n",
       "      <th>user_avg_days_between_orders</th>\n",
       "      <th>user_reordered_products_ratio</th>\n",
       "      <th>product_reorder_rate</th>\n",
       "      <th>avg_pos_incart</th>\n",
       "      <th>p_reduced_feat_1</th>\n",
       "      <th>p_reduced_feat_2</th>\n",
       "      <th>p_reduced_feat_3</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>aisle_reorder_rate</th>\n",
       "      <th>dept_reorder_rate</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>order_hour_of_day</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>hour_reorder_rate</th>\n",
       "      <th>day_reorder_rate</th>\n",
       "      <th>p_days_since_prior_order_reorder_rate</th>\n",
       "      <th>u_days_since_prior_order_reorder_rate</th>\n",
       "      <th>days_since_prior_reorder_rate</th>\n",
       "      <th>order_id</th>\n",
       "      <th>reordered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>196</td>\n",
       "      <td>0.157143</td>\n",
       "      <td>0.909091</td>\n",
       "      <td>1.363636</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.777843</td>\n",
       "      <td>3.732399</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>77</td>\n",
       "      <td>7</td>\n",
       "      <td>0.639301</td>\n",
       "      <td>0.000936</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.059092</td>\n",
       "      <td>0.152448</td>\n",
       "      <td>0.038340</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.181818</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>10258</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.600000</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.715610</td>\n",
       "      <td>4.268293</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>117</td>\n",
       "      <td>19</td>\n",
       "      <td>0.519358</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.061951</td>\n",
       "      <td>0.140488</td>\n",
       "      <td>0.040976</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>10326</td>\n",
       "      <td>0.014286</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.653439</td>\n",
       "      <td>4.227846</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>0.718823</td>\n",
       "      <td>0.005192</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.067765</td>\n",
       "      <td>0.124264</td>\n",
       "      <td>0.035648</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>12427</td>\n",
       "      <td>0.142857</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.740182</td>\n",
       "      <td>4.781245</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>19</td>\n",
       "      <td>0.592129</td>\n",
       "      <td>0.043527</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.066149</td>\n",
       "      <td>0.130208</td>\n",
       "      <td>0.042108</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>13032</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>6.500000</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.728571</td>\n",
       "      <td>19</td>\n",
       "      <td>70</td>\n",
       "      <td>6.363636</td>\n",
       "      <td>17.272727</td>\n",
       "      <td>0.631579</td>\n",
       "      <td>0.661117</td>\n",
       "      <td>5.632172</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>121</td>\n",
       "      <td>14</td>\n",
       "      <td>0.572233</td>\n",
       "      <td>0.315318</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.063781</td>\n",
       "      <td>0.142418</td>\n",
       "      <td>0.033811</td>\n",
       "      <td>0.242857</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>1187899</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  product_id  u_p_order_rate  u_p_reorder_rate  u_p_avg_position  u_p_orders_since_last  max_streak  user_reorder_rate  user_unique_products  user_total_products  user_avg_cart_size  user_avg_days_between_orders  user_reordered_products_ratio  product_reorder_rate  avg_pos_incart  p_reduced_feat_1  p_reduced_feat_2  p_reduced_feat_3  aisle_id  department_id  aisle_reorder_rate  dept_reorder_rate  order_dow  order_hour_of_day  days_since_prior_order  hour_reorder_rate  day_reorder_rate  p_days_since_prior_order_reorder_rate  u_days_since_prior_order_reorder_rate  days_since_prior_reorder_rate  order_id  reordered\n",
       "0        1         196        0.157143          0.909091          1.363636                      0           7           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.777843        3.732399               0.0               0.0               1.0        77              7            0.639301           0.000936          4                  0                    14.0           0.059092          0.152448                               0.038340                               0.242857                       0.181818   1187899        1.0\n",
       "1        1       10258        0.142857          0.900000          3.600000                      0           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.715610        4.268293               0.0               0.0               0.0       117             19            0.519358           0.043527          4                  0                    14.0           0.061951          0.140488                               0.040976                               0.242857                       0.200000   1187899        1.0\n",
       "2        1       10326        0.014286          0.000000          5.000000                      6           0           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.653439        4.227846               0.0               0.0               0.0        24              4            0.718823           0.005192          4                  0                    14.0           0.067765          0.124264                               0.035648                               0.242857                       0.000000   1187899        0.0\n",
       "3        1       12427        0.142857          0.900000          3.300000                      1           6           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.740182        4.781245               0.0               0.0               0.0        23             19            0.592129           0.043527          4                  0                    14.0           0.066149          0.130208                               0.042108                               0.242857                       0.100000   1187899        0.0\n",
       "4        1       13032        0.057143          0.750000          6.500000                      0           2           0.728571                    19                   70            6.363636                     17.272727                       0.631579              0.661117        5.632172               0.0               0.0               0.0       121             14            0.572233           0.315318          4                  0                    14.0           0.063781          0.142418                               0.033811                               0.242857                       0.250000   1187899        1.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"train_orders_merged_df_v6.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# End"
   ]
  },
  {
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}