Yard_capacity - daily  (Emp).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yard Capacity Planning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\PSEN\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\statsmodels\\tools\\_testing.py:19: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.\n",
      "  import pandas.util.testing as tm\n"
     ]
    }
   ],
   "source": [
    "import pyodbc\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt \n",
    "import seaborn as sns \n",
    "import statsmodels.formula.api as smf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data acquired from SQL server regarding pieces, cubes, employee time by warehouse and department"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Shipped Date</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Department</th>\n",
       "      <th>Pieces</th>\n",
       "      <th>Cubes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>17</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>15</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>ECR</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Shipped Date Warehouse Department     Pieces       Cubes\n",
       "0   2019-05-08         1       1322  23293.000  436954.193\n",
       "1   2020-01-31        17       4402   8223.000  172705.571\n",
       "2   2021-01-07         5       3619  19268.000  371035.749\n",
       "3   2019-05-16        15       3345  15843.957  324392.477\n",
       "4   2021-02-18       ECR       2642   2195.000   58541.940"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['WHS']=np.where(df['Warehouse']=='1','RKD',\n",
    "                   np.where(df['Warehouse']=='15','LPT',\n",
    "                            np.where(df['Warehouse']=='17','ADV',\n",
    "                                     np.where(df['Warehouse']=='5','RED','ECR'))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Shipped Date</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Department</th>\n",
       "      <th>Pieces</th>\n",
       "      <th>Cubes</th>\n",
       "      <th>WHS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "      <td>RKD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>17</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "      <td>ADV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>5</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "      <td>RED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>15</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "      <td>LPT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>ECR</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "      <td>ECR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Shipped Date Warehouse Department     Pieces       Cubes  WHS\n",
       "0   2019-05-08         1       1322  23293.000  436954.193  RKD\n",
       "1   2020-01-31        17       4402   8223.000  172705.571  ADV\n",
       "2   2021-01-07         5       3619  19268.000  371035.749  RED\n",
       "3   2019-05-16        15       3345  15843.957  324392.477  LPT\n",
       "4   2021-02-18       ECR       2642   2195.000   58541.940  ECR"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Shipped Date     object\n",
       "Warehouse        object\n",
       "Department       object\n",
       "Pieces          float64\n",
       "Cubes           float64\n",
       "WHS              object\n",
       "dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date          datetime64[ns]\n",
       "Department            object\n",
       "total Unit           float64\n",
       "total Cube           float64\n",
       "Warehouse             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop(['Warehouse'],axis=1,inplace=True)\n",
    "df.rename(columns={'Shipped Date':'Date','Pieces':'total Unit','Cubes':'total Cube','WHS':'Warehouse'},inplace='True')\n",
    "df['Date']=pd.to_datetime(df['Date'])\n",
    "#df['Hour']=df['Hour'].astype('str')\n",
    "\n",
    "df.dtypes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Date</th>\n",
       "      <th>home_wh_id</th>\n",
       "      <th>home_department</th>\n",
       "      <th>Number of Employee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>15</td>\n",
       "      <td>3345</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>5</td>\n",
       "      <td>3619</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>17</td>\n",
       "      <td>4402</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>ECR</td>\n",
       "      <td>2642</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>1</td>\n",
       "      <td>1322</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date home_wh_id home_department  Number of Employee\n",
       "0  2021-06-27         15            3345                  52\n",
       "1  2021-06-27          5            3619                  76\n",
       "2  2021-06-27         17            4402                  53\n",
       "3  2021-06-27        ECR            2642                 108\n",
       "4  2021-06-27          1            1322                  55"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "time.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "time['WHS']=np.where(time['home_wh_id']=='1','RKD',\n",
    "                    np.where(time['home_wh_id']=='15','LPT',\n",
    "                            np.where(time['home_wh_id']=='17','ADV',\n",
    "                                    np.where(time['home_wh_id']=='5','RED','ECR'\n",
    "                                    ))))\n",
    "                                            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "time['Department']=np.where(time['home_wh_id']=='1','1322',\n",
    "                    np.where(time['home_wh_id']=='15','3345',\n",
    "                            np.where(time['home_wh_id']=='17','4402',\n",
    "                                    np.where(time['home_wh_id']=='5','3619','2642'\n",
    "                                    ))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                  object\n",
       "home_wh_id            object\n",
       "home_department       object\n",
       "Number of Employee     int64\n",
       "Warehouse             object\n",
       "Department            object\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#time.drop(['WarehouseID'],axis=1,inplace=True)\n",
    "time.rename(columns={'WHS':'Warehouse'},inplace='True')\n",
    "\n",
    "time.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                  datetime64[ns]\n",
       "Number of Employee             int64\n",
       "Warehouse                     object\n",
       "Department                    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#time.Hour=time.Hour.astype('str')\n",
    "\n",
    "#time['Hour']=time[\"Hour\"].str.rjust(2,'0')\n",
    "time.drop(['home_wh_id','home_department'],axis=1,inplace=True)\n",
    "time['Date']=pd.to_datetime(time['Date'])\n",
    "time.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Department</th>\n",
       "      <th>Number of Employee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>LPT</td>\n",
       "      <td>3345</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>RED</td>\n",
       "      <td>3619</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>ADV</td>\n",
       "      <td>4402</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>ECR</td>\n",
       "      <td>2642</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-06-27</td>\n",
       "      <td>RKD</td>\n",
       "      <td>1322</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Warehouse Department  Number of Employee\n",
       "0 2021-06-27       LPT       3345                  52\n",
       "1 2021-06-27       RED       3619                  76\n",
       "2 2021-06-27       ADV       4402                  53\n",
       "3 2021-06-27       ECR       2642                 108\n",
       "4 2021-06-27       RKD       1322                  55"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "time=time[['Date','Warehouse','Department','Number of Employee']]\n",
    "time.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Warehouse</th>\n",
       "      <th>Department</th>\n",
       "      <th>Size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RKD</td>\n",
       "      <td>1322</td>\n",
       "      <td>74234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LPT</td>\n",
       "      <td>3345</td>\n",
       "      <td>76369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADV</td>\n",
       "      <td>4402</td>\n",
       "      <td>76467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RED</td>\n",
       "      <td>3619</td>\n",
       "      <td>94521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ECR</td>\n",
       "      <td>2642</td>\n",
       "      <td>70857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MSQ</td>\n",
       "      <td>4005</td>\n",
       "      <td>54600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Warehouse  Department   Size\n",
       "0       RKD        1322  74234\n",
       "1       LPT        3345  76369\n",
       "2       ADV        4402  76467\n",
       "3       RED        3619  94521\n",
       "4       ECR        2642  70857\n",
       "5       MSQ        4005  54600"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "size=pd.read_excel('square_footage.xlsx')\n",
    "size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=pd.merge(df,time,on=['Date','Department','Warehouse'], how= \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Department</th>\n",
       "      <th>Size</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>RKD</td>\n",
       "      <td>1322</td>\n",
       "      <td>74234</td>\n",
       "      <td>RKD1322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LPT</td>\n",
       "      <td>3345</td>\n",
       "      <td>76369</td>\n",
       "      <td>LPT3345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADV</td>\n",
       "      <td>4402</td>\n",
       "      <td>76467</td>\n",
       "      <td>ADV4402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RED</td>\n",
       "      <td>3619</td>\n",
       "      <td>94521</td>\n",
       "      <td>RED3619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ECR</td>\n",
       "      <td>2642</td>\n",
       "      <td>70857</td>\n",
       "      <td>ECR2642</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Warehouse Department   Size      key\n",
       "0       RKD       1322  74234  RKD1322\n",
       "1       LPT       3345  76369  LPT3345\n",
       "2       ADV       4402  76467  ADV4402\n",
       "3       RED       3619  94521  RED3619\n",
       "4       ECR       2642  70857  ECR2642"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "size['key']=size['Warehouse'].astype('str')+size[\"Department\"].astype('str')\n",
    "size.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Department</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>key</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "      <td>RKD</td>\n",
       "      <td>153</td>\n",
       "      <td>RKD1322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "      <td>ADV</td>\n",
       "      <td>119</td>\n",
       "      <td>ADV4402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "      <td>RED</td>\n",
       "      <td>158</td>\n",
       "      <td>RED3619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "      <td>LPT</td>\n",
       "      <td>154</td>\n",
       "      <td>LPT3345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "      <td>ECR</td>\n",
       "      <td>27</td>\n",
       "      <td>ECR2642</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Department  total Unit  total Cube Warehouse  Number of Employee  \\\n",
       "0 2019-05-08       1322   23293.000  436954.193       RKD                 153   \n",
       "1 2020-01-31       4402    8223.000  172705.571       ADV                 119   \n",
       "2 2021-01-07       3619   19268.000  371035.749       RED                 158   \n",
       "3 2019-05-16       3345   15843.957  324392.477       LPT                 154   \n",
       "4 2021-02-18       2642    2195.000   58541.940       ECR                  27   \n",
       "\n",
       "       key  \n",
       "0  RKD1322  \n",
       "1  ADV4402  \n",
       "2  RED3619  \n",
       "3  LPT3345  \n",
       "4  ECR2642  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['key']=df1['Warehouse']+df1['Department']\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Department</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>key</th>\n",
       "      <th>Size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "      <td>RKD</td>\n",
       "      <td>153</td>\n",
       "      <td>RKD1322</td>\n",
       "      <td>74234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "      <td>ADV</td>\n",
       "      <td>119</td>\n",
       "      <td>ADV4402</td>\n",
       "      <td>76467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "      <td>RED</td>\n",
       "      <td>158</td>\n",
       "      <td>RED3619</td>\n",
       "      <td>94521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "      <td>LPT</td>\n",
       "      <td>154</td>\n",
       "      <td>LPT3345</td>\n",
       "      <td>76369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "      <td>ECR</td>\n",
       "      <td>27</td>\n",
       "      <td>ECR2642</td>\n",
       "      <td>70857</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Department  total Unit  total Cube Warehouse  Number of Employee  \\\n",
       "0 2019-05-08       1322   23293.000  436954.193       RKD                 153   \n",
       "1 2020-01-31       4402    8223.000  172705.571       ADV                 119   \n",
       "2 2021-01-07       3619   19268.000  371035.749       RED                 158   \n",
       "3 2019-05-16       3345   15843.957  324392.477       LPT                 154   \n",
       "4 2021-02-18       2642    2195.000   58541.940       ECR                  27   \n",
       "\n",
       "       key   Size  \n",
       "0  RKD1322  74234  \n",
       "1  ADV4402  76467  \n",
       "2  RED3619  94521  \n",
       "3  LPT3345  76369  \n",
       "4  ECR2642  70857  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final=pd.merge(df1,size[['key','Size']],on=['key'], how='left')\n",
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5458, 8)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date                  0\n",
       "Department            0\n",
       "total Unit            0\n",
       "total Cube            0\n",
       "Warehouse             0\n",
       "Number of Employee    0\n",
       "key                   0\n",
       "Size                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final=df_final.dropna(subset=['Number of Employee'])\n",
    "df_final=df_final.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>Date</th>\n",
       "      <th>Department</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>key</th>\n",
       "      <th>Size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "      <td>RKD</td>\n",
       "      <td>153</td>\n",
       "      <td>RKD1322</td>\n",
       "      <td>74234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "      <td>ADV</td>\n",
       "      <td>119</td>\n",
       "      <td>ADV4402</td>\n",
       "      <td>76467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "      <td>RED</td>\n",
       "      <td>158</td>\n",
       "      <td>RED3619</td>\n",
       "      <td>94521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "      <td>LPT</td>\n",
       "      <td>154</td>\n",
       "      <td>LPT3345</td>\n",
       "      <td>76369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "      <td>ECR</td>\n",
       "      <td>27</td>\n",
       "      <td>ECR2642</td>\n",
       "      <td>70857</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Department  total Unit  total Cube Warehouse  Number of Employee  \\\n",
       "0 2019-05-08       1322   23293.000  436954.193       RKD                 153   \n",
       "1 2020-01-31       4402    8223.000  172705.571       ADV                 119   \n",
       "2 2021-01-07       3619   19268.000  371035.749       RED                 158   \n",
       "3 2019-05-16       3345   15843.957  324392.477       LPT                 154   \n",
       "4 2021-02-18       2642    2195.000   58541.940       ECR                  27   \n",
       "\n",
       "       key   Size  \n",
       "0  RKD1322  74234  \n",
       "1  ADV4402  76467  \n",
       "2  RED3619  94521  \n",
       "3  LPT3345  76369  \n",
       "4  ECR2642  70857  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>Date</th>\n",
       "      <th>Department</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>Size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "      <td>RKD</td>\n",
       "      <td>153</td>\n",
       "      <td>74234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "      <td>ADV</td>\n",
       "      <td>119</td>\n",
       "      <td>76467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "      <td>RED</td>\n",
       "      <td>158</td>\n",
       "      <td>94521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "      <td>LPT</td>\n",
       "      <td>154</td>\n",
       "      <td>76369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "      <td>ECR</td>\n",
       "      <td>27</td>\n",
       "      <td>70857</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Department  total Unit  total Cube Warehouse  Number of Employee  \\\n",
       "0 2019-05-08       1322   23293.000  436954.193       RKD                 153   \n",
       "1 2020-01-31       4402    8223.000  172705.571       ADV                 119   \n",
       "2 2021-01-07       3619   19268.000  371035.749       RED                 158   \n",
       "3 2019-05-16       3345   15843.957  324392.477       LPT                 154   \n",
       "4 2021-02-18       2642    2195.000   58541.940       ECR                  27   \n",
       "\n",
       "    Size  \n",
       "0  74234  \n",
       "1  76467  \n",
       "2  94521  \n",
       "3  76369  \n",
       "4  70857  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.drop(['key'],axis=1,inplace=True)\n",
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\PSEN\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: FutureWarning: Series.dt.weekofyear and Series.dt.week have been deprecated.  Please use Series.dt.isocalendar().week instead.\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "df_final['Year']=df_final['Date'].dt.year\n",
    "df_final['Month']=df_final['Date'].dt.month\n",
    "df_final['Week']=df_final['Date'].dt.week\n",
    "df_final['Day']=df_final['Date'].dt.day\n",
    "df_final['Week Day']=df_final['Date'].dt.dayofweek\n",
    "df_final['Year Day']=df_final['Date'].dt.dayofyear\n",
    "#df_final['Quarter']=df_final['Date'].dt.quarter\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Date</th>\n",
       "      <th>Department</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>Size</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Week</th>\n",
       "      <th>Day</th>\n",
       "      <th>Week Day</th>\n",
       "      <th>Year Day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-05-08</td>\n",
       "      <td>1322</td>\n",
       "      <td>23293.000</td>\n",
       "      <td>436954.193</td>\n",
       "      <td>RKD</td>\n",
       "      <td>153</td>\n",
       "      <td>74234</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "      <td>19</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>4402</td>\n",
       "      <td>8223.000</td>\n",
       "      <td>172705.571</td>\n",
       "      <td>ADV</td>\n",
       "      <td>119</td>\n",
       "      <td>76467</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>3619</td>\n",
       "      <td>19268.000</td>\n",
       "      <td>371035.749</td>\n",
       "      <td>RED</td>\n",
       "      <td>158</td>\n",
       "      <td>94521</td>\n",
       "      <td>2021</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>3345</td>\n",
       "      <td>15843.957</td>\n",
       "      <td>324392.477</td>\n",
       "      <td>LPT</td>\n",
       "      <td>154</td>\n",
       "      <td>76369</td>\n",
       "      <td>2019</td>\n",
       "      <td>5</td>\n",
       "      <td>20</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-18</td>\n",
       "      <td>2642</td>\n",
       "      <td>2195.000</td>\n",
       "      <td>58541.940</td>\n",
       "      <td>ECR</td>\n",
       "      <td>27</td>\n",
       "      <td>70857</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date Department  total Unit  total Cube Warehouse  Number of Employee  \\\n",
       "0 2019-05-08       1322   23293.000  436954.193       RKD                 153   \n",
       "1 2020-01-31       4402    8223.000  172705.571       ADV                 119   \n",
       "2 2021-01-07       3619   19268.000  371035.749       RED                 158   \n",
       "3 2019-05-16       3345   15843.957  324392.477       LPT                 154   \n",
       "4 2021-02-18       2642    2195.000   58541.940       ECR                  27   \n",
       "\n",
       "    Size  Year  Month  Week  Day  Week Day  Year Day  \n",
       "0  74234  2019      5    19    8         2       128  \n",
       "1  76467  2020      1     5   31         4        31  \n",
       "2  94521  2021      1     1    7         3         7  \n",
       "3  76369  2019      5    20   16         3       136  \n",
       "4  70857  2021      2     7   18         3        49  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.sort_values(by='Date', ascending= True, inplace= True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.drop(['Date','Department'],axis=1,inplace=True)\n",
    "df_final=df_final[['Year','Month','Day','Year Day','Week Day','Warehouse','total Unit','total Cube','Size','Number of Employee']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year Day</th>\n",
       "      <th>Week Day</th>\n",
       "      <th>Warehouse</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Size</th>\n",
       "      <th>Number of Employee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>ECR</td>\n",
       "      <td>12661.000</td>\n",
       "      <td>285028.367</td>\n",
       "      <td>70857</td>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2722</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>RED</td>\n",
       "      <td>11666.000</td>\n",
       "      <td>223682.705</td>\n",
       "      <td>94521</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1462</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>LPT</td>\n",
       "      <td>12636.976</td>\n",
       "      <td>263363.623</td>\n",
       "      <td>76369</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>RKD</td>\n",
       "      <td>16961.002</td>\n",
       "      <td>329403.893</td>\n",
       "      <td>74234</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2362</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>ADV</td>\n",
       "      <td>9025.001</td>\n",
       "      <td>158894.347</td>\n",
       "      <td>76467</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year  Month  Day  Year Day  Week Day Warehouse  total Unit  total Cube  \\\n",
       "1101  2018      6   28       179         3       ECR   12661.000  285028.367   \n",
       "2722  2018      6   28       179         3       RED   11666.000  223682.705   \n",
       "1462  2018      6   28       179         3       LPT   12636.976  263363.623   \n",
       "2182  2018      6   28       179         3       RKD   16961.002  329403.893   \n",
       "2362  2018      6   28       179         3       ADV    9025.001  158894.347   \n",
       "\n",
       "       Size  Number of Employee  \n",
       "1101  70857                 114  \n",
       "2722  94521                 139  \n",
       "1462  76369                 140  \n",
       "2182  74234                 141  \n",
       "2362  76467                  80  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year Day</th>\n",
       "      <th>Week Day</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Size</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>ADV</th>\n",
       "      <th>ECR</th>\n",
       "      <th>LPT</th>\n",
       "      <th>RED</th>\n",
       "      <th>RKD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>12661.000</td>\n",
       "      <td>285028.367</td>\n",
       "      <td>70857</td>\n",
       "      <td>114</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2722</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>11666.000</td>\n",
       "      <td>223682.705</td>\n",
       "      <td>94521</td>\n",
       "      <td>139</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1462</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>12636.976</td>\n",
       "      <td>263363.623</td>\n",
       "      <td>76369</td>\n",
       "      <td>140</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>16961.002</td>\n",
       "      <td>329403.893</td>\n",
       "      <td>74234</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2362</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>9025.001</td>\n",
       "      <td>158894.347</td>\n",
       "      <td>76467</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year  Month  Day  Year Day  Week Day  total Unit  total Cube   Size  \\\n",
       "1101  2018      6   28       179         3   12661.000  285028.367  70857   \n",
       "2722  2018      6   28       179         3   11666.000  223682.705  94521   \n",
       "1462  2018      6   28       179         3   12636.976  263363.623  76369   \n",
       "2182  2018      6   28       179         3   16961.002  329403.893  74234   \n",
       "2362  2018      6   28       179         3    9025.001  158894.347  76467   \n",
       "\n",
       "      Number of Employee  ADV  ECR  LPT  RED  RKD  \n",
       "1101                 114    0    1    0    0    0  \n",
       "2722                 139    0    0    0    1    0  \n",
       "1462                 140    0    0    1    0    0  \n",
       "2182                 141    0    0    0    0    1  \n",
       "2362                  80    1    0    0    0    0  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df_final=pd.concat([df_final, pd.get_dummies(df_final['Hour'])], axis=1)\n",
    "#df_final.drop(['Hour'],axis=1,inplace=True)\n",
    "df_final=pd.concat([df_final, pd.get_dummies(df_final['Warehouse'])], axis=1)\n",
    "df_final.drop(['Warehouse'],axis=1,inplace=True)\n",
    "#df_final=pd.concat([df_final, pd.get_dummies(df_final['Department'])], axis=1)\n",
    "#df_final.drop(['Department'],axis=1,inplace=True)\n",
    "df_final.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year Day</th>\n",
       "      <th>Week Day</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Size</th>\n",
       "      <th>Number of Employee</th>\n",
       "      <th>ADV</th>\n",
       "      <th>ECR</th>\n",
       "      <th>LPT</th>\n",
       "      <th>RED</th>\n",
       "      <th>RKD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>12661.000</td>\n",
       "      <td>285028.367</td>\n",
       "      <td>70857</td>\n",
       "      <td>114</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2722</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>11666.000</td>\n",
       "      <td>223682.705</td>\n",
       "      <td>94521</td>\n",
       "      <td>139</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1462</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>12636.976</td>\n",
       "      <td>263363.623</td>\n",
       "      <td>76369</td>\n",
       "      <td>140</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>16961.002</td>\n",
       "      <td>329403.893</td>\n",
       "      <td>74234</td>\n",
       "      <td>141</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2362</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>9025.001</td>\n",
       "      <td>158894.347</td>\n",
       "      <td>76467</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year  Month  Day  Year Day  Week Day  total Unit  total Cube   Size  \\\n",
       "1101  2018      6   28       179         3   12661.000  285028.367  70857   \n",
       "2722  2018      6   28       179         3   11666.000  223682.705  94521   \n",
       "1462  2018      6   28       179         3   12636.976  263363.623  76369   \n",
       "2182  2018      6   28       179         3   16961.002  329403.893  74234   \n",
       "2362  2018      6   28       179         3    9025.001  158894.347  76467   \n",
       "\n",
       "      Number of Employee  ADV  ECR  LPT  RED  RKD  \n",
       "1101                 114    0    1    0    0    0  \n",
       "2722                 139    0    0    0    1    0  \n",
       "1462                 140    0    0    1    0    0  \n",
       "2182                 141    0    0    0    0    1  \n",
       "2362                  80    1    0    0    0    0  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Model Development and evaluation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "y=df_final['Number of Employee']\n",
    "x=df_final.drop(['Number of Employee'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Year Day</th>\n",
       "      <th>Week Day</th>\n",
       "      <th>total Unit</th>\n",
       "      <th>total Cube</th>\n",
       "      <th>Size</th>\n",
       "      <th>ADV</th>\n",
       "      <th>ECR</th>\n",
       "      <th>LPT</th>\n",
       "      <th>RED</th>\n",
       "      <th>RKD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>12661.000</td>\n",
       "      <td>285028.367</td>\n",
       "      <td>70857</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2722</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>11666.000</td>\n",
       "      <td>223682.705</td>\n",
       "      <td>94521</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1462</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>12636.976</td>\n",
       "      <td>263363.623</td>\n",
       "      <td>76369</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2182</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>16961.002</td>\n",
       "      <td>329403.893</td>\n",
       "      <td>74234</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2362</th>\n",
       "      <td>2018</td>\n",
       "      <td>6</td>\n",
       "      <td>28</td>\n",
       "      <td>179</td>\n",
       "      <td>3</td>\n",
       "      <td>9025.001</td>\n",
       "      <td>158894.347</td>\n",
       "      <td>76467</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year  Month  Day  Year Day  Week Day  total Unit  total Cube   Size  \\\n",
       "1101  2018      6   28       179         3   12661.000  285028.367  70857   \n",
       "2722  2018      6   28       179         3   11666.000  223682.705  94521   \n",
       "1462  2018      6   28       179         3   12636.976  263363.623  76369   \n",
       "2182  2018      6   28       179         3   16961.002  329403.893  74234   \n",
       "2362  2018      6   28       179         3    9025.001  158894.347  76467   \n",
       "\n",
       "      ADV  ECR  LPT  RED  RKD  \n",
       "1101    0    1    0    0    0  \n",
       "2722    0    0    0    1    0  \n",
       "1462    0    0    1    0    0  \n",
       "2182    0    0    0    0    1  \n",
       "2362    1    0    0    0    0  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import TimeSeriesSplit\n",
    "tss=TimeSeriesSplit(n_splits=10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "regressor=RandomForestRegressor(n_estimators=300,random_state=0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[13.038210002480053, 11.880171115011494, 11.02275150764278, 9.524023696765006, 14.055829245568665, 144.35452205528773, 34.04259113380298, 39.183174656510936, 20.240318952614526, 11.420715203706989]\n",
      "Min MAPE: 9.524023696765006\n",
      "Average MAPE: 30.876230756939115\n",
      "Max MAPE: 144.35452205528773\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "from sklearn.model_selection import TimeSeriesSplit\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from statistics import mean\n",
    "result=[]\n",
    "\n",
    "tss=TimeSeriesSplit(n_splits=10)\n",
    "regressor=RandomForestRegressor(n_estimators=300,random_state=0)\n",
    "for train_index, test_index in tss.split(x):\n",
    "    x_train, x_test=x.iloc[train_index, :].values, x.iloc[test_index, :].values\n",
    "    y_train, y_test=y.iloc[train_index].values, y.iloc[test_index].values\n",
    "    regressor.fit(x_train,y_train)\n",
    "    prediction=(regressor.predict(x_test))\n",
    "    actual=(y_test)\n",
    "    MAPE=np.mean(np.abs((actual-prediction)/actual))*100\n",
    "    result.append(MAPE)\n",
    "    \n",
    "print(result)\n",
    "print('Min MAPE:',min(result))\n",
    "print('Average MAPE:',mean(result))\n",
    "print('Max MAPE:',max(result))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RandomForestRegressor(n_estimators=300, random_state=0)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "regressor.fit(x,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final_imp=df_final.drop(['Number of Employee'],axis=1)\n",
    "importance=regressor.feature_importances_\n",
    "importance_features=list(zip(importance,df_final_imp.columns))\n",
    "importance_features.sort(reverse=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0.5421044222387973, 'Week Day'),\n",
       " (0.12678441333031, 'total Cube'),\n",
       " (0.10348558896912474, 'total Unit'),\n",
       " (0.07835835502765354, 'Year Day'),\n",
       " (0.04405836936349344, 'Year'),\n",
       " (0.035965992463458885, 'Size'),\n",
       " (0.016861053652066613, 'ECR'),\n",
       " (0.013402126105530744, 'Day'),\n",
       " (0.010264194362855637, 'LPT'),\n",
       " (0.009089017628126217, 'RED'),\n",
       " (0.007021917541636951, 'Month'),\n",
       " (0.006936603497860818, 'ADV'),\n",
       " (0.005667945819085085, 'RKD')]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "importance_features"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}