exp/Instacart.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import time\n",
    "import os\n",
    "import sys\n",
    "import utils \n",
    "import itertools\n",
    "import random as rand\n",
    "import math\n",
    "import copy\n",
    "sys.path.append('../')\n",
    "sys.path.insert(1, '../exp')\n",
    "\n",
    "# sklearn\n",
    "from sklearn.model_selection import cross_validate, train_test_split, RandomizedSearchCV\n",
    "from sklearn.linear_model import LogisticRegression, LinearRegression\n",
    "from sklearn.metrics import mean_squared_error, r2_score\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier, RandomForestRegressor\n",
    "from sklearn import metrics\n",
    "from sklearn.feature_selection import mutual_info_classif\n",
    "from sklearn.utils import compute_class_weight\n",
    "\n",
    "import xgboost as xgb\n",
    "\n",
    "#PostgreSQL\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "# sqlgen\n",
    "import sqlgen\n",
    "from sqlgen import study, trial\n",
    "from sqlgen.samplers import tpe, random\n",
    "from sqlgen.importance._fanova import FanovaImportanceEvaluator\n",
    "from sqlgen.importance._mean_decrease_impurity import MeanDecreaseImpurityImportanceEvaluator\n",
    "\n",
    "# graph\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly\n",
    "\n",
    "# featuretools\n",
    "import featuretools as ft\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1. Load data & Join Table\n",
    "\n",
    "Since we only need two tables: 'User' table and 'Log' table, we will concate three tables(ie. orders, order_products and products) together.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of users: 1000 , number of logs: 157932\n",
      "training users: 646 training data: 104262\n",
      "test users: 354 test data: 53670\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>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",
       "      <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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3412510</td>\n",
       "      <td>427</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17948</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Frozen Organic Wild Blueberries</td>\n",
       "      <td>116</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>3412510</td>\n",
       "      <td>427</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24852</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>Banana</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2480</th>\n",
       "      <td>3412510</td>\n",
       "      <td>427</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28204</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Organic Fuji Apple</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2902</th>\n",
       "      <td>3412510</td>\n",
       "      <td>427</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23102</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>Organic 1% Milk</td>\n",
       "      <td>84</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2915</th>\n",
       "      <td>3412510</td>\n",
       "      <td>427</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>432</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>Vanilla Almond Breeze Almond Milk</td>\n",
       "      <td>91</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      order_id  user_id eval_set  order_number  order_dow  order_hour_of_day  \\\n",
       "0      3412510      427    prior             1          1                 18   \n",
       "136    3412510      427    prior             1          1                 18   \n",
       "2480   3412510      427    prior             1          1                 18   \n",
       "2902   3412510      427    prior             1          1                 18   \n",
       "2915   3412510      427    prior             1          1                 18   \n",
       "\n",
       "      days_since_prior_order  product_id  add_to_cart_order  reordered  \\\n",
       "0                        NaN       17948                  1          0   \n",
       "136                      NaN       24852                  2          0   \n",
       "2480                     NaN       28204                  3          0   \n",
       "2902                     NaN       23102                  4          0   \n",
       "2915                     NaN         432                  5          0   \n",
       "\n",
       "                           product_name  aisle_id  department_id  \n",
       "0       Frozen Organic Wild Blueberries       116              1  \n",
       "136                              Banana        24              4  \n",
       "2480                 Organic Fuji Apple        24              4  \n",
       "2902                    Organic 1% Milk        84             16  \n",
       "2915  Vanilla Almond Breeze Almond Milk        91             16  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = \"../exp_data/Instacart/\"\n",
    "order_products = pd.read_csv(os.path.join(path, \"order_products__prior.csv\"))\n",
    "orders = pd.read_csv(os.path.join(path, \"orders.csv\"))\n",
    "departments = pd.read_csv(os.path.join(path, \"departments.csv\"))\n",
    "products = pd.read_csv(os.path.join(path, \"products.csv\"))\n",
    "\n",
    "user = pd.DataFrame()\n",
    "user['user_id'] = orders[\"user_id\"]\n",
    "user = user.drop_duplicates(keep = 'first', inplace=False)\n",
    "\n",
    "log = orders.merge(order_products).merge(products).sort_values(by=['user_id', 'order_number'])\n",
    "print(\"number of users:\", len(user), \", number of logs:\", len(log))\n",
    "\n",
    "orders_train = orders[orders['eval_set']=='train']\n",
    "global user_train\n",
    "user_train = pd.DataFrame()\n",
    "user_train['user_id'] = orders_train['user_id']\n",
    "log_train = user_train.merge(orders).merge(order_products).merge(products).sort_values(by=['user_id', 'order_number'])\n",
    "print(\"training users:\", len(user_train), \"training data:\", len(log_train))\n",
    "\n",
    "orders_test = orders[orders['eval_set']=='test']\n",
    "user_test = pd.DataFrame()\n",
    "user_test['user_id'] = orders_test['user_id']\n",
    "log_test = user_test.merge(orders).merge(order_products).merge(products).sort_values(by=['user_id', 'order_number'])\n",
    "print(\"test users:\", len(user_test), \"test data:\", len(log_test))\n",
    "\n",
    "log.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert product_name into 0/1 \n",
    "Because 'product_name' attribute has too many distinct values, here we simplify the attribute with only two values. \n",
    "\n",
    "If the product name contains 'Banana', then product_name=True, otherwise, Flase. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# Banana: 3520 # non-Banana: 100742\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>user_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_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",
       "      <th>add_to_cart_order</th>\n",
       "      <th>reordered</th>\n",
       "      <th>aisle_id</th>\n",
       "      <th>department_id</th>\n",
       "      <th>product_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>21288</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>47766</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>676</th>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>21903</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>123</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1457</th>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>38313</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>50</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>23181</td>\n",
       "      <td>prior</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>35</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_id  order_id  product_id eval_set  order_number  order_dow  \\\n",
       "0         892   2557057       21288    prior             1          5   \n",
       "74        892   2557057       47766    prior             1          5   \n",
       "676       892   2557057       21903    prior             1          5   \n",
       "1457      892   2557057       38313    prior             1          5   \n",
       "1497      892   2557057       23181    prior             1          5   \n",
       "\n",
       "      order_hour_of_day  days_since_prior_order  add_to_cart_order  reordered  \\\n",
       "0                    19                     0.0                  1          0   \n",
       "74                   19                     0.0                  2          0   \n",
       "676                  19                     0.0                  3          0   \n",
       "1457                 19                     0.0                  4          0   \n",
       "1497                 19                     0.0                  5          0   \n",
       "\n",
       "      aisle_id  department_id  product_name  \n",
       "0           24              4             0  \n",
       "74          24              4             0  \n",
       "676        123              4             0  \n",
       "1457        50             19             1  \n",
       "1497        35             12             0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_product_name = log_train['product_name'].str.contains(r'Banana')\n",
    "log_train = log_train.drop('product_name', 1)\n",
    "log_train['product_name'] = new_product_name\n",
    "log_train = log_train[['user_id', 'order_id', 'product_id', 'eval_set', 'order_number', \n",
    "                      'order_dow', 'order_hour_of_day', 'days_since_prior_order', 'add_to_cart_order', \n",
    "                       'reordered', 'aisle_id', 'department_id', 'product_name']]\n",
    "log_train['product_name'] = log_train['product_name'].astype(int)\n",
    "log_train = log_train.fillna(0)\n",
    "print(\"# Banana:\", len(log_train[log_train['product_name']==True]), \"# non-Banana:\", len(log_train[log_train['product_name']==False]))\n",
    "log_train.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2. Make Labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of users buy banana 209\n"
     ]
    }
   ],
   "source": [
    "training = log_train[log_train['eval_set']=='train']\n",
    "has_Banana = {}\n",
    "for i, row in training.iterrows():\n",
    "    if row[-1] == True:\n",
    "        has_Banana.update({row[0]: 1})\n",
    "print('# of users buy banana', len(has_Banana))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "neg samples: 437\n",
      "pos samples: 209\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>user_id</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>892</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1048</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1662</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1849</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>2094</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_id  label\n",
       "27       892    0.0\n",
       "31      1048    0.0\n",
       "48      1662    0.0\n",
       "56      1849    0.0\n",
       "128     2094    1.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_train['label'] = np.nan\n",
    "for item in has_Banana.items():\n",
    "    user_train.loc[user_train['user_id'] == item[0], 'label'] = 1\n",
    "\n",
    "# fillna with 0 \n",
    "user_train = user_train.fillna(0)\n",
    "print(\"neg samples:\", len(user_train[user_train['label']==0]))\n",
    "print(\"pos samples:\", len(user_train[user_train['label']==1]))\n",
    "user_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(516, 1) (130, 1)\n"
     ]
    }
   ],
   "source": [
    "X_train, X_test, y_train, y_test = train_test_split(\n",
    "        user_train.drop(['label'], axis=1), user_train['label'], test_size=0.2, random_state=42)\n",
    "\n",
    "print(X_train.shape, X_test.shape)\n",
    "train_data = X_train\n",
    "train_data['label'] = y_train\n",
    "test_data = X_test\n",
    "test_data['label'] = y_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "global copy_train_data, copy_test_data, train_data, test_data\n",
    "global trail_auc_store, trail_mi_store, train_auc_store\n",
    "\n",
    "trail_auc_store = []\n",
    "trail_mi_store = []\n",
    "trail_args_store = []\n",
    "train_auc_store = []\n",
    "\n",
    "global random_args_log, tpe_args_log\n",
    "global random_user_train_log, tpe_user_train_log\n",
    "\n",
    "random_args_log = {}\n",
    "tpe_args_log = {}\n",
    "random_user_train_log = {}\n",
    "tpe_user_train_log = {}\n",
    "\n",
    "global eva_global, seed_global"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3. Store table in PostragSQL\n",
    "Store log_train to databse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'user': 'lydiazheng', 'channel_binding': 'prefer', 'dbname': 'auto_fg', 'host': '127.0.0.1', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'ssl_min_protocol_version': 'TLSv1.2', 'gssencmode': 'prefer', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} \n",
      "\n",
      "You are connected to -  ('PostgreSQL 13.3 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit',) \n",
      "\n",
      "log_train Table created successfully in PostgreSQL. \n",
      "\n",
      "\n",
      "PostgreSQL connection is closed\n"
     ]
    }
   ],
   "source": [
    "engine = utils.store_instacart(log_train)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4. Feature Generation & Machine Learning\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index_copy</th>\n",
       "      <th>user_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>department_id</th>\n",
       "      <th>order_dow</th>\n",
       "      <th>days_since_prior_order</th>\n",
       "      <th>unique_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>892_2557057_21288</td>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>21288</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>892_2557057_47766</td>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>47766</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>676</th>\n",
       "      <td>892_2557057_21903</td>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>21903</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1457</th>\n",
       "      <td>892_2557057_38313</td>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>38313</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1497</th>\n",
       "      <td>892_2557057_23181</td>\n",
       "      <td>892</td>\n",
       "      <td>2557057</td>\n",
       "      <td>23181</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             index_copy  user_id  order_id  product_id  product_name  \\\n",
       "0     892_2557057_21288      892   2557057       21288             0   \n",
       "74    892_2557057_47766      892   2557057       47766             0   \n",
       "676   892_2557057_21903      892   2557057       21903             0   \n",
       "1457  892_2557057_38313      892   2557057       38313             1   \n",
       "1497  892_2557057_23181      892   2557057       23181             0   \n",
       "\n",
       "      department_id  order_dow  days_since_prior_order  unique_id  \n",
       "0                 4          5                     0.0          0  \n",
       "74                4          5                     0.0          1  \n",
       "676               4          5                     0.0          2  \n",
       "1457             19          5                     0.0          3  \n",
       "1497             12          5                     0.0          4  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log_train[\"index\"] = log_train[\"user_id\"].astype(str) + \"_\" + log_train[\"order_id\"].astype(str) + \"_\" + log_train[\"product_id\"].astype(str)\n",
    "log_train = log_train[[\"index\", \"user_id\", \"order_id\", \"product_id\", \"product_name\", \"department_id\", \"order_dow\", \n",
    "                     \"days_since_prior_order\"]]\n",
    "\n",
    "log_train_copy = copy.deepcopy(log_train)\n",
    "log_train_copy = log_train_copy.rename({'index': 'index_copy'}, axis=1)\n",
    "log_train_copy['unique_id'] = range(0, len(log_train_copy))\n",
    "log_train_copy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.1 (20210417.1919)\n",
       " -->\n",
       "<!-- Title: instacart Pages: 1 -->\n",
       "<svg width=\"225pt\" height=\"243pt\"\n",
       " viewBox=\"0.00 0.00 225.00 243.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 239)\">\n",
       "<title>instacart</title>\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-239 221,-239 221,4 -4,4\"/>\n",
       "<!-- log_train -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>log_train</title>\n",
       "<polygon fill=\"none\" stroke=\"black\" points=\"0,-83.5 0,-234.5 217,-234.5 217,-83.5 0,-83.5\"/>\n",
       "<text text-anchor=\"middle\" x=\"108.5\" y=\"-219.3\" font-family=\"Times,serif\" font-size=\"14.00\">log_train (104262 rows)</text>\n",
       "<polyline fill=\"none\" stroke=\"black\" points=\"0,-211.5 217,-211.5 \"/>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-196.3\" font-family=\"Times,serif\" font-size=\"14.00\">index : index</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-181.3\" font-family=\"Times,serif\" font-size=\"14.00\">user_id : id</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-166.3\" font-family=\"Times,serif\" font-size=\"14.00\">order_id : id</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-151.3\" font-family=\"Times,serif\" font-size=\"14.00\">product_id : id</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-136.3\" font-family=\"Times,serif\" font-size=\"14.00\">order_dow : numeric</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-121.3\" font-family=\"Times,serif\" font-size=\"14.00\">days_since_prior_order : time_index</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-106.3\" font-family=\"Times,serif\" font-size=\"14.00\">department_id : numeric</text>\n",
       "<text text-anchor=\"start\" x=\"8\" y=\"-91.3\" font-family=\"Times,serif\" font-size=\"14.00\">product_name : numeric</text>\n",
       "</g>\n",
       "<!-- users -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>users</title>\n",
       "<polygon fill=\"none\" stroke=\"black\" points=\"55,-0.5 55,-46.5 162,-46.5 162,-0.5 55,-0.5\"/>\n",
       "<text text-anchor=\"middle\" x=\"108.5\" y=\"-31.3\" font-family=\"Times,serif\" font-size=\"14.00\">users (646 rows)</text>\n",
       "<polyline fill=\"none\" stroke=\"black\" points=\"55,-23.5 162,-23.5 \"/>\n",
       "<text text-anchor=\"start\" x=\"63\" y=\"-8.3\" font-family=\"Times,serif\" font-size=\"14.00\">user_id : index</text>\n",
       "</g>\n",
       "<!-- log_train&#45;&gt;users -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>log_train&#45;&gt;users</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M108.5,-83.33C108.5,-83.33 108.5,-56.67 108.5,-56.67\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"112,-56.67 108.5,-46.67 105,-56.67 112,-56.67\"/>\n",
       "<text text-anchor=\"middle\" x=\"88.5\" y=\"-58.8\" font-family=\"Times,serif\" font-size=\"14.00\">user_id</text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x7fd106515a20>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log_train_vtypes = {\n",
    "    \"index\": ft.variable_types.Index,\n",
    "    \"user_id\": ft.variable_types.Id,\n",
    "    \"order_id\": ft.variable_types.Id,\n",
    "    \"product_id\": ft.variable_types.Id,\n",
    "    \"order_dow\": ft.variable_types.Numeric,\n",
    "    \"days_since_prior_order\": ft.variable_types.TimeIndex,\n",
    "    \"department_id\": ft.variable_types.Numeric,\n",
    "    \"product_name\": ft.variable_types.Numeric\n",
    "}\n",
    "\n",
    "es = ft.EntitySet(\"instacart\")\n",
    "es.entity_from_dataframe(entity_id=\"log_train\",\n",
    "                         dataframe=log_train,\n",
    "                         index=\"index\",\n",
    "                         variable_types=log_train_vtypes)\n",
    "\n",
    "es.normalize_entity(base_entity_id=\"log_train\", new_entity_id=\"users\", index=\"user_id\")\n",
    "\n",
    "es.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Built 9 features\n",
      "Elapsed: 00:00 | Progress: 100%|██████████\n",
      "Time: 0.1331179141998291\n"
     ]
    }
   ],
   "source": [
    "start = time.time()\n",
    "feature_matrix, features = ft.dfs(target_entity=\"users\",\n",
    "                                  agg_primitives = [\"sum\", \"min\", \"max\", \"count\", \"mean\"],\n",
    "#                                   where_primitives = [\"count\"],\n",
    "                                  trans_primitives = [],\n",
    "                                  ignore_variables={\"log_train\":[\"order_id\", \"product_id\", \"department_id\"]},\n",
    "                                  entityset=es,\n",
    "                                  verbose=True)\n",
    "end = time.time()\n",
    "print(\"Time:\", end-start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>SUM(log_train.product_name)</th>\n",
       "      <th>SUM(log_train.order_dow)</th>\n",
       "      <th>MIN(log_train.product_name)</th>\n",
       "      <th>MIN(log_train.order_dow)</th>\n",
       "      <th>MAX(log_train.product_name)</th>\n",
       "      <th>MAX(log_train.order_dow)</th>\n",
       "      <th>COUNT(log_train)</th>\n",
       "      <th>MEAN(log_train.product_name)</th>\n",
       "      <th>MEAN(log_train.order_dow)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>892</th>\n",
       "      <td>1</td>\n",
       "      <td>453</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>128</td>\n",
       "      <td>0.007812</td>\n",
       "      <td>3.539062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1048</th>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>35</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.771429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1662</th>\n",
       "      <td>0</td>\n",
       "      <td>44</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.588235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1849</th>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.928571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2094</th>\n",
       "      <td>30</td>\n",
       "      <td>1504</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>525</td>\n",
       "      <td>0.057143</td>\n",
       "      <td>2.864762</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SUM(log_train.product_name)  SUM(log_train.order_dow)  \\\n",
       "user_id                                                          \n",
       "892                                1                       453   \n",
       "1048                               0                        62   \n",
       "1662                               0                        44   \n",
       "1849                               0                        27   \n",
       "2094                              30                      1504   \n",
       "\n",
       "         MIN(log_train.product_name)  MIN(log_train.order_dow)  \\\n",
       "user_id                                                          \n",
       "892                                0                         0   \n",
       "1048                               0                         1   \n",
       "1662                               0                         0   \n",
       "1849                               0                         0   \n",
       "2094                               0                         0   \n",
       "\n",
       "         MAX(log_train.product_name)  MAX(log_train.order_dow)  \\\n",
       "user_id                                                          \n",
       "892                                1                         6   \n",
       "1048                               0                         4   \n",
       "1662                               0                         5   \n",
       "1849                               0                         6   \n",
       "2094                               1                         6   \n",
       "\n",
       "         COUNT(log_train)  MEAN(log_train.product_name)  \\\n",
       "user_id                                                   \n",
       "892                   128                      0.007812   \n",
       "1048                   35                      0.000000   \n",
       "1662                   17                      0.000000   \n",
       "1849                   14                      0.000000   \n",
       "2094                  525                      0.057143   \n",
       "\n",
       "         MEAN(log_train.order_dow)  \n",
       "user_id                             \n",
       "892                       3.539062  \n",
       "1048                      1.771429  \n",
       "1662                      2.588235  \n",
       "1849                      1.928571  \n",
       "2094                      2.864762  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_matrix.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get Train & Test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_data = train_data.merge(feature_matrix, on='user_id', how=\"left\")\n",
    "test_data = test_data.merge(feature_matrix, on='user_id', how=\"left\")\n",
    "\n",
    "copy_train_data = copy.deepcopy(train_data)\n",
    "copy_test_data = copy.deepcopy(test_data)\n",
    "\n",
    "train_data = train_data.reindex(sorted(train_data.columns), axis=1)\n",
    "test_data = test_data.reindex(sorted(test_data.columns), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>label</th>\n",
       "      <th>SUM(log_train.product_name)</th>\n",
       "      <th>SUM(log_train.order_dow)</th>\n",
       "      <th>MIN(log_train.product_name)</th>\n",
       "      <th>MIN(log_train.order_dow)</th>\n",
       "      <th>MAX(log_train.product_name)</th>\n",
       "      <th>MAX(log_train.order_dow)</th>\n",
       "      <th>COUNT(log_train)</th>\n",
       "      <th>MEAN(log_train.product_name)</th>\n",
       "      <th>MEAN(log_train.order_dow)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12041</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>262</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>104</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.519231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91188</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1444</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>477</td>\n",
       "      <td>0.027254</td>\n",
       "      <td>3.027254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>188359</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>151</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>46</td>\n",
       "      <td>0.021739</td>\n",
       "      <td>3.282609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>206038</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>332</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>87</td>\n",
       "      <td>0.034483</td>\n",
       "      <td>3.816092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>95764</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>298</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>111</td>\n",
       "      <td>0.027027</td>\n",
       "      <td>2.684685</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  label  SUM(log_train.product_name)  SUM(log_train.order_dow)  \\\n",
       "0    12041    0.0                            0                       262   \n",
       "1    91188    0.0                           13                      1444   \n",
       "2   188359    0.0                            1                       151   \n",
       "3   206038    1.0                            3                       332   \n",
       "4    95764    0.0                            3                       298   \n",
       "\n",
       "   MIN(log_train.product_name)  MIN(log_train.order_dow)  \\\n",
       "0                            0                         1   \n",
       "1                            0                         0   \n",
       "2                            0                         1   \n",
       "3                            0                         0   \n",
       "4                            0                         0   \n",
       "\n",
       "   MAX(log_train.product_name)  MAX(log_train.order_dow)  COUNT(log_train)  \\\n",
       "0                            0                         6               104   \n",
       "1                            1                         6               477   \n",
       "2                            1                         5                46   \n",
       "3                            1                         6                87   \n",
       "4                            1                         5               111   \n",
       "\n",
       "   MEAN(log_train.product_name)  MEAN(log_train.order_dow)  \n",
       "0                      0.000000                   2.519231  \n",
       "1                      0.027254                   3.027254  \n",
       "2                      0.021739                   3.282609  \n",
       "3                      0.034483                   3.816092  \n",
       "4                      0.027027                   2.684685  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLGEN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set # of evaluation of opt algo\n",
    "global res_x \n",
    "res_x = list()\n",
    "\n",
    "aggregation = ['SUM', 'MIN', 'MAX', 'COUNT', 'AVG'] \n",
    "categorical_m = ['product_name', 'order_dow']\n",
    "categorical = ['product_name', 'department_id', 'order_dow']\n",
    "numerical = ['days_since_prior_order']\n",
    "attributes = categorical + numerical"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_user_train(args, train_data):  \n",
    "    agg, m, lb_day, ub_day, w_cat_value_0, w_cat_value_1, w_cat_value_2 = args \n",
    "    agg = aggregation[agg]\n",
    "    m = categorical_m[m]\n",
    "    \n",
    "    w_cat_value_temp = [w_cat_value_0, w_cat_value_1, w_cat_value_2]\n",
    "    w_cat = []\n",
    "    w_cat_value = []\n",
    "\n",
    "    for i in range(len(categorical)):\n",
    "        if w_cat_value_temp[i] != -1:\n",
    "            w_cat.append(categorical[i])\n",
    "            w_cat_value.append(w_cat_value_temp[i])\n",
    "            \n",
    "    sql_output = utils.generate_feature_in_small_space(engine, agg, m, lb_day, ub_day, w_cat, w_cat_value) \n",
    "    w_cat_str = \"_\".join(str(w_cat[x])+'='+str(w_cat_value[x]) for x in range(len(w_cat)))\n",
    "    new_feature = pd.DataFrame(sql_output, columns = ['user_id', agg+'('+m+')_'+w_cat_str+'_'+'lb_day='+str(lb_day)+\"_ub_day=\"+str(ub_day)]) #+'='+g_cat\n",
    "    new_feature = new_feature.astype('float')\n",
    "    new_train_data = train_data.merge(new_feature, how='left')\n",
    "    \n",
    "    return new_train_data, args"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define an objective function\n",
    "\n",
    "def objective(trial):\n",
    "    agg = trial.suggest_categorical('agg', np.array([i for i in range(len(aggregation))]))\n",
    "    m = trial.suggest_categorical('m', np.array([i for i in range(len(categorical_m))]))  \n",
    "    lb_day = trial.suggest_int('lb_day', 0,14)\n",
    "    ub_day = trial.suggest_int('ub_day', 15,30)\n",
    "    w_cat_value_0 = trial.suggest_categorical('w_cat_value_0', np.append(log_train[categorical[0]].unique(), [-1], 0))\n",
    "    w_cat_value_1 = trial.suggest_categorical('w_cat_value_1', np.append(log_train[categorical[1]].unique(), [-1], 0))\n",
    "    w_cat_value_2 = trial.suggest_categorical('w_cat_value_2', np.append(log_train[categorical[2]].unique(), [-1], 0))\n",
    "    \n",
    "    args = [agg, m, lb_day, ub_day, w_cat_value_0, w_cat_value_1, w_cat_value_2]\n",
    "    new_train_data, args = update_user_train(args, train_data)\n",
    "    score, _ = utils.model(new_train_data)\n",
    "    \n",
    "    for x in res_x:\n",
    "        if args == x:\n",
    "            score -= 0.2\n",
    "            break\n",
    "            \n",
    "    print(args, \" Accuracy: \", score)\n",
    "    return score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a NEW function with mutual information\n",
    "\n",
    "def mi(trial):\n",
    "    agg = trial.suggest_categorical('agg', np.array([i for i in range(len(aggregation))]))\n",
    "    m = trial.suggest_categorical('m', np.array([i for i in range(len(categorical_m))]))  \n",
    "    lb_day = trial.suggest_int('lb_day', 0,14)\n",
    "    ub_day = trial.suggest_int('ub_day', 15,30)\n",
    "    w_cat_value_0 = trial.suggest_categorical('w_cat_value_0', np.append(log_train[categorical[0]].unique(), [-1], 0))\n",
    "    w_cat_value_1 = trial.suggest_categorical('w_cat_value_1', np.append(log_train[categorical[1]].unique(), [-1], 0))\n",
    "    w_cat_value_2 = trial.suggest_categorical('w_cat_value_2', np.append(log_train[categorical[2]].unique(), [-1], 0))\n",
    "    \n",
    "    args = [agg, m, lb_day, ub_day, w_cat_value_0, w_cat_value_1, w_cat_value_2]\n",
    "    new_train_data, args = update_user_train(args, train_data)\n",
    "\n",
    "    mi_matrix = mutual_info_classif(new_train_data.fillna(0), new_train_data['label'], random_state=0)\n",
    "    mi_score = mi_matrix[-1]\n",
    "    \n",
    "    for x in res_x:\n",
    "        if args == x:\n",
    "            mi_score = 0\n",
    "            break\n",
    "            \n",
    "    trail_mi_store.append(mi_score)\n",
    "    trail_args_store.append(args)\n",
    "    print(args, \"MI:\", mi_score)\n",
    "    return mi_score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Random sampling \n",
    "def evaluate(trial_arr, mi_log, train_size):\n",
    "\n",
    "    x_features = pd.DataFrame()\n",
    "    x_features['index'] = range(0, len(mi_log))\n",
    "    x_features['mi'] = mi_log\n",
    "    x_features['agg'] = [trial_arr[i]['agg'] for i in range(len(trial_arr))]\n",
    "    x_features['m'] = [trial_arr[i]['m'] for i in range(len(trial_arr))]\n",
    "    x_features['lb_day'] = [trial_arr[i]['lb_day'] for i in range(len(trial_arr))]\n",
    "    x_features['ub_day'] = [trial_arr[i]['ub_day'] for i in range(len(trial_arr))]\n",
    "    x_features['w_cat_value_0'] = [trial_arr[i]['w_cat_value_0'] for i in range(len(trial_arr))]\n",
    "    x_features['w_cat_value_1'] = [trial_arr[i]['w_cat_value_1'] for i in range(len(trial_arr))]\n",
    "    x_features['w_cat_value_2'] = [trial_arr[i]['w_cat_value_2'] for i in range(len(trial_arr))]\n",
    "    \n",
    "    print(x_features.shape)\n",
    "    x_train, x_test = train_test_split(x_features, train_size=train_size, random_state=0)\n",
    "\n",
    "    y_acc = []\n",
    "    for index, feature in x_train.iterrows():\n",
    "        args = [int(feature['agg']),\n",
    "               int(feature['m']),\n",
    "               int(feature['lb_day']),\n",
    "               int(feature['ub_day']),\n",
    "               int(feature['w_cat_value_0']),\n",
    "               int(feature['w_cat_value_1']),\n",
    "               int(feature['w_cat_value_2'])] \n",
    "        new_train_data, args = update_user_train(args, train_data)\n",
    "        \n",
    "        y_acc.append(utils.model(new_train_data)[0])\n",
    "\n",
    "    y_train = pd.DataFrame()\n",
    "    y_train['index'] = x_train['index']\n",
    "    y_train['label'] = y_acc\n",
    "    \n",
    "    global clf\n",
    "    clf = RandomForestRegressor(random_state=0)\n",
    "    clf.fit(x_train[['mi']], y_train['label'])\n",
    "    \n",
    "    global estimated_accuracy\n",
    "    estimated_accuracy = clf.predict(x_features[['mi']])\n",
    "    predict_y = pd.DataFrame()\n",
    "    predict_y['index'] = range(0, len(mi_log))\n",
    "    predict_y['label'] = estimated_accuracy\n",
    "    \n",
    "    for index, row in y_train.iterrows():\n",
    "        predict_y.loc[predict_y['index'] == row['index'], 'label'] = row['label']\n",
    "    \n",
    "    best_trail_index = y_train.nlargest(1, ['label']).index[0]\n",
    "    train_auc_store.append(predict_y['label'].to_numpy())\n",
    "    return predict_y['label'].to_numpy(), best_trail_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "top = 5\n",
    "\n",
    "n_calls = [1500] \n",
    "random_state_arr = [0] \n",
    "train_size_arr = [100]\n",
    "all_store = []\n",
    "for n_call in n_calls:\n",
    "    tpe_args_log[n_call] = {}\n",
    "    tpe_user_train_log[n_call] = {}\n",
    "    for seed in random_state_arr:\n",
    "        tpe_args_log[n_call][seed] = []"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLGEN - Opt3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eva_store_optuna = []\n",
    "train_auc_store\n",
    "start = time.time()\n",
    "\n",
    "for eva in n_calls:\n",
    "    print(\"trails:\", eva)\n",
    "    eva_global = eva\n",
    "    seed_store = []\n",
    "    for seed in random_state_arr:\n",
    "        seed_global = seed\n",
    "        global train_data, res_x \n",
    "        res_optuna_lst = []\n",
    "        res_optuna_fun = []\n",
    "        res_x = list()\n",
    "        train_data = copy_train_data\n",
    "        print(\"seed:\", seed)\n",
    "\n",
    "        for i in range(top):\n",
    "            tpe_result = study.create_study(\n",
    "                direction=\"maximize\", \n",
    "                sampler=tpe.TPESampler(n_startup_trials=20, seed=seed)) #\n",
    "            tpe_result.optimize(objective, n_trials=eva, \n",
    "                                mi_initializer=mi, evaluate=evaluate, mi_init_trails=1000, train_size=100)\n",
    "\n",
    "            best_trial = []\n",
    "            for key, value in tpe_result.best_trial.params.items():\n",
    "                best_trial.append(value)\n",
    "            res_x.append(best_trial)\n",
    "\n",
    "            train_data, args = update_user_train(best_trial, train_data)\n",
    "            res_optuna_lst.append(tpe_result.best_trial.params)\n",
    "            res_optuna_fun.append(tpe_result.best_value)\n",
    "\n",
    "            print(tpe_result.best_trial.params, tpe_result.best_value)\n",
    "            print(train_data.shape)\n",
    "            tpe_args_log[eva][seed].append(args)\n",
    "        seed_store.append(res_optuna_fun)\n",
    "        tpe_user_train_log[eva][seed] = train_data\n",
    "\n",
    "    eva_store_optuna.append(seed_store)\n",
    "end = time.time()\n",
    "print(\"Time:\", end-start)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Evaluate Test Accuracy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def model_instacart(user_train):\n",
    "    y = user_train['label']\n",
    "    y = y.to_frame()\n",
    "    X = user_train.drop(['label'], axis=1)\n",
    "    X = X.fillna(0)\n",
    "\n",
    "    clf = RandomForestClassifier(random_state=0)\n",
    "    auc = cross_validate(clf, X, y, cv=5,scoring=('accuracy'), return_train_score=True, n_jobs=-1, return_estimator=True)\n",
    "    valid_auc = auc['test_score'].mean()\n",
    "    train_auc = auc['train_score'].mean()\n",
    "\n",
    "    return train_auc, valid_auc, auc['estimator'][0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_auc = {}\n",
    "train_auc = {}\n",
    "valid_auc = {}\n",
    "for n_calls in tpe_args_log:\n",
    "    for seed in tpe_args_log[n_calls]:\n",
    "        test_auc[seed] = []\n",
    "        train_auc[seed] = []\n",
    "        valid_auc[seed] = []\n",
    "        train_data = copy_train_data\n",
    "        test_data = copy_test_data\n",
    "        \n",
    "        # Default AUC without new features\n",
    "        train, valid, clf = model_instacart(train_data)\n",
    "        train_auc[seed].append(train)\n",
    "        valid_auc[seed].append(valid)\n",
    "        test_pred = clf.predict(test_data.drop(columns='label', axis=1))\n",
    "        test_auc[seed].append(metrics.accuracy_score(test_data['label'], test_pred))\n",
    "        for args in tpe_args_log[n_calls][seed]:\n",
    "            test_data, config = update_user_train(args, test_data)\n",
    "            train_data, config = update_user_train(args, train_data)\n",
    "            train, valid, clf = model_instacart(train_data)\n",
    "            train_auc[seed].append(train)\n",
    "            valid_auc[seed].append(valid)\n",
    "            \n",
    "            test_data = test_data.fillna(0)\n",
    "            test_pred = clf.predict(test_data.drop(columns='label', axis=1))\n",
    "            test_auc[seed].append(metrics.accuracy_score(test_data['label'], test_pred))\n",
    "train_auc, valid_auc, test_auc"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "auto_fg",
   "language": "python",
   "name": "auto_fg"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}