Time Series Forecasting with Stacked Machine Learning Models_DEMO.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T14:03:33.715545Z",
     "start_time": "2019-07-29T14:03:32.536643Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import everything we need\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import datetime\n",
    "from datetime import datetime\n",
    "from datetime import datetime as dt\n",
    "\n",
    "from sklearn.model_selection import cross_val_score, TimeSeriesSplit, RandomizedSearchCV, GridSearchCV\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "scaler = StandardScaler()\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.linear_model import LassoCV, RidgeCV\n",
    "import xgboost\n",
    "from xgboost import XGBRegressor \n",
    "import lightgbm as lgb\n",
    "\n",
    "from plotly import __version__ \n",
    "from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot\n",
    "from plotly import graph_objs as go\n",
    "import chart_studio.plotly\n",
    "import cufflinks as cf\n",
    "cf.go_offline()\n",
    "cf.set_config_file(offline=False, world_readable=True)\n",
    "init_notebook_mode(connected = True)\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T14:03:52.744560Z",
     "start_time": "2019-07-29T14:03:52.728194Z"
    },
    "code_folding": [
     1,
     22,
     41
    ]
   },
   "outputs": [],
   "source": [
    "# Function better understanding the tables\n",
    "def feature_summary(df_fa):\n",
    "    print('DataFrame shape')\n",
    "    print('rows:',df_fa.shape[0])\n",
    "    print('cols:',df_fa.shape[1])\n",
    "    col_list=['Null','Unique_Count','Data_type','Max/Min','Mean','Std','Skewness','Sample_values']\n",
    "    df=pd.DataFrame(index=df_fa.columns,columns=col_list)\n",
    "    df['Null']=list([len(df_fa[col][df_fa[col].isnull()]) for i,col in enumerate(df_fa.columns)])\n",
    "    #df['%_Null']=list([len(df_fa[col][df_fa[col].isnull()])/df_fa.shape[0]*100 for i,col in enumerate(df_fa.columns)])\n",
    "    df['Unique_Count']=list([len(df_fa[col].unique()) for i,col in enumerate(df_fa.columns)])\n",
    "    df['Data_type']=list([df_fa[col].dtype for i,col in enumerate(df_fa.columns)])\n",
    "    for i,col in enumerate(df_fa.columns):\n",
    "        if 'float' in str(df_fa[col].dtype) or 'int' in str(df_fa[col].dtype):\n",
    "            df.at[col,'Max/Min']=str(round(df_fa[col].max(),2))+'/'+str(round(df_fa[col].min(),2))\n",
    "            df.at[col,'Mean']=df_fa[col].mean()\n",
    "            df.at[col,'Std']=df_fa[col].std()\n",
    "            df.at[col,'Skewness']=df_fa[col].skew()\n",
    "        df.at[col,'Sample_values']=list(df_fa[col].unique())\n",
    "           \n",
    "    return(df.fillna('-'))\n",
    "\n",
    "# Split data in a different way\n",
    "def timeseries_train_test_split(X, y, test_size):\n",
    "    \"\"\"\n",
    "        Perform train-test split with respect to time series structure\n",
    "    \"\"\"\n",
    "    \n",
    "    # get the index after which test set starts\n",
    "    test_index = int(len(X)*(1-test_size))\n",
    "    \n",
    "    X_train = X.iloc[:test_index]\n",
    "    y_train = y.iloc[:test_index]\n",
    "    X_test = X.iloc[test_index:]\n",
    "    y_test = y.iloc[test_index:]\n",
    "    \n",
    "    return X_train, X_test, y_train, y_test\n",
    "\n",
    "# for time-series cross-validation set 5 folds \n",
    "tscv = TimeSeriesSplit(n_splits=5)\n",
    "\n",
    "# Metric\n",
    "def mean_absolute_percentage_error(y_true, y_pred): \n",
    "    return np.mean(np.abs((y_true - y_pred) / y_true)) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T14:09:21.122683Z",
     "start_time": "2019-07-29T14:04:11.993220Z"
    }
   },
   "outputs": [],
   "source": [
    "# A function read data from my database and dropped some rows by some simple requirements\n",
    "df = loadData('***') \n",
    "\n",
    "# There're some accounts stopping selling products after 2019, so I dropped as we don't need to predict these accounts\n",
    "df = dropAccByRatio2019(df, 0.06) \n",
    "\n",
    "# Dropped account and product less than 6 records\n",
    "df = dropAccPrdLessRecord(df, 6) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T14:09:21.242253Z",
     "start_time": "2019-07-29T14:09:21.164843Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# Modeling for a specific account and getting the metric results\n",
    "def modelingFor5(Account):\n",
    "    df_model = df[df['Account'] == Account]\n",
    "    df_timeseries = pd.DataFrame(df_model.groupby(['Date'])['NetAmount'].sum()).sort_index(axis=0)\n",
    "    \n",
    "    data = pd.DataFrame(df_timeseries.NetAmount)\n",
    "    data.columns = [\"y\"]\n",
    " \n",
    "    # Drop data after 2019-06\n",
    "    data = data.loc[data.index[:-1]]\n",
    "    \n",
    "    # Adding the lag of the target variable from 7 steps back up to 48 months ago\n",
    "    for i in range(7, 48):\n",
    "        data[\"lag_{}\".format(i)] = data.y.shift(i)\n",
    "        \n",
    "    y = data.dropna().y\n",
    "    X = data.dropna().drop(['y'], axis=1)\n",
    "\n",
    "    # Reserve 30% of data for testing\n",
    "    X_train, X_test, y_train, y_test = timeseries_train_test_split(X, y, test_size=0.3)\n",
    "    \n",
    "    # Scaling\n",
    "    X_train_scaled = scaler.fit_transform(X_train)\n",
    "    X_test_scaled = scaler.transform(X_test)\n",
    "\n",
    "    # Linear Regression \n",
    "    lr = LinearRegression()\n",
    "    lr.fit(X_train_scaled, y_train)\n",
    "    prediction1 = lr.predict(X_test_scaled)\n",
    "    error_linear = mean_absolute_percentage_error(prediction1, y_test)\n",
    "\n",
    "    # Ridge\n",
    "    ridge = RidgeCV(cv=tscv)\n",
    "    ridge.fit(X_train_scaled, y_train)\n",
    "    prediction2 = ridge.predict(X_test_scaled)\n",
    "    error_ridge = mean_absolute_percentage_error(prediction2, y_test)\n",
    "      \n",
    "    # Lasso\n",
    "    lasso = LassoCV(cv=tscv)\n",
    "    lasso.fit(X_train_scaled, y_train)\n",
    "    prediction3 = lasso.predict(X_test_scaled)\n",
    "    error_lasso = mean_absolute_percentage_error(prediction3, y_test)\n",
    "      \n",
    "    # XGB\n",
    "    xgb = XGBRegressor(base_score=0.5, booster='gbtree', colsample_bylevel=1,\n",
    "         colsample_bynode=1, colsample_bytree=0.3, gamma=0,\n",
    "         importance_type='gain', learning_rate=0.1, max_delta_step=0,\n",
    "         max_depth=4, min_child_weight=1, missing=None, n_estimators=100,\n",
    "         n_jobs=1, nthread=None, objective='reg:linear', random_state=0,\n",
    "         reg_alpha=0, reg_lambda=1, scale_pos_weight=1, seed=None,\n",
    "         silent=None, subsample=0.5, verbosity=1)   \n",
    "    xgb.fit(X_train_scaled, y_train)\n",
    "    prediction4 = xgb.predict(X_test_scaled)\n",
    "    error_xgb = mean_absolute_percentage_error(prediction4, y_test)\n",
    "\n",
    "    # LightGBM\n",
    "    lgb_train = lgb.Dataset(X_train_scaled, y_train)\n",
    "    lgb_eval = lgb.Dataset(X_test_scaled, y_test, reference=lgb_train)\n",
    "    lightgbm_params = {'boosting_type': 'gbdt', \n",
    "         'colsample_bytree': 0.65, \n",
    "         'learning_rate': 0.001, \n",
    "         'n_estimators': 20, \n",
    "         'num_leaves': 3, \n",
    "         'reg_alpha': 0.5, \n",
    "         'reg_lambda': 0.5, \n",
    "         'subsample': 0.7}\n",
    "    gbm = lgb.train(lightgbm_params,lgb_train,num_boost_round=10,valid_sets=lgb_eval)\n",
    "    prediction5 = gbm.predict(X_test_scaled)\n",
    "    error_lightgbm = mean_absolute_percentage_error(prediction5, y_test)\n",
    "    \n",
    "    # Stacking\n",
    "    stack_error = mean_absolute_percentage_error(prediction1*0.1+prediction2*0.075+prediction3*0.075+prediction4*0.35+prediction5*0.4, y_test)\n",
    "    l1 = [error_linear, error_ridge, error_lasso, error_xgb, error_lightgbm, stack_error, \n",
    "    (error_linear+error_ridge+error_lasso+error_xgb+error_lightgbm)/5, \n",
    "    min(error_linear, error_ridge, error_lasso, error_xgb, error_lightgbm)]\n",
    "\n",
    "    global df_final\n",
    "    df_final = pd.DataFrame({Account:l1}).T\n",
    "    df_final.columns = ['linear', 'ridge', 'lasso', 'xgb', 'lightgbm', 'stack_error', 'avg_error', 'min_error']\n",
    "\n",
    "    return df_final\n",
    "\n",
    "# Repeat modeling function for each account\n",
    "def getMetics(Accountlist):\n",
    "    df = modelingFor5(l1[0])\n",
    "    for i in Accountlist[1:]:\n",
    "        df = df.append(modelingFor5(i))\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T15:05:08.530566Z",
     "start_time": "2019-07-29T15:05:08.515685Z"
    }
   },
   "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>Account</th>\n",
       "      <th>linear</th>\n",
       "      <th>ridge</th>\n",
       "      <th>lasso</th>\n",
       "      <th>xgb</th>\n",
       "      <th>lightgbm</th>\n",
       "      <th>stack_error</th>\n",
       "      <th>avg_error</th>\n",
       "      <th>min_error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4.723653</td>\n",
       "      <td>3.961553</td>\n",
       "      <td>8.215475</td>\n",
       "      <td>6.680395</td>\n",
       "      <td>9.388411</td>\n",
       "      <td>6.690356</td>\n",
       "      <td>6.593897</td>\n",
       "      <td>3.961553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>12.372634</td>\n",
       "      <td>12.273017</td>\n",
       "      <td>13.172614</td>\n",
       "      <td>7.287424</td>\n",
       "      <td>7.313861</td>\n",
       "      <td>7.939729</td>\n",
       "      <td>10.483910</td>\n",
       "      <td>7.287424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9.967715</td>\n",
       "      <td>10.607925</td>\n",
       "      <td>9.849556</td>\n",
       "      <td>11.932323</td>\n",
       "      <td>8.882980</td>\n",
       "      <td>8.674076</td>\n",
       "      <td>10.248100</td>\n",
       "      <td>8.882980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>9.418262</td>\n",
       "      <td>9.388396</td>\n",
       "      <td>9.542745</td>\n",
       "      <td>6.874848</td>\n",
       "      <td>10.285188</td>\n",
       "      <td>6.939145</td>\n",
       "      <td>9.101888</td>\n",
       "      <td>6.874848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>10.057956</td>\n",
       "      <td>9.129196</td>\n",
       "      <td>13.594823</td>\n",
       "      <td>5.789648</td>\n",
       "      <td>7.494531</td>\n",
       "      <td>4.291907</td>\n",
       "      <td>9.213231</td>\n",
       "      <td>5.789648</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Account     linear      ridge      lasso        xgb   lightgbm  \\\n",
       "0        0   4.723653   3.961553   8.215475   6.680395   9.388411   \n",
       "1        1  12.372634  12.273017  13.172614   7.287424   7.313861   \n",
       "2        2   9.967715  10.607925   9.849556  11.932323   8.882980   \n",
       "3        3   9.418262   9.388396   9.542745   6.874848  10.285188   \n",
       "4        4  10.057956   9.129196  13.594823   5.789648   7.494531   \n",
       "\n",
       "   stack_error  avg_error  min_error  \n",
       "0     6.690356   6.593897   3.961553  \n",
       "1     7.939729  10.483910   7.287424  \n",
       "2     8.674076  10.248100   8.882980  \n",
       "3     6.939145   9.101888   6.874848  \n",
       "4     4.291907   9.213231   5.789648  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MetricResult = getMetics(AccountList)\n",
    "MetricResult.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T15:05:48.922676Z",
     "start_time": "2019-07-29T15:05:48.853238Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame shape\n",
      "rows: 252\n",
      "cols: 8\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>Null</th>\n",
       "      <th>Unique_Count</th>\n",
       "      <th>Data_type</th>\n",
       "      <th>Max/Min</th>\n",
       "      <th>Mean</th>\n",
       "      <th>Std</th>\n",
       "      <th>Skewness</th>\n",
       "      <th>Sample_values</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>linear</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>254.28/3.26</td>\n",
       "      <td>12.187474</td>\n",
       "      <td>17.721317</td>\n",
       "      <td>11.353183</td>\n",
       "      <td>[4.723653328963506, 12.372634233085533, 9.9677...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ridge</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>35.9/2.09</td>\n",
       "      <td>9.930269</td>\n",
       "      <td>4.154215</td>\n",
       "      <td>2.045378</td>\n",
       "      <td>[3.961552604153703, 12.273017139545168, 10.607...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lasso</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>33.71/2.68</td>\n",
       "      <td>10.045051</td>\n",
       "      <td>3.938487</td>\n",
       "      <td>1.646532</td>\n",
       "      <td>[8.215475280377449, 13.172613945741842, 9.8495...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xgb</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>17.1/3.86</td>\n",
       "      <td>8.417333</td>\n",
       "      <td>2.246964</td>\n",
       "      <td>0.551185</td>\n",
       "      <td>[6.680394652818449, 7.287423787046589, 11.9323...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lightgbm</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>13.89/4.06</td>\n",
       "      <td>8.485432</td>\n",
       "      <td>1.962333</td>\n",
       "      <td>0.302005</td>\n",
       "      <td>[9.388411416733115, 7.3138612013420135, 8.8829...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stack_error</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>9.99/2.79</td>\n",
       "      <td>7.460815</td>\n",
       "      <td>1.768602</td>\n",
       "      <td>-0.504680</td>\n",
       "      <td>[6.6903560992534565, 7.939729380306641, 8.6740...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avg_error</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>63.78/4.23</td>\n",
       "      <td>9.813111</td>\n",
       "      <td>4.390089</td>\n",
       "      <td>8.246720</td>\n",
       "      <td>[6.593897456609244, 10.48391006135223, 10.2480...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min_error</th>\n",
       "      <td>0</td>\n",
       "      <td>252</td>\n",
       "      <td>float64</td>\n",
       "      <td>12.44/2.09</td>\n",
       "      <td>6.821710</td>\n",
       "      <td>1.799177</td>\n",
       "      <td>0.207878</td>\n",
       "      <td>[3.961552604153703, 7.287423787046589, 8.88297...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Null  Unique_Count Data_type      Max/Min       Mean        Std  \\\n",
       "linear          0           252   float64  254.28/3.26  12.187474  17.721317   \n",
       "ridge           0           252   float64    35.9/2.09   9.930269   4.154215   \n",
       "lasso           0           252   float64   33.71/2.68  10.045051   3.938487   \n",
       "xgb             0           252   float64    17.1/3.86   8.417333   2.246964   \n",
       "lightgbm        0           252   float64   13.89/4.06   8.485432   1.962333   \n",
       "stack_error     0           252   float64    9.99/2.79   7.460815   1.768602   \n",
       "avg_error       0           252   float64   63.78/4.23   9.813111   4.390089   \n",
       "min_error       0           252   float64   12.44/2.09   6.821710   1.799177   \n",
       "\n",
       "              Skewness                                      Sample_values  \n",
       "linear       11.353183  [4.723653328963506, 12.372634233085533, 9.9677...  \n",
       "ridge         2.045378  [3.961552604153703, 12.273017139545168, 10.607...  \n",
       "lasso         1.646532  [8.215475280377449, 13.172613945741842, 9.8495...  \n",
       "xgb           0.551185  [6.680394652818449, 7.287423787046589, 11.9323...  \n",
       "lightgbm      0.302005  [9.388411416733115, 7.3138612013420135, 8.8829...  \n",
       "stack_error  -0.504680  [6.6903560992534565, 7.939729380306641, 8.6740...  \n",
       "avg_error     8.246720  [6.593897456609244, 10.48391006135223, 10.2480...  \n",
       "min_error     0.207878  [3.961552604153703, 7.287423787046589, 8.88297...  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_summary(MetricResult)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T15:08:28.022433Z",
     "start_time": "2019-07-29T15:08:27.988176Z"
    },
    "code_folding": []
   },
   "outputs": [],
   "source": [
    "# Plot for a specifc account with prediction, actual amount, and historical amount\n",
    "def plotFor5(Account):\n",
    "    df_model = df[df['Account'] == Account]\n",
    "    df_timeseries = pd.DataFrame(df_model.groupby(['Date'])['NetAmount'].sum()).sort_index(axis=0)\n",
    "    data = pd.DataFrame(df_timeseries.NetAmount)\n",
    "    data.columns = [\"y\"]\n",
    " \n",
    "    # Drop data after 2019-06\n",
    "    data = data.loc[data.index[:-1]] \n",
    "\n",
    "    # Adding the lag of the target variable from 7 steps back up to 48 months ago\n",
    "    for i in range(7, 48):\n",
    "        data[\"lag_{}\".format(i)] = data.y.shift(i)\n",
    "\n",
    "    y = data.dropna().y\n",
    "    X = data.dropna().drop(['y'], axis=1)\n",
    "    \n",
    "    # Reserve 30% of data for testing\n",
    "    X_train, X_test, y_train, y_test = timeseries_train_test_split(X, y, test_size=0.3)\n",
    "    # Scaling\n",
    "    X_train_scaled = scaler.fit_transform(X_train)\n",
    "    X_test_scaled = scaler.transform(X_test)\n",
    "\n",
    "    # Linear Regression \n",
    "    lr = LinearRegression()\n",
    "    lr.fit(X_train, y_train)\n",
    "    prediction1 = lr.predict(X_test)\n",
    "    error_linear = mean_absolute_percentage_error(prediction1, y_test)\n",
    "\n",
    "    # Ridge\n",
    "    ridge = RidgeCV(cv=tscv)\n",
    "    ridge.fit(X_train_scaled, y_train)\n",
    "    prediction2 = ridge.predict(X_test_scaled)\n",
    "    error_ridge = mean_absolute_percentage_error(prediction2, y_test)\n",
    "\n",
    "    # Lasso\n",
    "    lasso = LassoCV(cv=tscv)\n",
    "    lasso.fit(X_train_scaled, y_train)\n",
    "    prediction3 = lasso.predict(X_test_scaled)\n",
    "    error_lasso = mean_absolute_percentage_error(prediction3, y_test)\n",
    "\n",
    "    # XGB\n",
    "    xgb = XGBRegressor(base_score=0.5, booster='gbtree', colsample_bylevel=1,\n",
    "         colsample_bynode=1, colsample_bytree=0.3, gamma=0,\n",
    "         importance_type='gain', learning_rate=0.1, max_delta_step=0,\n",
    "         max_depth=4, min_child_weight=1, missing=None, n_estimators=100,\n",
    "         n_jobs=1, nthread=None, objective='reg:linear', random_state=0,\n",
    "         reg_alpha=0, reg_lambda=1, scale_pos_weight=1, seed=None,\n",
    "         silent=None, subsample=0.5, verbosity=1)          \n",
    "    xgb.fit(X_train_scaled, y_train)\n",
    "    prediction4 = xgb.predict(X_test_scaled)\n",
    "    error_xgb = mean_absolute_percentage_error(prediction4, y_test)\n",
    "\n",
    "    # LightGBM\n",
    "    lgb_train = lgb.Dataset(X_train_scaled, y_train)\n",
    "    lgb_eval = lgb.Dataset(X_test_scaled, y_test, reference=lgb_train)\n",
    "    lightgbm_params = {'boosting_type': 'gbdt', \n",
    "               'colsample_bytree': 0.90, \n",
    "               'learning_rate': 0.005, \n",
    "               'n_estimators': 40, \n",
    "               'num_leaves': 6, \n",
    "               'reg_alpha': 1, \n",
    "               'reg_lambda': 1, \n",
    "               'subsample': 0.7}\n",
    "    gbm = lgb.train(lightgbm_params,lgb_train,num_boost_round=10,valid_sets=lgb_eval)\n",
    "    prediction5 = gbm.predict(X_test_scaled)\n",
    "    error_lightgbm = mean_absolute_percentage_error(prediction5, y_test)\n",
    "    \n",
    "    # Prediction\n",
    "    Prediction = prediction1*0.1+prediction2*0.075+prediction3*0.075+prediction4*0.35+prediction5*0.4\n",
    "    \n",
    "    # Plot\n",
    "    max1 = max(max(Prediction), max(y_test))\n",
    "    max2 = max(max1,max(y_train))\n",
    "    layout = go.Layout(xaxis=dict(autorange=True),yaxis=dict(range = [0, max2*1.2]))\n",
    "    fig = go.Figure(layout= layout)\n",
    "    fig.add_trace(go.Scatter(y=y_test, x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='Actual', line = dict(width=4)))\n",
    "    fig.add_trace(go.Scatter(y=prediction1, x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='Linear', line = dict(width=2, dash='dash')))\n",
    "    fig.add_trace(go.Scatter(y=prediction2,x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='Ridge', line = dict(width=2, dash='dash')))\n",
    "    fig.add_trace(go.Scatter(y=prediction3,x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='Lasso', line = dict(width=2, dash='dash')))\n",
    "    fig.add_trace(go.Scatter(y=prediction4,x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='XGB', line = dict(width=2, dash='dash')))\n",
    "    fig.add_trace(go.Scatter(y=prediction5,x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='Lightgbm', line = dict(width=2, dash='dash')))\n",
    "    fig.add_trace(go.Scatter(y=Prediction,x= list(range(13,19)),\n",
    "                mode='lines',\n",
    "                name='Prediction', line = dict(width=4)))\n",
    "    y_trainPlot = list(y_train)\n",
    "    y_trainPlot.append(y_test[0])\n",
    "    fig.add_trace(go.Scatter(y=y_trainPlot, x= list(range(0,14)),\n",
    "                mode='lines',\n",
    "                name='History', line = dict(width=2,dash='dash')))\n",
    "    y_fake = [y_train[-1],Prediction[0]]\n",
    "    fig.add_trace(go.Scatter(y=y_fake, x= list(range(12,14)),\n",
    "                mode='lines',\n",
    "                name='Fake', line = dict(width=2,dash='dash')))\n",
    "    fig.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T15:09:08.238693Z",
     "start_time": "2019-07-29T15:09:07.860518Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[11:09:08] WARNING: C:/Jenkins/workspace/xgboost-win64_release_0.90/src/objective/regression_obj.cu:152: reg:linear is now deprecated in favor of reg:squarederror.\n"
     ]
    },
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "config": {
        "plotlyServerURL": "https://plot.ly"
       },
       "data": [
        {
         "line": {
          "width": 4
         },
         "mode": "lines",
         "name": "Actual",
         "type": "scatter",
         "uid": "658507b1-29d6-478f-a429-4974d96f1330",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          991675.82,
          971865.4899999999,
          1006651.1799999999,
          1124498.2000000002,
          1107159.8900000006,
          1082036.6900000004
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "Linear",
         "type": "scatter",
         "uid": "869bb8e5-98c6-4093-b972-28708894634d",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          1018076.2361809139,
          1035660.9418166897,
          1094848.4122308583,
          1222716.7869005967,
          1168291.0587582926,
          1185702.9070094645
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "Ridge",
         "type": "scatter",
         "uid": "310aba2b-a7e6-4c63-8c04-c1a30886cb37",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          1030119.7490978531,
          1056082.6758943894,
          1119200.4237518234,
          1251246.5478427985,
          1140003.3769831755,
          1144214.5476274847
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "Lasso",
         "type": "scatter",
         "uid": "7fe02884-faee-4da0-8394-a653507178fc",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          1026229.5707143849,
          1030808.0213892235,
          1075790.678474277,
          1191070.5991429298,
          1113793.8148244098,
          1028923.311710466
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "XGB",
         "type": "scatter",
         "uid": "49b35bcc-190e-41ad-a03e-3fc24fc2f01d",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          876399.875,
          938590.3125,
          970772.1875,
          1027455.4375,
          1024448.375,
          1124309.125
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "Lightgbm",
         "type": "scatter",
         "uid": "bb6dba83-63dc-4ffc-b2f3-79d5b1c1bae2",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          1010856.4759615385,
          1010856.4759615385,
          1010856.4759615385,
          1010856.4759615385,
          1010856.4759615385,
          1010856.4759615385
         ]
        },
        {
         "line": {
          "width": 4
         },
         "mode": "lines",
         "name": "Prediction",
         "type": "scatter",
         "uid": "745a300d-485c-4150-94c6-9f997c19338c",
         "x": [
          13,
          14,
          15,
          16,
          17,
          18
         ],
         "y": [
          967116.3504886245,
          992932.0806125554,
          1018222.0142746586,
          1069397.4613486046,
          1048763.4231460136,
          1079406.4080359081
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "History",
         "type": "scatter",
         "uid": "7fcb0ca7-c64e-498f-b837-1136ee88bb67",
         "x": [
          0,
          1,
          2,
          3,
          4,
          5,
          6,
          7,
          8,
          9,
          10,
          11,
          12,
          13
         ],
         "y": [
          753665.8399999994,
          893796.3000000002,
          859952.2500000002,
          943043.93,
          1144328.5299999993,
          1124690.150000001,
          1141768.0299999998,
          1139743.1100000003,
          1041490.49,
          985467.2700000004,
          1201754.4100000008,
          913649.1500000004,
          997784.8700000002,
          991675.82
         ]
        },
        {
         "line": {
          "dash": "dash",
          "width": 2
         },
         "mode": "lines",
         "name": "Fake",
         "type": "scatter",
         "uid": "b918fcd6-71b3-4884-a7d1-b16933308f01",
         "x": [
          12,
          13
         ],
         "y": [
          997784.8700000002,
          967116.3504886245
         ]
        }
       ],
       "layout": {
        "xaxis": {
         "autorange": true
        },
        "yaxis": {
         "range": [
          0,
          1442105.292000001
         ]
        }
       }
      },
      "text/html": [
       "<div>\n",
       "        \n",
       "        \n",
       "            <div id=\"27fb52c7-1c78-45ba-889a-b736e625e2c1\" class=\"plotly-graph-div\" style=\"height:525px; width:100%;\"></div>\n",
       "            <script type=\"text/javascript\">\n",
       "                require([\"plotly\"], function(Plotly) {\n",
       "                    window.PLOTLYENV=window.PLOTLYENV || {};\n",
       "                    window.PLOTLYENV.BASE_URL='https://plot.ly';\n",
       "                    \n",
       "                if (document.getElementById(\"27fb52c7-1c78-45ba-889a-b736e625e2c1\")) {\n",
       "                    Plotly.newPlot(\n",
       "                        '27fb52c7-1c78-45ba-889a-b736e625e2c1',\n",
       "                        [{\"line\": {\"width\": 4}, \"mode\": \"lines\", \"name\": \"Actual\", \"type\": \"scatter\", \"uid\": \"658507b1-29d6-478f-a429-4974d96f1330\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [991675.82, 971865.4899999999, 1006651.1799999999, 1124498.2000000002, 1107159.8900000006, 1082036.6900000004]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"Linear\", \"type\": \"scatter\", \"uid\": \"869bb8e5-98c6-4093-b972-28708894634d\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [1018076.2361809139, 1035660.9418166897, 1094848.4122308583, 1222716.7869005967, 1168291.0587582926, 1185702.9070094645]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"Ridge\", \"type\": \"scatter\", \"uid\": \"310aba2b-a7e6-4c63-8c04-c1a30886cb37\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [1030119.7490978531, 1056082.6758943894, 1119200.4237518234, 1251246.5478427985, 1140003.3769831755, 1144214.5476274847]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"Lasso\", \"type\": \"scatter\", \"uid\": \"7fe02884-faee-4da0-8394-a653507178fc\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [1026229.5707143849, 1030808.0213892235, 1075790.678474277, 1191070.5991429298, 1113793.8148244098, 1028923.311710466]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"XGB\", \"type\": \"scatter\", \"uid\": \"49b35bcc-190e-41ad-a03e-3fc24fc2f01d\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [876399.875, 938590.3125, 970772.1875, 1027455.4375, 1024448.375, 1124309.125]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"Lightgbm\", \"type\": \"scatter\", \"uid\": \"bb6dba83-63dc-4ffc-b2f3-79d5b1c1bae2\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [1010856.4759615385, 1010856.4759615385, 1010856.4759615385, 1010856.4759615385, 1010856.4759615385, 1010856.4759615385]}, {\"line\": {\"width\": 4}, \"mode\": \"lines\", \"name\": \"Prediction\", \"type\": \"scatter\", \"uid\": \"745a300d-485c-4150-94c6-9f997c19338c\", \"x\": [13, 14, 15, 16, 17, 18], \"y\": [967116.3504886245, 992932.0806125554, 1018222.0142746586, 1069397.4613486046, 1048763.4231460136, 1079406.4080359081]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"History\", \"type\": \"scatter\", \"uid\": \"7fcb0ca7-c64e-498f-b837-1136ee88bb67\", \"x\": [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], \"y\": [753665.8399999994, 893796.3000000002, 859952.2500000002, 943043.93, 1144328.5299999993, 1124690.150000001, 1141768.0299999998, 1139743.1100000003, 1041490.49, 985467.2700000004, 1201754.4100000008, 913649.1500000004, 997784.8700000002, 991675.82]}, {\"line\": {\"dash\": \"dash\", \"width\": 2}, \"mode\": \"lines\", \"name\": \"Fake\", \"type\": \"scatter\", \"uid\": \"b918fcd6-71b3-4884-a7d1-b16933308f01\", \"x\": [12, 13], \"y\": [997784.8700000002, 967116.3504886245]}],\n",
       "                        {\"xaxis\": {\"autorange\": true}, \"yaxis\": {\"range\": [0, 1442105.292000001]}},\n",
       "                        {\"plotlyServerURL\": \"https://plot.ly\", \"responsive\": true}\n",
       "                    ).then(function(){\n",
       "                            \n",
       "var gd = document.getElementById('27fb52c7-1c78-45ba-889a-b736e625e2c1');\n",
       "var x = new MutationObserver(function (mutations, observer) {{\n",
       "        var display = window.getComputedStyle(gd).display;\n",
       "        if (!display || display === 'none') {{\n",
       "            console.log([gd, 'removed!']);\n",
       "            Plotly.purge(gd);\n",
       "            observer.disconnect();\n",
       "        }}\n",
       "}});\n",
       "\n",
       "// Listen for the removal of the full notebook cells\n",
       "var notebookContainer = gd.closest('#notebook-container');\n",
       "if (notebookContainer) {{\n",
       "    x.observe(notebookContainer, {childList: true});\n",
       "}}\n",
       "\n",
       "// Listen for the clearing of the current output cell\n",
       "var outputEl = gd.closest('.output');\n",
       "if (outputEl) {{\n",
       "    x.observe(outputEl, {childList: true});\n",
       "}}\n",
       "\n",
       "                        })\n",
       "                };\n",
       "                });\n",
       "            </script>\n",
       "        </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plotFor5(Account = 7434)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T14:11:42.063438Z",
     "start_time": "2019-07-29T14:11:42.038153Z"
    },
    "code_folding": [
     0,
     72,
     81
    ]
   },
   "outputs": [],
   "source": [
    "def predictNet_PRD(Account, Product):\n",
    "    # Data preprocessing\n",
    "    df_model = df[df['Account'] == Account][df['Product'] == Product]\n",
    "    df_timeseries = pd.DataFrame(df_model.groupby(['Date'])['NetAmount'].sum()).sort_index(axis=0)\n",
    "    data = pd.DataFrame(df_timeseries.NetAmount)\n",
    "    data.columns = [\"y\"]\n",
    "    data = data.loc[data.index[:-1]]\n",
    "    X_predict = pd.DataFrame(index = ['2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12'])\n",
    "    data = pd.concat([data,X_predict])\n",
    "    for i in range(7, 48):\n",
    "        data[\"lag_{}\".format(i)] = data.y.shift(i)\n",
    "    data = data.reset_index()\n",
    "    data['index'] = pd.to_datetime(data['index'])\n",
    "    data['index'] = data['index'].apply(lambda x: x.strftime(\"%Y-%m\"))\n",
    "    data_train = data[data['index'] < '2019-07'].set_index('index')\n",
    "    data_test = data[data['index'] >= '2019-07'].set_index('index')\n",
    "    y_train = data_train.dropna().y\n",
    "    X_train = data_train.dropna().drop(['y'], axis=1)    \n",
    "    X_test = data_test.drop(['y'], axis=1)   \n",
    "    X_train_scaled = scaler.fit_transform(X_train)\n",
    "    X_test_scaled = scaler.transform(X_test)\n",
    "    \n",
    "    # Linear Regression \n",
    "    lr = LinearRegression()\n",
    "    lr.fit(X_train, y_train)\n",
    "    prediction1 = lr.predict(X_test)\n",
    "    \n",
    "    # Ridge\n",
    "    ridge = RidgeCV(cv=tscv)\n",
    "    ridge.fit(X_train_scaled, y_train)\n",
    "    prediction2 = ridge.predict(X_test_scaled)\n",
    "    \n",
    "    # Lasso\n",
    "    lasso = LassoCV(cv=tscv)\n",
    "    lasso.fit(X_train_scaled, y_train)\n",
    "    prediction3 = lasso.predict(X_test_scaled)\n",
    "    \n",
    "    # XGB\n",
    "    xgb = XGBRegressor(base_score=0.5, booster='gbtree', colsample_bylevel=1,\n",
    "         colsample_bynode=1, colsample_bytree=0.3, gamma=0,\n",
    "         importance_type='gain', learning_rate=0.1, max_delta_step=0,\n",
    "         max_depth=4, min_child_weight=1, missing=None, n_estimators=100,\n",
    "         n_jobs=1, nthread=None, objective='reg:linear', random_state=0,\n",
    "         reg_alpha=0, reg_lambda=1, scale_pos_weight=1, seed=None,\n",
    "         silent=None, subsample=0.5, verbosity=1)          \n",
    "    xgb.fit(X_train_scaled, y_train)\n",
    "    prediction4 = xgb.predict(X_test_scaled)\n",
    "    \n",
    "    # LightGBM\n",
    "    lgb_train = lgb.Dataset(X_train_scaled, y_train)\n",
    "    # lgb_eval = lgb.Dataset(X_test_scaled, y_test, reference=lgb_train)\n",
    "    lightgbm_params = {'boosting_type': 'gbdt', \n",
    "               'colsample_bytree': 0.90, \n",
    "               'learning_rate': 0.005, \n",
    "               'n_estimators': 40, \n",
    "               'num_leaves': 6, \n",
    "               'reg_alpha': 1, \n",
    "               'reg_lambda': 1, \n",
    "               'subsample': 0.7}\n",
    "    gbm = lgb.train(lightgbm_params,lgb_train,num_boost_round=10)\n",
    "    prediction5 = gbm.predict(X_test_scaled)\n",
    "    \n",
    "    # Stacking\n",
    "    global stack_model\n",
    "    stack_model = prediction1*0.1+prediction2*0.075+prediction3*0.075+prediction4*0.35+prediction5*0.4\n",
    "    global final_df\n",
    "    final_df = pd.DataFrame(index = ['2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12'], data = stack_model, columns = [Account]).T\n",
    "    final_df['Product'] = Product\n",
    "    final_df = final_df.reset_index()\n",
    "    final_df.columns = ['Account', '2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12', 'Product']\n",
    "    return final_df.set_index(['Account','Product'])\n",
    "\n",
    "def getPredict_PRD(AccountList,ProductList):\n",
    "    d1 = predictNet_PRD(AccountList[0],ProductList)\n",
    "    for i in AccountList[1:]:\n",
    "        try:\n",
    "            d1 = d1.append(predictNet_PRD(i,ProductList))\n",
    "        except:\n",
    "            pass\n",
    "    return d1\n",
    "\n",
    "def getPredict_PRD2(AccountList,ProductList):\n",
    "    d1 = getPredict_PRD(AccountList,ProductList[0])\n",
    "    for i in ProductList[1:]:\n",
    "        try:\n",
    "            d1 = d1.append(getPredict_PRD(AccountList,ProductList[i]))\n",
    "        except:\n",
    "            pass\n",
    "    return d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-29T15:18:30.619781Z",
     "start_time": "2019-07-29T15:18:30.598457Z"
    }
   },
   "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></th>\n",
       "      <th>2019-07</th>\n",
       "      <th>2019-08</th>\n",
       "      <th>2019-09</th>\n",
       "      <th>2019-10</th>\n",
       "      <th>2019-11</th>\n",
       "      <th>2019-12</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Account</th>\n",
       "      <th>Product</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>0</th>\n",
       "      <th>20</th>\n",
       "      <td>4730.157627</td>\n",
       "      <td>5170.756282</td>\n",
       "      <td>4788.486828</td>\n",
       "      <td>5424.397809</td>\n",
       "      <td>5407.867831</td>\n",
       "      <td>4179.799306</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>21</th>\n",
       "      <td>432.601077</td>\n",
       "      <td>392.859806</td>\n",
       "      <td>393.054819</td>\n",
       "      <td>410.126293</td>\n",
       "      <td>394.676076</td>\n",
       "      <td>353.446963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>22</th>\n",
       "      <td>1239.510916</td>\n",
       "      <td>1133.931007</td>\n",
       "      <td>1126.204391</td>\n",
       "      <td>1024.522220</td>\n",
       "      <td>1236.197142</td>\n",
       "      <td>986.734004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>23</th>\n",
       "      <td>2249.486534</td>\n",
       "      <td>2414.827824</td>\n",
       "      <td>2006.095529</td>\n",
       "      <td>2162.343460</td>\n",
       "      <td>2401.872874</td>\n",
       "      <td>2390.752056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <th>24</th>\n",
       "      <td>286.999568</td>\n",
       "      <td>198.064670</td>\n",
       "      <td>363.629041</td>\n",
       "      <td>402.840904</td>\n",
       "      <td>266.489535</td>\n",
       "      <td>356.220065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>25</th>\n",
       "      <td>126.657260</td>\n",
       "      <td>168.206306</td>\n",
       "      <td>130.317548</td>\n",
       "      <td>130.408987</td>\n",
       "      <td>130.607050</td>\n",
       "      <td>145.235409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>26</th>\n",
       "      <td>201.285151</td>\n",
       "      <td>168.841911</td>\n",
       "      <td>223.773551</td>\n",
       "      <td>178.620335</td>\n",
       "      <td>156.690365</td>\n",
       "      <td>146.032619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <th>27</th>\n",
       "      <td>486.983016</td>\n",
       "      <td>369.895739</td>\n",
       "      <td>566.698872</td>\n",
       "      <td>434.506630</td>\n",
       "      <td>507.219457</td>\n",
       "      <td>396.856565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <th>28</th>\n",
       "      <td>431.703721</td>\n",
       "      <td>263.057921</td>\n",
       "      <td>421.110714</td>\n",
       "      <td>310.334040</td>\n",
       "      <td>377.895357</td>\n",
       "      <td>334.763816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <th>29</th>\n",
       "      <td>117.532566</td>\n",
       "      <td>130.617063</td>\n",
       "      <td>126.754196</td>\n",
       "      <td>154.606799</td>\n",
       "      <td>123.309189</td>\n",
       "      <td>135.156583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <th>30</th>\n",
       "      <td>397.848888</td>\n",
       "      <td>392.424871</td>\n",
       "      <td>435.032880</td>\n",
       "      <td>350.539690</td>\n",
       "      <td>360.255981</td>\n",
       "      <td>402.379958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <th>31</th>\n",
       "      <td>103.075052</td>\n",
       "      <td>118.575951</td>\n",
       "      <td>119.832302</td>\n",
       "      <td>111.500353</td>\n",
       "      <td>103.649018</td>\n",
       "      <td>133.651608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <th>32</th>\n",
       "      <td>986.386938</td>\n",
       "      <td>556.650757</td>\n",
       "      <td>627.534714</td>\n",
       "      <td>641.537714</td>\n",
       "      <td>805.185145</td>\n",
       "      <td>839.807531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <th>33</th>\n",
       "      <td>180.870805</td>\n",
       "      <td>172.569809</td>\n",
       "      <td>182.873206</td>\n",
       "      <td>162.920787</td>\n",
       "      <td>159.172417</td>\n",
       "      <td>165.471996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <th>34</th>\n",
       "      <td>235.664030</td>\n",
       "      <td>203.523232</td>\n",
       "      <td>222.923112</td>\n",
       "      <td>250.557946</td>\n",
       "      <td>340.341710</td>\n",
       "      <td>240.253740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <th>35</th>\n",
       "      <td>296.036894</td>\n",
       "      <td>229.571981</td>\n",
       "      <td>255.265579</td>\n",
       "      <td>255.191424</td>\n",
       "      <td>222.906963</td>\n",
       "      <td>294.600608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <th>36</th>\n",
       "      <td>135.049540</td>\n",
       "      <td>122.656680</td>\n",
       "      <td>129.646297</td>\n",
       "      <td>124.779070</td>\n",
       "      <td>140.755522</td>\n",
       "      <td>136.143778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <th>37</th>\n",
       "      <td>251.066478</td>\n",
       "      <td>245.229842</td>\n",
       "      <td>233.079940</td>\n",
       "      <td>253.710194</td>\n",
       "      <td>205.864810</td>\n",
       "      <td>282.352187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <th>38</th>\n",
       "      <td>296.381671</td>\n",
       "      <td>314.953060</td>\n",
       "      <td>241.478613</td>\n",
       "      <td>278.912288</td>\n",
       "      <td>218.497304</td>\n",
       "      <td>311.334954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <th>39</th>\n",
       "      <td>146.723991</td>\n",
       "      <td>172.552385</td>\n",
       "      <td>168.023396</td>\n",
       "      <td>138.900158</td>\n",
       "      <td>181.836510</td>\n",
       "      <td>142.611271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     2019-07      2019-08      2019-09      2019-10  \\\n",
       "Account Product                                                       \n",
       "0       20       4730.157627  5170.756282  4788.486828  5424.397809   \n",
       "1       21        432.601077   392.859806   393.054819   410.126293   \n",
       "2       22       1239.510916  1133.931007  1126.204391  1024.522220   \n",
       "3       23       2249.486534  2414.827824  2006.095529  2162.343460   \n",
       "4       24        286.999568   198.064670   363.629041   402.840904   \n",
       "5       25        126.657260   168.206306   130.317548   130.408987   \n",
       "6       26        201.285151   168.841911   223.773551   178.620335   \n",
       "7       27        486.983016   369.895739   566.698872   434.506630   \n",
       "8       28        431.703721   263.057921   421.110714   310.334040   \n",
       "9       29        117.532566   130.617063   126.754196   154.606799   \n",
       "10      30        397.848888   392.424871   435.032880   350.539690   \n",
       "11      31        103.075052   118.575951   119.832302   111.500353   \n",
       "12      32        986.386938   556.650757   627.534714   641.537714   \n",
       "13      33        180.870805   172.569809   182.873206   162.920787   \n",
       "14      34        235.664030   203.523232   222.923112   250.557946   \n",
       "15      35        296.036894   229.571981   255.265579   255.191424   \n",
       "16      36        135.049540   122.656680   129.646297   124.779070   \n",
       "17      37        251.066478   245.229842   233.079940   253.710194   \n",
       "18      38        296.381671   314.953060   241.478613   278.912288   \n",
       "19      39        146.723991   172.552385   168.023396   138.900158   \n",
       "\n",
       "                     2019-11      2019-12  \n",
       "Account Product                            \n",
       "0       20       5407.867831  4179.799306  \n",
       "1       21        394.676076   353.446963  \n",
       "2       22       1236.197142   986.734004  \n",
       "3       23       2401.872874  2390.752056  \n",
       "4       24        266.489535   356.220065  \n",
       "5       25        130.607050   145.235409  \n",
       "6       26        156.690365   146.032619  \n",
       "7       27        507.219457   396.856565  \n",
       "8       28        377.895357   334.763816  \n",
       "9       29        123.309189   135.156583  \n",
       "10      30        360.255981   402.379958  \n",
       "11      31        103.649018   133.651608  \n",
       "12      32        805.185145   839.807531  \n",
       "13      33        159.172417   165.471996  \n",
       "14      34        340.341710   240.253740  \n",
       "15      35        222.906963   294.600608  \n",
       "16      36        140.755522   136.143778  \n",
       "17      37        205.864810   282.352187  \n",
       "18      38        218.497304   311.334954  \n",
       "19      39        181.836510   142.611271  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Final Prediction\n",
    "predictResultPRD = getPredict_PRD2(AccountList,ProductList)\n",
    "predictResultPRD.head(20)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}