CourseraProject_part1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "CVs_TE0Z0s-e"
   },
   "source": [
    "# Processing the given time series data to create and store the training, validation and test datasets for supervised model building"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "rVt5w_1IH4TO"
   },
   "source": [
    "This notebook is created as part of the coursera course \"How to win data science competitions\" Final project.\n",
    "\n",
    "References:\n",
    "* https://towardsdatascience.com/using-gradient-boosting-for-time-series-prediction-tasks-600fac66a5fc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xcDMK-0-0s-i"
   },
   "source": [
    "## Lets look at the test dataset on which we have to make the prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "vx-Lbb8K0s-m"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "gYPjroXE01bP",
    "outputId": "2a205ed1-a114-4f87-fa82-2f2538d6c0bd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
     ]
    }
   ],
   "source": [
    "from google.colab import drive\n",
    "drive.mount('/content/drive')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "colab_type": "code",
    "id": "v6knqe8z0s-5",
    "outputId": "6557136f-15db-42cc-b2f7-403dbeaacea3"
   },
   "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>ID</th>\n",
       "      <th>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  shop_id  item_id\n",
       "0   0        5     5037\n",
       "1   1        5     5320\n",
       "2   2        5     5233\n",
       "3   3        5     5232\n",
       "4   4        5     5268"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test = pd.read_csv('/content/drive/My Drive/CourseraProject/data/test.csv')\n",
    "df_test.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "IT-d57mX0s_J"
   },
   "source": [
    "## Q1) What is the shape of the test dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "AvPGh9aF0s_M",
    "outputId": "c4b4d6ca-9e31-4401-aae6-ecb5f42232fe"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(214200, 3)"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "WdJHRTV30s_Y"
   },
   "source": [
    "So there are around 2 Lakh+ entries in the test dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XY_aGlS70s_Z"
   },
   "source": [
    "## Q2) What are the names of the columns?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "WbjQXjKe0s_c",
    "outputId": "e21fdf3c-f711-4d2c-f7e5-25939dfc87a8"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID', 'shop_id', 'item_id'], dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0r-fT-DC0s_o"
   },
   "source": [
    "A ID indicates a particular shop_id and item_id pair combination."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "t5Au1k4L0s_s"
   },
   "source": [
    "### Q3) How many unique shop ids are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "Ih0hsrcn0s_v",
    "outputId": "624809d5-1f90-4c33-8d5c-94872094f19e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "42"
      ]
     },
     "execution_count": 6,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test['shop_id'].value_counts().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "YUU1GPxA0s_8"
   },
   "source": [
    "There are 42 unique shop ids in the test dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "TkI0oL7n0s_-"
   },
   "source": [
    "### Q4) How many unique item ids are there?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "NrWwH0bX0s__",
    "outputId": "ca8c7a79-cd54-43c5-f8a2-f4eb715844ad"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5100"
      ]
     },
     "execution_count": 7,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test['item_id'].value_counts().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XXD1E4qN0tAJ"
   },
   "source": [
    "There are 5100 unique item ids in the test dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "o3Hc4qcl0tAL",
    "outputId": "03532dfa-64c9-459a-9edb-7a3f22803f88"
   },
   "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>ID</th>\n",
       "      <th>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  shop_id  item_id\n",
       "0   0        5     5037\n",
       "1   1        5     5320\n",
       "2   2        5     5233\n",
       "3   3        5     5232\n",
       "4   4        5     5268"
      ]
     },
     "execution_count": 8,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "rYI-vtiT0tAW",
    "outputId": "b04ccda3-93ea-4a29-fc71-ccc9869c82d5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "214200"
      ]
     },
     "execution_count": 9,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How the test set was created\n",
    "from itertools import product\n",
    "test_comb = list(product(*[df_test.shop_id.unique(),df_test.item_id.unique()]))\n",
    "len(test_comb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "R3SZBR_q0tAg"
   },
   "source": [
    "The test set was generated by cartesian product of unique values from shop_id and item_id columns of the test dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "19bYj5b_0tAi",
    "outputId": "e770bdfe-10a9-4932-f6f8-249e886fbf8c"
   },
   "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>date_block_num</th>\n",
       "      <th>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_price</th>\n",
       "      <th>item_cnt_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>02.01.2013</td>\n",
       "      <td>0</td>\n",
       "      <td>59</td>\n",
       "      <td>22154</td>\n",
       "      <td>999.00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>03.01.2013</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>2552</td>\n",
       "      <td>899.00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>05.01.2013</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>2552</td>\n",
       "      <td>899.00</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06.01.2013</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>2554</td>\n",
       "      <td>1709.05</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15.01.2013</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>2555</td>\n",
       "      <td>1099.00</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  date_block_num  shop_id  item_id  item_price  item_cnt_day\n",
       "0  02.01.2013               0       59    22154      999.00           1.0\n",
       "1  03.01.2013               0       25     2552      899.00           1.0\n",
       "2  05.01.2013               0       25     2552      899.00          -1.0\n",
       "3  06.01.2013               0       25     2554     1709.05           1.0\n",
       "4  15.01.2013               0       25     2555     1099.00           1.0"
      ]
     },
     "execution_count": 10,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Lets read the main training dataset\n",
    "df_train = pd.read_csv('/content/drive/My Drive/CourseraProject/data/sales_train.csv')\n",
    "df_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "Auje19pK0tAx",
    "outputId": "f96dddf3-7617-4753-8232-fb3b7e41c981"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2935849, 6)"
      ]
     },
     "execution_count": 11,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "M5CbpLMC0tA9",
    "outputId": "ca50072c-19af-4aee-8672-01d584042558"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34"
      ]
     },
     "execution_count": 12,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.date_block_num.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "Kj_tDn7X0tBK",
    "outputId": "17fffc34-e5e3-4c02-c1bf-9f37fd1a74c6"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "60\n",
      "21807\n"
     ]
    }
   ],
   "source": [
    "# How many unique elements in shop_id and item_id of sales train?\n",
    "print(df_train.shop_id.nunique())\n",
    "print(df_train.item_id.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "undjAzpK0tBR"
   },
   "source": [
    "Since the test data is generated with combination of shops and items, we have to restructure train data to match the test data generation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "P2an9wa40tBV",
    "outputId": "fc686672-257c-47ec-8c9f-ec46fd542fb9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10913850, 3)\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>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date_block_num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59</td>\n",
       "      <td>22154</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59</td>\n",
       "      <td>2552</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59</td>\n",
       "      <td>2554</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59</td>\n",
       "      <td>2555</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shop_id  item_id  date_block_num\n",
       "0       59    22154               0\n",
       "1       59     2552               0\n",
       "2       59     2554               0\n",
       "3       59     2555               0\n",
       "4       59     2564               0"
      ]
     },
     "execution_count": 14,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_cols = ['shop_id', 'item_id', 'date_block_num']\n",
    "grid = []\n",
    "for block_num in df_train['date_block_num'].unique():\n",
    "    cur_shops = df_train.loc[df_train['date_block_num'] == block_num, 'shop_id'].unique()\n",
    "    cur_items = df_train.loc[df_train['date_block_num'] == block_num, 'item_id'].unique()\n",
    "    grid.append(np.array(list(product(*[cur_shops, cur_items, [block_num]])),dtype='int32'))\n",
    "grid = pd.DataFrame(np.vstack(grid), columns = index_cols,dtype=np.int32)\n",
    "print(grid.shape)\n",
    "grid.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "Fhv5gIB00tBg",
    "outputId": "5e48b29f-21e4-46d4-9966-3ce0fe12b495"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.717442552392851\n"
     ]
    }
   ],
   "source": [
    "print(grid.shape[0]/df_train.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "FDjAImaP0tBq"
   },
   "source": [
    "The training data is smaller than all possible combinations calculated using shop_id, item_id columns of the training data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "60VH2i4g0tBr",
    "outputId": "b291e9c8-90d5-405c-ce3d-78695940c212"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID                     0\n",
       "shop_id                0\n",
       "item_id                0\n",
       "date              102796\n",
       "date_block_num    102796\n",
       "item_price        102796\n",
       "item_cnt_day      102796\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.merge(df_train,how='left',on=['shop_id','item_id']).isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "VHs_y_Gn0tB0",
    "outputId": "a52b34ea-a493-4710-9aed-3917bdbec0b3"
   },
   "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>ID</th>\n",
       "      <th>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>item_price</th>\n",
       "      <th>item_cnt_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "      <td>21.09.2014</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2599.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "      <td>29.11.2014</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2599.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "      <td>28.12.2014</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "      <td>20.12.2014</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "      <td>02.01.2015</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  shop_id  item_id        date  date_block_num  item_price  item_cnt_day\n",
       "0   0        5     5037  21.09.2014            20.0      2599.0           1.0\n",
       "1   0        5     5037  29.11.2014            22.0      2599.0           1.0\n",
       "2   0        5     5037  28.12.2014            23.0      1999.0           1.0\n",
       "3   0        5     5037  20.12.2014            23.0      1999.0           1.0\n",
       "4   0        5     5037  02.01.2015            24.0      1999.0           1.0"
      ]
     },
     "execution_count": 17,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.merge(df_train,how='left',on=['shop_id','item_id']).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "yB9i3QoY0tB9"
   },
   "outputs": [],
   "source": [
    "# Removing the outliers from the item_price and item_cnt_day\n",
    "df_train = df_train[df_train.item_price<100000]\n",
    "df_train = df_train[df_train.item_cnt_day<=900]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "colab_type": "code",
    "id": "p8x5dahF0tCD",
    "outputId": "3d98cf2d-3341-458f-981d-56a9fcc34d22"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2935846, 6)"
      ]
     },
     "execution_count": 19,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0Rwa5a0w0tCN"
   },
   "source": [
    "3 rows are dropped from the df_train"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "9xvpaY1h0tCP"
   },
   "source": [
    "# Let us create the final train dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "jzjTYEXM0tCQ"
   },
   "outputs": [],
   "source": [
    "# Test predictions require item_cnt prediction for a month so in training it should also be in monthly format\n",
    "df_train_m = df_train.groupby(['date_block_num','shop_id','item_id']).agg({'item_cnt_day': 'sum','item_price': np.mean}).reset_index()\n",
    "# To mimic the test data generation process..\n",
    "df_train_m = pd.merge(grid,df_train_m,on=['date_block_num','shop_id','item_id'],how='left').fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "colab_type": "code",
    "id": "v9dMfla10tCa",
    "outputId": "65d85bf1-69fb-4fc2-f14b-c006175ff892"
   },
   "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>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>item_cnt_day</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59</td>\n",
       "      <td>22154</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59</td>\n",
       "      <td>2552</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59</td>\n",
       "      <td>2554</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59</td>\n",
       "      <td>2555</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shop_id  item_id  date_block_num  item_cnt_day  item_price\n",
       "0       59    22154               0           1.0       999.0\n",
       "1       59     2552               0           0.0         0.0\n",
       "2       59     2554               0           0.0         0.0\n",
       "3       59     2555               0           0.0         0.0\n",
       "4       59     2564               0           0.0         0.0"
      ]
     },
     "execution_count": 21,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "5uFEMpaT0tCh"
   },
   "outputs": [],
   "source": [
    "# Lets read remaining files\n",
    "df_item_cat = pd.read_csv('/content/drive/My Drive/CourseraProject/data/item_categories.csv')\n",
    "df_item = pd.read_csv('/content/drive/My Drive/CourseraProject/data/items.csv')\n",
    "df_shops = pd.read_csv('/content/drive/My Drive/CourseraProject/data/shops.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "colab_type": "code",
    "id": "_d6YShGm0tCt",
    "outputId": "c9f9c6e2-0f12-48b4-c2b2-f077e052b80b"
   },
   "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>item_category_name</th>\n",
       "      <th>item_category_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PC - Гарнитуры/Наушники</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Аксессуары - PS2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Аксессуары - PS3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Аксессуары - PS4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Аксессуары - PSP</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        item_category_name  item_category_id\n",
       "0  PC - Гарнитуры/Наушники                 0\n",
       "1         Аксессуары - PS2                 1\n",
       "2         Аксессуары - PS3                 2\n",
       "3         Аксессуары - PS4                 3\n",
       "4         Аксессуары - PSP                 4"
      ]
     },
     "execution_count": 23,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_item_cat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "colab_type": "code",
    "id": "0WMJvvqW0tC0",
    "outputId": "27a0e755-6877-4faa-f69a-5b0d1f32df8e"
   },
   "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>item_name</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_category_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>! ВО ВЛАСТИ НАВАЖДЕНИЯ (ПЛАСТ.)         D</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>!ABBYY FineReader 12 Professional Edition Full...</td>\n",
       "      <td>1</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>***В ЛУЧАХ СЛАВЫ   (UNV)                    D</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>***ГОЛУБАЯ ВОЛНА  (Univ)                      D</td>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>***КОРОБКА (СТЕКЛО)                       D</td>\n",
       "      <td>4</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           item_name  item_id  item_category_id\n",
       "0          ! ВО ВЛАСТИ НАВАЖДЕНИЯ (ПЛАСТ.)         D        0                40\n",
       "1  !ABBYY FineReader 12 Professional Edition Full...        1                76\n",
       "2      ***В ЛУЧАХ СЛАВЫ   (UNV)                    D        2                40\n",
       "3    ***ГОЛУБАЯ ВОЛНА  (Univ)                      D        3                40\n",
       "4        ***КОРОБКА (СТЕКЛО)                       D        4                40"
      ]
     },
     "execution_count": 24,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_item.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "colab_type": "code",
    "id": "Z9rDlEx50tDD",
    "outputId": "0bf0dbe1-6a23-430e-be1e-2ba286f96b00"
   },
   "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>shop_name</th>\n",
       "      <th>shop_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>!Якутск Орджоникидзе, 56 фран</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>!Якутск ТЦ \"Центральный\" фран</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Адыгея ТЦ \"Мега\"</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Балашиха ТРК \"Октябрь-Киномир\"</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Волжский ТЦ \"Волга Молл\"</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        shop_name  shop_id\n",
       "0   !Якутск Орджоникидзе, 56 фран        0\n",
       "1   !Якутск ТЦ \"Центральный\" фран        1\n",
       "2                Адыгея ТЦ \"Мега\"        2\n",
       "3  Балашиха ТРК \"Октябрь-Киномир\"        3\n",
       "4        Волжский ТЦ \"Волга Молл\"        4"
      ]
     },
     "execution_count": 25,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_shops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 241
    },
    "colab_type": "code",
    "id": "5S52Ghwz0tDI",
    "outputId": "276aa081-9414-4d13-b829-54e8cb0c03d0"
   },
   "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>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>item_cnt_day</th>\n",
       "      <th>item_price</th>\n",
       "      <th>item_name</th>\n",
       "      <th>item_category_id</th>\n",
       "      <th>item_category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59</td>\n",
       "      <td>22154</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>ЯВЛЕНИЕ 2012 (BD)</td>\n",
       "      <td>37</td>\n",
       "      <td>Кино - Blu-Ray</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59</td>\n",
       "      <td>2552</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DEEP PURPLE  The House Of Blue Light  LP</td>\n",
       "      <td>58</td>\n",
       "      <td>Музыка - Винил</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59</td>\n",
       "      <td>2554</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DEEP PURPLE  Who Do You Think We Are  LP</td>\n",
       "      <td>58</td>\n",
       "      <td>Музыка - Винил</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59</td>\n",
       "      <td>2555</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DEEP PURPLE 30 Very Best Of 2CD (Фирм.)</td>\n",
       "      <td>56</td>\n",
       "      <td>Музыка - CD фирменного производства</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>DEEP PURPLE Perihelion: Live In Concert DVD (К...</td>\n",
       "      <td>59</td>\n",
       "      <td>Музыка - Музыкальное видео</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shop_id  item_id  ...  item_category_id                   item_category_name\n",
       "0       59    22154  ...                37                       Кино - Blu-Ray\n",
       "1       59     2552  ...                58                       Музыка - Винил\n",
       "2       59     2554  ...                58                       Музыка - Винил\n",
       "3       59     2555  ...                56  Музыка - CD фирменного производства\n",
       "4       59     2564  ...                59           Музыка - Музыкальное видео\n",
       "\n",
       "[5 rows x 8 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df_shop is not necessary to be merged with training dataframe as its just shop name and respective ids\n",
    "# lets merge df_item and df_item_cat with df_train_m\n",
    "df_train_m = pd.merge(df_train_m,df_item,on=['item_id'],how='left')\n",
    "df_train_m = pd.merge(df_train_m,df_item_cat,on=['item_category_id'], how='left')\n",
    "df_train_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "XEE2HP5C3ROA"
   },
   "outputs": [],
   "source": [
    "for type_id in ['item_id','shop_id','item_category_id']:\n",
    "    for column_id, agg_func, func_name in [('item_price',np.mean,'avg'),('item_cnt_day',np.sum,'sum'),('item_cnt_day',np.mean,'avg')]:\n",
    "\n",
    "        mean_df = df_train_m.groupby([type_id,'date_block_num']).agg(agg_func).reset_index()[[column_id,type_id,'date_block_num']]\n",
    "        mean_df.columns = [type_id+'_'+func_name+'_'+column_id, type_id,'date_block_num']\n",
    "        \n",
    "        df_train_m = pd.merge(df_train_m,mean_df,on=['date_block_num', type_id],how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "yopjkphC8a3v"
   },
   "source": [
    "We append test data into train data so we can create lag features on them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "colab_type": "code",
    "id": "eqKCKad55Fpj",
    "outputId": "e851fd04-23b5-4d06-bc2d-80dd8185fed0"
   },
   "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>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>item_category_id</th>\n",
       "      <th>item_category_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>5037</td>\n",
       "      <td>34</td>\n",
       "      <td>19</td>\n",
       "      <td>Игры - PS3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>5320</td>\n",
       "      <td>34</td>\n",
       "      <td>55</td>\n",
       "      <td>Музыка - CD локального производства</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>5233</td>\n",
       "      <td>34</td>\n",
       "      <td>19</td>\n",
       "      <td>Игры - PS3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>5232</td>\n",
       "      <td>34</td>\n",
       "      <td>23</td>\n",
       "      <td>Игры - XBOX 360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5268</td>\n",
       "      <td>34</td>\n",
       "      <td>20</td>\n",
       "      <td>Игры - PS4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shop_id  item_id  ...  item_category_id                   item_category_name\n",
       "0        5     5037  ...                19                           Игры - PS3\n",
       "1        5     5320  ...                55  Музыка - CD локального производства\n",
       "2        5     5233  ...                19                           Игры - PS3\n",
       "3        5     5232  ...                23                      Игры - XBOX 360\n",
       "4        5     5268  ...                20                           Игры - PS4\n",
       "\n",
       "[5 rows x 5 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_test = df_test.copy()\n",
    "temp_test['date_block_num'] = 34\n",
    "temp_test.drop('ID', axis=1, inplace=True)\n",
    "\n",
    "temp_test = temp_test.merge(df_item,on=['item_id'],how='left')\n",
    "temp_test = temp_test.merge(df_item_cat,on=['item_category_id'], how='left')\n",
    "temp_test.drop('item_name', axis=1, inplace=True)\n",
    "temp_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3DHv9-jG80qq"
   },
   "outputs": [],
   "source": [
    "df_train_m.drop('item_name', axis=1, inplace=True)\n",
    "df_train_m = pd.concat([df_train_m,temp_test], axis=0, ignore_index=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 278
    },
    "colab_type": "code",
    "id": "kSmN-kFM9N3P",
    "outputId": "e12b6401-4a4b-4ecd-f1a8-76be7e53235a"
   },
   "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>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>item_cnt_day</th>\n",
       "      <th>item_price</th>\n",
       "      <th>item_category_id</th>\n",
       "      <th>item_category_name</th>\n",
       "      <th>item_id_avg_item_price</th>\n",
       "      <th>item_id_sum_item_cnt_day</th>\n",
       "      <th>item_id_avg_item_cnt_day</th>\n",
       "      <th>shop_id_avg_item_price</th>\n",
       "      <th>shop_id_sum_item_cnt_day</th>\n",
       "      <th>shop_id_avg_item_cnt_day</th>\n",
       "      <th>item_category_id_avg_item_price</th>\n",
       "      <th>item_category_id_sum_item_cnt_day</th>\n",
       "      <th>item_category_id_avg_item_cnt_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59</td>\n",
       "      <td>22154</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>999.0</td>\n",
       "      <td>37</td>\n",
       "      <td>Кино - Blu-Ray</td>\n",
       "      <td>310.800000</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.400000</td>\n",
       "      <td>97.974472</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>0.248552</td>\n",
       "      <td>63.014719</td>\n",
       "      <td>6094.0</td>\n",
       "      <td>0.199738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59</td>\n",
       "      <td>2552</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>58</td>\n",
       "      <td>Музыка - Винил</td>\n",
       "      <td>19.977778</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>97.974472</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>0.248552</td>\n",
       "      <td>54.898665</td>\n",
       "      <td>287.0</td>\n",
       "      <td>0.043386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59</td>\n",
       "      <td>2554</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>58</td>\n",
       "      <td>Музыка - Винил</td>\n",
       "      <td>37.978889</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.022222</td>\n",
       "      <td>97.974472</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>0.248552</td>\n",
       "      <td>54.898665</td>\n",
       "      <td>287.0</td>\n",
       "      <td>0.043386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59</td>\n",
       "      <td>2555</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>56</td>\n",
       "      <td>Музыка - CD фирменного производства</td>\n",
       "      <td>48.837778</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.044444</td>\n",
       "      <td>97.974472</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>0.248552</td>\n",
       "      <td>38.267019</td>\n",
       "      <td>268.0</td>\n",
       "      <td>0.049630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59</td>\n",
       "      <td>2564</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59</td>\n",
       "      <td>Музыка - Музыкальное видео</td>\n",
       "      <td>36.400000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>97.974472</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>0.248552</td>\n",
       "      <td>35.593867</td>\n",
       "      <td>701.0</td>\n",
       "      <td>0.093280</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   shop_id  ...  item_category_id_avg_item_cnt_day\n",
       "0       59  ...                           0.199738\n",
       "1       59  ...                           0.043386\n",
       "2       59  ...                           0.043386\n",
       "3       59  ...                           0.049630\n",
       "4       59  ...                           0.093280\n",
       "\n",
       "[5 rows x 16 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_m.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "gte3gsOn9T7q"
   },
   "outputs": [],
   "source": [
    "df_train_m = df_train_m.drop('item_category_name',axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "eL-tcM3l99m8"
   },
   "source": [
    "We create lags on 10 features. 9 features are from mean encoding and 1 feature is the item_cnt_day.\n",
    "\n",
    "After several tries, we found that month lag intervals of 1, 2, 3, 4, 5, 6, 9 and 12 give best score in the leaderboard. Take note that 16GB of ram is unable to compute such large number of interval. We need at least 64GB of ram. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "x5_gKtOR9pJu"
   },
   "outputs": [],
   "source": [
    "lag_variables  = ['item_id_avg_item_price',\n",
    "'item_id_sum_item_cnt_day',\n",
    "'item_id_avg_item_cnt_day',\n",
    "'shop_id_avg_item_price',\n",
    "'shop_id_sum_item_cnt_day',\n",
    "'shop_id_avg_item_cnt_day',\n",
    "'item_category_id_avg_item_price',\n",
    "'item_category_id_sum_item_cnt_day',\n",
    "'item_category_id_avg_item_cnt_day',\n",
    "'item_cnt_day']\n",
    "\n",
    "#Limited by computation resource\n",
    "lags = [1,2]\n",
    "#lags = [1, 2, 3, 4, 5, 6, 12]\n",
    "\n",
    "for lag in lags:\n",
    "    df_train_m_new = df_train_m.copy()\n",
    "    df_train_m_new.date_block_num+=lag\n",
    "    df_train_m_new = df_train_m_new[['date_block_num','shop_id','item_id']+lag_variables]\n",
    "    df_train_m_new.columns = ['date_block_num','shop_id','item_id']+ [lag_feat+'_lag_'+str(lag) for lag_feat in lag_variables]\n",
    "    df_train_m = df_train_m.merge(df_train_m_new,on=['date_block_num','shop_id','item_id'] ,how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 320
    },
    "colab_type": "code",
    "id": "3vzb2vUw-77z",
    "outputId": "963f5979-6649-43a7-caec-411821be127a"
   },
   "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>shop_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>item_cnt_day</th>\n",
       "      <th>item_price</th>\n",
       "      <th>item_category_id</th>\n",
       "      <th>item_id_avg_item_price</th>\n",
       "      <th>item_id_sum_item_cnt_day</th>\n",
       "      <th>item_id_avg_item_cnt_day</th>\n",
       "      <th>shop_id_avg_item_price</th>\n",
       "      <th>shop_id_sum_item_cnt_day</th>\n",
       "      <th>shop_id_avg_item_cnt_day</th>\n",
       "      <th>item_category_id_avg_item_price</th>\n",
       "      <th>item_category_id_sum_item_cnt_day</th>\n",
       "      <th>item_category_id_avg_item_cnt_day</th>\n",
       "      <th>item_id_avg_item_price_lag_1</th>\n",
       "      <th>item_id_sum_item_cnt_day_lag_1</th>\n",
       "      <th>item_id_avg_item_cnt_day_lag_1</th>\n",
       "      <th>shop_id_avg_item_price_lag_1</th>\n",
       "      <th>shop_id_sum_item_cnt_day_lag_1</th>\n",
       "      <th>shop_id_avg_item_cnt_day_lag_1</th>\n",
       "      <th>item_category_id_avg_item_price_lag_1</th>\n",
       "      <th>item_category_id_sum_item_cnt_day_lag_1</th>\n",
       "      <th>item_category_id_avg_item_cnt_day_lag_1</th>\n",
       "      <th>item_cnt_day_lag_1</th>\n",
       "      <th>item_id_avg_item_price_lag_2</th>\n",
       "      <th>item_id_sum_item_cnt_day_lag_2</th>\n",
       "      <th>item_id_avg_item_cnt_day_lag_2</th>\n",
       "      <th>shop_id_avg_item_price_lag_2</th>\n",
       "      <th>shop_id_sum_item_cnt_day_lag_2</th>\n",
       "      <th>shop_id_avg_item_cnt_day_lag_2</th>\n",
       "      <th>item_category_id_avg_item_price_lag_2</th>\n",
       "      <th>item_category_id_sum_item_cnt_day_lag_2</th>\n",
       "      <th>item_category_id_avg_item_cnt_day_lag_2</th>\n",
       "      <th>item_cnt_day_lag_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.112805e+07</td>\n",
       "      <td>1.112805e+07</td>\n",
       "      <td>1.112805e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.112805e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>1.091385e+07</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>8.605461e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "      <td>7.953134e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.119598e+01</td>\n",
       "      <td>1.130368e+04</td>\n",
       "      <td>1.533958e+01</td>\n",
       "      <td>3.339826e-01</td>\n",
       "      <td>1.165788e+02</td>\n",
       "      <td>4.494411e+01</td>\n",
       "      <td>1.165788e+02</td>\n",
       "      <td>1.563809e+01</td>\n",
       "      <td>3.339826e-01</td>\n",
       "      <td>1.165788e+02</td>\n",
       "      <td>2.375230e+03</td>\n",
       "      <td>3.339826e-01</td>\n",
       "      <td>1.165788e+02</td>\n",
       "      <td>8.501826e+03</td>\n",
       "      <td>3.339826e-01</td>\n",
       "      <td>1.397048e+02</td>\n",
       "      <td>1.904271e+01</td>\n",
       "      <td>4.066517e-01</td>\n",
       "      <td>1.167099e+02</td>\n",
       "      <td>2.390998e+03</td>\n",
       "      <td>3.348756e-01</td>\n",
       "      <td>1.217431e+02</td>\n",
       "      <td>8.750132e+03</td>\n",
       "      <td>3.512910e-01</td>\n",
       "      <td>4.075280e-01</td>\n",
       "      <td>1.426280e+02</td>\n",
       "      <td>1.973502e+01</td>\n",
       "      <td>4.210480e-01</td>\n",
       "      <td>1.157925e+02</td>\n",
       "      <td>2.410998e+03</td>\n",
       "      <td>3.361374e-01</td>\n",
       "      <td>1.209647e+02</td>\n",
       "      <td>8.838638e+03</td>\n",
       "      <td>3.525775e-01</td>\n",
       "      <td>4.229887e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.735381e+01</td>\n",
       "      <td>6.210930e+03</td>\n",
       "      <td>9.760396e+00</td>\n",
       "      <td>3.334916e+00</td>\n",
       "      <td>6.518409e+02</td>\n",
       "      <td>1.514009e+01</td>\n",
       "      <td>4.436272e+02</td>\n",
       "      <td>9.621937e+01</td>\n",
       "      <td>2.041777e+00</td>\n",
       "      <td>6.043432e+01</td>\n",
       "      <td>1.952808e+03</td>\n",
       "      <td>2.644711e-01</td>\n",
       "      <td>3.204518e+02</td>\n",
       "      <td>8.973466e+03</td>\n",
       "      <td>1.638926e+00</td>\n",
       "      <td>4.914868e+02</td>\n",
       "      <td>1.071965e+02</td>\n",
       "      <td>2.273987e+00</td>\n",
       "      <td>6.006481e+01</td>\n",
       "      <td>1.952238e+03</td>\n",
       "      <td>2.637254e-01</td>\n",
       "      <td>3.378542e+02</td>\n",
       "      <td>9.017931e+03</td>\n",
       "      <td>1.812894e+00</td>\n",
       "      <td>3.578130e+00</td>\n",
       "      <td>4.940315e+02</td>\n",
       "      <td>1.097803e+02</td>\n",
       "      <td>2.328683e+00</td>\n",
       "      <td>5.916657e+01</td>\n",
       "      <td>1.962018e+03</td>\n",
       "      <td>2.644368e-01</td>\n",
       "      <td>3.346894e+02</td>\n",
       "      <td>9.052480e+03</td>\n",
       "      <td>1.851287e+00</td>\n",
       "      <td>3.619428e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-2.200000e+01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>-2.200000e+01</td>\n",
       "      <td>-4.888889e-01</td>\n",
       "      <td>2.329451e-02</td>\n",
       "      <td>-1.000000e+00</td>\n",
       "      <td>-1.966568e-04</td>\n",
       "      <td>2.600000e-01</td>\n",
       "      <td>-1.000000e+00</td>\n",
       "      <td>-2.272727e-02</td>\n",
       "      <td>4.444444e-03</td>\n",
       "      <td>-2.200000e+01</td>\n",
       "      <td>-4.888889e-01</td>\n",
       "      <td>2.329451e-02</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>3.025261e-04</td>\n",
       "      <td>4.130435e-01</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>7.751938e-03</td>\n",
       "      <td>-2.200000e+01</td>\n",
       "      <td>4.444444e-03</td>\n",
       "      <td>-2.200000e+01</td>\n",
       "      <td>-4.888889e-01</td>\n",
       "      <td>2.329451e-02</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>3.025261e-04</td>\n",
       "      <td>4.130435e-01</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.785714e-02</td>\n",
       "      <td>-2.200000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.600000e+01</td>\n",
       "      <td>5.947000e+03</td>\n",
       "      <td>7.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.700000e+01</td>\n",
       "      <td>9.652174e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>3.921569e-02</td>\n",
       "      <td>7.951914e+01</td>\n",
       "      <td>1.228000e+03</td>\n",
       "      <td>1.837063e-01</td>\n",
       "      <td>2.973899e+01</td>\n",
       "      <td>1.077000e+03</td>\n",
       "      <td>1.578334e-01</td>\n",
       "      <td>1.328889e+01</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>4.651163e-02</td>\n",
       "      <td>7.945678e+01</td>\n",
       "      <td>1.243000e+03</td>\n",
       "      <td>1.853114e-01</td>\n",
       "      <td>2.993934e+01</td>\n",
       "      <td>1.162000e+03</td>\n",
       "      <td>1.714948e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.436957e+01</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>6.000000e-02</td>\n",
       "      <td>7.920055e+01</td>\n",
       "      <td>1.251000e+03</td>\n",
       "      <td>1.863496e-01</td>\n",
       "      <td>2.993934e+01</td>\n",
       "      <td>1.169000e+03</td>\n",
       "      <td>1.714948e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.000000e+01</td>\n",
       "      <td>1.138800e+04</td>\n",
       "      <td>1.500000e+01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>4.000000e+01</td>\n",
       "      <td>2.706250e+01</td>\n",
       "      <td>4.000000e+00</td>\n",
       "      <td>8.695652e-02</td>\n",
       "      <td>1.026577e+02</td>\n",
       "      <td>1.807000e+03</td>\n",
       "      <td>2.560714e-01</td>\n",
       "      <td>4.557390e+01</td>\n",
       "      <td>5.959000e+03</td>\n",
       "      <td>2.254803e-01</td>\n",
       "      <td>3.481395e+01</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>1.276596e-01</td>\n",
       "      <td>1.026263e+02</td>\n",
       "      <td>1.816000e+03</td>\n",
       "      <td>2.566306e-01</td>\n",
       "      <td>4.569278e+01</td>\n",
       "      <td>6.088000e+03</td>\n",
       "      <td>2.285004e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3.588000e+01</td>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>1.304348e-01</td>\n",
       "      <td>1.020176e+02</td>\n",
       "      <td>1.833000e+03</td>\n",
       "      <td>2.573808e-01</td>\n",
       "      <td>4.569278e+01</td>\n",
       "      <td>6.181000e+03</td>\n",
       "      <td>2.285004e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>4.700000e+01</td>\n",
       "      <td>1.659200e+04</td>\n",
       "      <td>2.300000e+01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>5.500000e+01</td>\n",
       "      <td>7.740851e+01</td>\n",
       "      <td>1.100000e+01</td>\n",
       "      <td>2.391304e-01</td>\n",
       "      <td>1.402056e+02</td>\n",
       "      <td>2.683000e+03</td>\n",
       "      <td>3.571321e-01</td>\n",
       "      <td>1.110535e+02</td>\n",
       "      <td>1.283400e+04</td>\n",
       "      <td>2.876345e-01</td>\n",
       "      <td>9.862222e+01</td>\n",
       "      <td>1.400000e+01</td>\n",
       "      <td>3.043478e-01</td>\n",
       "      <td>1.399021e+02</td>\n",
       "      <td>2.700000e+03</td>\n",
       "      <td>3.564936e-01</td>\n",
       "      <td>1.165383e+02</td>\n",
       "      <td>1.308400e+04</td>\n",
       "      <td>2.929805e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.017917e+02</td>\n",
       "      <td>1.500000e+01</td>\n",
       "      <td>3.191489e-01</td>\n",
       "      <td>1.384293e+02</td>\n",
       "      <td>2.724000e+03</td>\n",
       "      <td>3.571321e-01</td>\n",
       "      <td>1.162283e+02</td>\n",
       "      <td>1.361600e+04</td>\n",
       "      <td>2.929805e-01</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>5.900000e+01</td>\n",
       "      <td>2.216900e+04</td>\n",
       "      <td>3.400000e+01</td>\n",
       "      <td>1.644000e+03</td>\n",
       "      <td>5.099900e+04</td>\n",
       "      <td>8.300000e+01</td>\n",
       "      <td>2.408645e+04</td>\n",
       "      <td>1.255700e+04</td>\n",
       "      <td>2.729783e+02</td>\n",
       "      <td>4.426968e+02</td>\n",
       "      <td>1.631800e+04</td>\n",
       "      <td>2.211961e+00</td>\n",
       "      <td>2.255054e+04</td>\n",
       "      <td>3.627700e+04</td>\n",
       "      <td>2.729783e+02</td>\n",
       "      <td>2.408645e+04</td>\n",
       "      <td>1.255700e+04</td>\n",
       "      <td>2.729783e+02</td>\n",
       "      <td>4.426968e+02</td>\n",
       "      <td>1.631800e+04</td>\n",
       "      <td>2.211961e+00</td>\n",
       "      <td>2.255054e+04</td>\n",
       "      <td>3.627700e+04</td>\n",
       "      <td>2.729783e+02</td>\n",
       "      <td>1.305000e+03</td>\n",
       "      <td>2.408645e+04</td>\n",
       "      <td>1.255700e+04</td>\n",
       "      <td>2.729783e+02</td>\n",
       "      <td>4.420703e+02</td>\n",
       "      <td>1.631800e+04</td>\n",
       "      <td>2.211961e+00</td>\n",
       "      <td>2.255054e+04</td>\n",
       "      <td>3.627700e+04</td>\n",
       "      <td>2.729783e+02</td>\n",
       "      <td>1.305000e+03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            shop_id  ...  item_cnt_day_lag_2\n",
       "count  1.112805e+07  ...        7.953134e+06\n",
       "mean   3.119598e+01  ...        4.229887e-01\n",
       "std    1.735381e+01  ...        3.619428e+00\n",
       "min    0.000000e+00  ...       -2.200000e+01\n",
       "25%    1.600000e+01  ...        0.000000e+00\n",
       "50%    3.000000e+01  ...        0.000000e+00\n",
       "75%    4.700000e+01  ...        0.000000e+00\n",
       "max    5.900000e+01  ...        1.305000e+03\n",
       "\n",
       "[8 rows x 35 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_m.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "colab_type": "code",
    "id": "s780tJnY_LgR",
    "outputId": "39fc2d19-27ee-43e4-d78d-caa56c3b4363"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11128050, 35)"
      ]
     },
     "execution_count": 34,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train_m.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "3RDwEHjP_O1g"
   },
   "outputs": [],
   "source": [
    "for feat in df_train_m.columns:\n",
    "    if 'item_cnt' in feat:\n",
    "        df_train_m[feat]=df_train_m[feat].fillna(0)\n",
    "    elif 'item_price' in feat:\n",
    "        df_train_m[feat]=df_train_m[feat].fillna(df_train_m[feat].median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "IgKpyn5uBICJ"
   },
   "outputs": [],
   "source": [
    "cols_to_drop = lag_variables[:-1] + ['item_price']\n",
    "df_train_m = df_train_m[df_train_m['date_block_num']>12]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6rbES2ktBTtL"
   },
   "source": [
    "# Validation\n",
    "\n",
    "We use holdout scheme for cross validation. We use sales month from 12 to 32 for training, month 33 for validation and month 34 for testing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "64QwY7t3BUrD"
   },
   "outputs": [],
   "source": [
    "X_train = df_train_m[df_train_m['date_block_num']<33].drop(cols_to_drop, axis=1)\n",
    "X_cv =  df_train_m[df_train_m['date_block_num']==33].drop(cols_to_drop, axis=1)\n",
    "X_test = df_train_m[df_train_m['date_block_num']==34].drop(cols_to_drop, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "MjcfWRDCBphG"
   },
   "source": [
    "# Reference from discussion\n",
    "\n",
    "From discussion in Kaggle forum, we applied this useful trick, which clip the item_cnt_day within 40 instead of 20. In this way, there are more predictions on item_cnt_day = 20."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "cyNgkmmRBd9D"
   },
   "outputs": [],
   "source": [
    "X_train['item_cnt_day'].clip(0,40, inplace=True)\n",
    "\n",
    "X_cv['item_cnt_day'].clip(0,40, inplace=True)\n",
    "\n",
    "X_test['item_cnt_day'].clip(0,40, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "VU6FfG-fEL2x"
   },
   "outputs": [],
   "source": [
    "X_train.to_csv('drive/My Drive/CourseraProject/data/X_train.csv', index=False)\n",
    "X_cv.to_csv('drive/My Drive/CourseraProject/data/X_cv.csv', index=False)\n",
    "X_test.to_csv('drive/My Drive/CourseraProject/data/X_test.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "TkI0oL7n0s_-"
   ],
   "name": "CourseraProject-part1.ipynb",
   "provenance": []
  },
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}