save_sarima_prediction_to_DB.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/ubuntu/anaconda3/lib/python3.6/site-packages/statsmodels/compat/pandas.py:56: FutureWarning: The pandas.core.datetools module is deprecated and will be removed in a future version. Please use the pandas.tseries module instead.\n",
      "  from pandas.core import datetools\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import pickle\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import statsmodels.api as sm\n",
    "from sklearn.preprocessing import StandardScaler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "import psycopg2 as pg\n",
    "import pandas.io.sql as pd_sql\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "import psycopg2 \n",
    "import io\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_sarima_prediction(deptID):\n",
    "    \n",
    "    result = pd.DataFrame()\n",
    "    \n",
    "    storeIDs =[i for i in range(1,46)]\n",
    "    \n",
    "    sales_data_file = 'data/clean_sales_dept_' + str(deptID) + '.pkl'\n",
    "    all_stores_sales_df = pd.read_pickle(sales_data_file)\n",
    "    idx = sorted(all_stores_sales_df['Date'].unique())\n",
    "    \n",
    "    sarima_best_prams_file = 'data/sarima_best_params_dept_' + str(deptID) + '.pkl'\n",
    "    best_params_dict = pickle.load(open(sarima_best_prams_file, \"rb\"))\n",
    "    \n",
    "    for storeID in storeIDs:\n",
    "        print('dept= ',deptID,' store= ',storeID)\n",
    "        \n",
    "        try:\n",
    "            sales_df = all_stores_sales_df[all_stores_sales_df['Store']==storeID]\n",
    "            sales = sales_df['Weekly_Sales']\n",
    "            best_params = best_params_dict[storeID]\n",
    "        \n",
    "            scaler = StandardScaler()\n",
    "            scaler.fit(sales.values.reshape(-1, 1))\n",
    "            X = scaler.transform(sales.values.reshape(-1, 1))\n",
    "\n",
    "            mod = sm.tsa.statespace.SARIMAX(\n",
    "                X,\n",
    "                order = best_params[0],\n",
    "                seasonal_order = best_params[1],\n",
    "                enforce_stationarity=False,\n",
    "                enforce_invertibility=False)\n",
    "            res = mod.fit()\n",
    "\n",
    "            predicted_scaled_sales = res.predict(0)\n",
    "        \n",
    "            predicted_scaled_sales = res.predict(0)\n",
    "            predicted_scaled_sales=pd.Series(predicted_scaled_sales)\n",
    "            predicted_scaled_sales.index=idx\n",
    "            predicted_scaled_sales_df = predicted_scaled_sales.to_frame().reset_index()\n",
    "            predicted_scaled_sales_df.columns=['Date','sarima_predicted_scaled_sales']\n",
    "        \n",
    "            n_rows = predicted_scaled_sales_df.shape[0]\n",
    "            predicted_scaled_sales_df['Store']=[storeID]*n_rows\n",
    "            predicted_scaled_sales_df['Dept']=[deptID]*n_rows\n",
    "        except:\n",
    "            print('failed to get prediction for dept= ',deptID,' store= ',storeID)\n",
    "            continue\n",
    "        if storeID == 1:\n",
    "            result = predicted_scaled_sales_df\n",
    "        else:\n",
    "            result = pd.concat([result,predicted_scaled_sales_df])\n",
    "            \n",
    "    pickle.dump(result, open('data/sarima_prediction_df_dept_' + str(deptID) + '.pkl', \"wb\"))\n",
    "    return "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pickle.load(open('data/sarima_prediction_df_dept_1.pkl', \"rb\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dept=  2  store=  1\n",
      "dept=  2  store=  2\n",
      "dept=  2  store=  3\n",
      "dept=  2  store=  4\n",
      "dept=  2  store=  5\n",
      "dept=  2  store=  6\n",
      "dept=  2  store=  7\n",
      "dept=  2  store=  8\n",
      "dept=  2  store=  9\n",
      "dept=  2  store=  10\n",
      "dept=  2  store=  11\n",
      "dept=  2  store=  12\n",
      "dept=  2  store=  13\n",
      "dept=  2  store=  14\n",
      "dept=  2  store=  15\n",
      "dept=  2  store=  16\n"
     ]
    }
   ],
   "source": [
    "for deptID in [i for i in range(2,82)]:\n",
    "    try:\n",
    "        get_sarima_prediction(deptID)\n",
    "    except:\n",
    "        print('failed to process dept ',deptID)\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "for deptID in [i for i in range(2,82)]:\n",
    "    try:\n",
    "        df2=pickle.load(open('data/sarima_prediction_df_dept_'+str(deptID)+'.pkl', \"rb\"))\n",
    "        df1=pd.concat([df1,df2])\n",
    "    except:\n",
    "        #print('failed to process dept ',deptID)\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>sarima_predicted_scaled_sales</th>\n",
       "      <th>Store</th>\n",
       "      <th>Dept</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-01-04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-01-11</td>\n",
       "      <td>-0.006817</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-01-18</td>\n",
       "      <td>-0.006950</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-01-25</td>\n",
       "      <td>-0.006952</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-02-01</td>\n",
       "      <td>-0.006952</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  sarima_predicted_scaled_sales  Store  Dept\n",
       "0 2010-01-04                       0.000000      1     1\n",
       "1 2010-01-11                      -0.006817      1     1\n",
       "2 2010-01-18                      -0.006950      1     1\n",
       "3 2010-01-25                      -0.006952      1     1\n",
       "4 2010-02-01                      -0.006952      1     1"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_to_string(d):\n",
    "    return d.strftime('%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_year(d):\n",
    "    return d.year\n",
    "def get_month(d):\n",
    "    return d.month\n",
    "def get_day(d):\n",
    "    return d.day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>sarima_predicted_scaled_sales</th>\n",
       "      <th>Store</th>\n",
       "      <th>Dept</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-01-04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010-01-11</td>\n",
       "      <td>-0.006817</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010-01-18</td>\n",
       "      <td>-0.006950</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-01-25</td>\n",
       "      <td>-0.006952</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-02-01</td>\n",
       "      <td>-0.006952</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  sarima_predicted_scaled_sales  Store  Dept  year  month  day\n",
       "0 2010-01-04                       0.000000      1     1  2010      1    4\n",
       "1 2010-01-11                      -0.006817      1     1  2010      1   11\n",
       "2 2010-01-18                      -0.006950      1     1  2010      1   18\n",
       "3 2010-01-25                      -0.006952      1     1  2010      1   25\n",
       "4 2010-02-01                      -0.006952      1     1  2010      2    1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df1.copy(deep=True)\n",
    "df['year']=df['Date'].apply(get_year)\n",
    "df['month']=df['Date'].apply(get_month)\n",
    "df['day']=df['Date'].apply(get_day)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>sarima_predicted_scaled_sales</th>\n",
       "      <th>Store</th>\n",
       "      <th>Dept</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.006817</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.006950</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.006952</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.006952</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sarima_predicted_scaled_sales  Store  Dept  year  month  day\n",
       "0                       0.000000      1     1  2010      1    4\n",
       "1                      -0.006817      1     1  2010      1   11\n",
       "2                      -0.006950      1     1  2010      1   18\n",
       "3                      -0.006952      1     1  2010      1   25\n",
       "4                      -0.006952      1     1  2010      2    1"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop(['Date'],axis=1,inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle.dump(df, open('data/sarima_prediction_df_all_dept.pkl', \"wb\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['sarima_predicted_scaled_sales', 'Store', 'Dept', 'year', 'month',\n",
       "       'day'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection_args = {\n",
    "    'host': '34.220.165.163', # current aws instance public IP\n",
    "    'user': 'ubuntu',    \n",
    "    'dbname': 'ubuntu',   \n",
    "    'port': 5432         \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = pg.connect(**connection_args)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = connection.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(\"\"\"CREATE TABLE sarima_prediction (\n",
    "    sarima_predicted_scaled_sales float, \n",
    "    Dept int,\n",
    "    Store int,\n",
    "    year int,\n",
    "    month int,\n",
    "    day int\n",
    "  \n",
    ");\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = io.StringIO()\n",
    "df.to_csv(output, sep='\\t', header=False, index=False)\n",
    "output.seek(0)\n",
    "contents = output.getvalue()\n",
    "cur.copy_from(output, 'sarima_prediction', null=\"\") #null values become ''   \n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>sarima_predicted_scaled_sales</th>\n",
       "      <th>dept</th>\n",
       "      <th>store</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.347483</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2012</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sarima_predicted_scaled_sales  dept  store  year  month  day\n",
       "0                      -0.347483     1      1  2012     11   19"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_sql_query('select * from sarima_prediction where store=1 and dept=1 and year=2012 and month=11 and day= 19 ',con=connection)\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}