BIP_submission_function.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BIP's submission function\n",
    "\n",
    "A function which goal is to return a dataframe with the exact format of the predicted dataset to be delivered. \n",
    "\n",
    "It starts from a dataframe with predictions **NOT yet summed by month**.\n",
    "\n",
    "The input dataframe should **at least** contain the attributes:\n",
    "\n",
    "| StoreID |  D_Month | NumberOfSales | _NumberOfSales |\n",
    "| --------| -------- | --------------| -------------- |  \n",
    "|    1000 |        1 |   8540.000000 |    9776.962748 |  \n",
    "|    1000 |        1 |  10364.000000 |    6018.866667 |  \n",
    "|    1000 |        2 |   4676.000000 |    6234.658815 |  \n",
    "|    1000 |        3 |   7675.446488 |    6513.012222 |  \n",
    "|    1000 |        5 |   6267.000000 |    7242.277146 | \n",
    "\n",
    "where:\n",
    " \n",
    " - *NumberOfSales* are the **real** values\n",
    " - *_NumberOfSales* are the **predicted** values\n",
    " \n",
    " \n",
    "### **WARNING:** No check is done on the months. They can be any arbitrary month"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start from an arbitrary test set to simulate a submission of the predicted dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>StoreID</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>HasPromotions</th>\n",
       "      <th>NearestCompetitor</th>\n",
       "      <th>Region</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>Region_AreaKM2</th>\n",
       "      <th>Region_GDP</th>\n",
       "      <th>Region_PopulationK</th>\n",
       "      <th>...</th>\n",
       "      <th>p4</th>\n",
       "      <th>p5</th>\n",
       "      <th>p6</th>\n",
       "      <th>p7</th>\n",
       "      <th>p8</th>\n",
       "      <th>p9</th>\n",
       "      <th>p10</th>\n",
       "      <th>p11</th>\n",
       "      <th>p12</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>8540</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000163</td>\n",
       "      <td>-0.002012</td>\n",
       "      <td>0.002307</td>\n",
       "      <td>0.001049</td>\n",
       "      <td>-0.001226</td>\n",
       "      <td>0.000372</td>\n",
       "      <td>0.000095</td>\n",
       "      <td>0.000836</td>\n",
       "      <td>-0.000170</td>\n",
       "      <td>9908.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>10364</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000491</td>\n",
       "      <td>-0.002070</td>\n",
       "      <td>0.002708</td>\n",
       "      <td>-0.000669</td>\n",
       "      <td>-0.001426</td>\n",
       "      <td>-0.000215</td>\n",
       "      <td>-0.000540</td>\n",
       "      <td>0.000367</td>\n",
       "      <td>-0.000163</td>\n",
       "      <td>6420.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000</td>\n",
       "      <td>03/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>4676</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000582</td>\n",
       "      <td>-0.002125</td>\n",
       "      <td>0.003129</td>\n",
       "      <td>-0.000895</td>\n",
       "      <td>-0.001529</td>\n",
       "      <td>-0.000557</td>\n",
       "      <td>0.000031</td>\n",
       "      <td>-0.000107</td>\n",
       "      <td>-0.000037</td>\n",
       "      <td>4952.240598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000</td>\n",
       "      <td>05/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>6267</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000389</td>\n",
       "      <td>0.002572</td>\n",
       "      <td>-0.000628</td>\n",
       "      <td>-0.000278</td>\n",
       "      <td>-0.001221</td>\n",
       "      <td>0.000540</td>\n",
       "      <td>0.000499</td>\n",
       "      <td>-0.000087</td>\n",
       "      <td>-0.000236</td>\n",
       "      <td>6538.379156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000</td>\n",
       "      <td>06/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>5953</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000154</td>\n",
       "      <td>0.000616</td>\n",
       "      <td>0.003896</td>\n",
       "      <td>0.000200</td>\n",
       "      <td>-0.001384</td>\n",
       "      <td>-0.000087</td>\n",
       "      <td>-0.000091</td>\n",
       "      <td>0.000085</td>\n",
       "      <td>0.000273</td>\n",
       "      <td>7872.339310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID        Date  IsHoliday  HasPromotions  NearestCompetitor  Region  \\\n",
       "0     1000  01/01/2018          0              0                326       7   \n",
       "1     1000  02/01/2018          0              0                326       7   \n",
       "2     1000  03/01/2018          0              0                326       7   \n",
       "3     1000  05/01/2018          0              0                326       7   \n",
       "4     1000  06/01/2018          0              0                326       7   \n",
       "\n",
       "   NumberOfSales  Region_AreaKM2  Region_GDP  Region_PopulationK  \\\n",
       "0           8540            9643       17130                2770   \n",
       "1          10364            9643       17130                2770   \n",
       "2           4676            9643       17130                2770   \n",
       "3           6267            9643       17130                2770   \n",
       "4           5953            9643       17130                2770   \n",
       "\n",
       "        ...              p4        p5        p6        p7        p8        p9  \\\n",
       "0       ...       -0.000163 -0.002012  0.002307  0.001049 -0.001226  0.000372   \n",
       "1       ...       -0.000491 -0.002070  0.002708 -0.000669 -0.001426 -0.000215   \n",
       "2       ...       -0.000582 -0.002125  0.003129 -0.000895 -0.001529 -0.000557   \n",
       "3       ...       -0.000389  0.002572 -0.000628 -0.000278 -0.001221  0.000540   \n",
       "4       ...       -0.000154  0.000616  0.003896  0.000200 -0.001384 -0.000087   \n",
       "\n",
       "        p10       p11       p12  _NumberOfSales  \n",
       "0  0.000095  0.000836 -0.000170     9908.500000  \n",
       "1 -0.000540  0.000367 -0.000163     6420.166667  \n",
       "2  0.000031 -0.000107 -0.000037     4952.240598  \n",
       "3  0.000499 -0.000087 -0.000236     6538.379156  \n",
       "4 -0.000091  0.000085  0.000273     7872.339310  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from import_man import *\n",
    "\n",
    "submission_features = ['StoreID', 'D_Month', 'NumberOfSales', '_NumberOfSales']\n",
    "\n",
    "df = pd.read_csv('./dataset/test_m12_53_RFR_on_prep.csv')\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>StoreID</th>\n",
       "      <th>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>8540</td>\n",
       "      <td>9908.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>10364</td>\n",
       "      <td>6420.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>4676</td>\n",
       "      <td>4952.240598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>6267</td>\n",
       "      <td>6538.379156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>5953</td>\n",
       "      <td>7872.339310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  D_Month  NumberOfSales  _NumberOfSales\n",
       "0     1000        1           8540     9908.500000\n",
       "1     1000        1          10364     6420.166667\n",
       "2     1000        1           4676     4952.240598\n",
       "3     1000        1           6267     6538.379156\n",
       "4     1000        1           5953     7872.339310"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[submission_features]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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></th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreID</th>\n",
       "      <th>D_Month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1000</th>\n",
       "      <th>1</th>\n",
       "      <td>182917</td>\n",
       "      <td>201451.830901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>166161</td>\n",
       "      <td>184721.490504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1001</th>\n",
       "      <th>1</th>\n",
       "      <td>95745</td>\n",
       "      <td>83185.502132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>88423</td>\n",
       "      <td>72102.648063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1002</th>\n",
       "      <th>1</th>\n",
       "      <td>121995</td>\n",
       "      <td>134334.706754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 NumberOfSales  _NumberOfSales\n",
       "StoreID D_Month                               \n",
       "1000    1               182917   201451.830901\n",
       "        2               166161   184721.490504\n",
       "1001    1                95745    83185.502132\n",
       "        2                88423    72102.648063\n",
       "1002    1               121995   134334.706754"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's sum NumberOfSales by the store and month\n",
    "df = df.groupby(['StoreID', 'D_Month']).sum()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>StoreID</th>\n",
       "      <th>D_Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>182917</td>\n",
       "      <td>201451.830901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>2</td>\n",
       "      <td>166161</td>\n",
       "      <td>184721.490504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>95745</td>\n",
       "      <td>83185.502132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1001</td>\n",
       "      <td>2</td>\n",
       "      <td>88423</td>\n",
       "      <td>72102.648063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>121995</td>\n",
       "      <td>134334.706754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  D_Month  NumberOfSales  _NumberOfSales\n",
       "0     1000        1         182917   201451.830901\n",
       "1     1000        2         166161   184721.490504\n",
       "2     1001        1          95745    83185.502132\n",
       "3     1001        2          88423    72102.648063\n",
       "4     1002        1         121995   134334.706754"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index(inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>StoreID</th>\n",
       "      <th>Month</th>\n",
       "      <th>Target</th>\n",
       "      <th>NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>182917</td>\n",
       "      <td>201451.830901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>2</td>\n",
       "      <td>166161</td>\n",
       "      <td>184721.490504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>95745</td>\n",
       "      <td>83185.502132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1001</td>\n",
       "      <td>2</td>\n",
       "      <td>88423</td>\n",
       "      <td>72102.648063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>121995</td>\n",
       "      <td>134334.706754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Month  Target  NumberOfSales\n",
       "0     1000      1  182917  201451.830901\n",
       "1     1000      2  166161  184721.490504\n",
       "2     1001      1   95745   83185.502132\n",
       "3     1001      2   88423   72102.648063\n",
       "4     1002      1  121995  134334.706754"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# name conversion from our standard to BIP submission requirements\n",
    "df.rename(index=str, columns={\"D_Month\": \"Month\", \"NumberOfSales\": \"Target\", \"_NumberOfSales\": \"NumberOfSales\"}, inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./dataset/submission_fake.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The `apply_BIP_submission_format` function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def apply_BIP_submission_format(df, real_submit=False):\n",
    "    \"\"\"\n",
    "    Given a dataframe with predictions, **NOT yet summed by month** it returns a dataframe which\n",
    "    format is exactly the one required for the submission.\n",
    "\n",
    "    If real_submit is set to False (default), the function will require (and also include in the\n",
    "    outputted dataframe) the value of the *true* NumberOfSale.\n",
    "    \n",
    "    Required attributes (real_submit=False):\n",
    "        ['StoreID', 'D_Month', 'NumberOfSales', '_NumberOfSales']\n",
    "    \n",
    "    Required attributes (real_submit=True):\n",
    "        ['StoreID', 'D_Month', '_NumberOfSales']\n",
    "\n",
    "    \n",
    "    :param df: The data frame\n",
    "    :param real_submit: Whether the dataframe returned is for the real submit or not.\n",
    "    :return: Dataframe in the submit format.\n",
    "    \"\"\"\n",
    "    submission_features = ['StoreID', 'D_Month', 'NumberOfSales', '_NumberOfSales']\n",
    "    \n",
    "    if real_submit:\n",
    "        submission_features.remove('NumberOfSales')\n",
    "    \n",
    "    # create a copy of the dataframe to do not change the provided one\n",
    "    df = df.copy()\n",
    "    \n",
    "    # Remove useless columns and select all the ones required.\n",
    "    # Implicit check that all the required columns are present.\n",
    "    df = df[submission_features]\n",
    "    \n",
    "    # sum _NumberOfSales (and NumberOfSales) by the store and month\n",
    "    df = df.groupby(['StoreID', 'D_Month']).sum()\n",
    "    \n",
    "    df.reset_index(inplace=True)\n",
    "    \n",
    "    # name conversion from our standard to BIP submission standard\n",
    "    columns_renamings = {\n",
    "        'D_Month': 'Month',\n",
    "        'NumberOfSales': 'Target',\n",
    "        '_NumberOfSales': 'NumberOfSales'\n",
    "    }\n",
    "    # remove NumberOfSales renaming in case of real_submit\n",
    "    if real_submit:\n",
    "        columns_renamings.pop('NumberOfSales')\n",
    "    \n",
    "    df.rename(index=str, inplace=True, columns=columns_renamings)\n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function test"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### real_submit=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>StoreID</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>HasPromotions</th>\n",
       "      <th>NearestCompetitor</th>\n",
       "      <th>Region</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>Region_AreaKM2</th>\n",
       "      <th>Region_GDP</th>\n",
       "      <th>Region_PopulationK</th>\n",
       "      <th>...</th>\n",
       "      <th>p4</th>\n",
       "      <th>p5</th>\n",
       "      <th>p6</th>\n",
       "      <th>p7</th>\n",
       "      <th>p8</th>\n",
       "      <th>p9</th>\n",
       "      <th>p10</th>\n",
       "      <th>p11</th>\n",
       "      <th>p12</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>8540</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000163</td>\n",
       "      <td>-0.002012</td>\n",
       "      <td>0.002307</td>\n",
       "      <td>0.001049</td>\n",
       "      <td>-0.001226</td>\n",
       "      <td>0.000372</td>\n",
       "      <td>0.000095</td>\n",
       "      <td>0.000836</td>\n",
       "      <td>-0.000170</td>\n",
       "      <td>9908.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>10364</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000491</td>\n",
       "      <td>-0.002070</td>\n",
       "      <td>0.002708</td>\n",
       "      <td>-0.000669</td>\n",
       "      <td>-0.001426</td>\n",
       "      <td>-0.000215</td>\n",
       "      <td>-0.000540</td>\n",
       "      <td>0.000367</td>\n",
       "      <td>-0.000163</td>\n",
       "      <td>6420.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000</td>\n",
       "      <td>03/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>4676</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000582</td>\n",
       "      <td>-0.002125</td>\n",
       "      <td>0.003129</td>\n",
       "      <td>-0.000895</td>\n",
       "      <td>-0.001529</td>\n",
       "      <td>-0.000557</td>\n",
       "      <td>0.000031</td>\n",
       "      <td>-0.000107</td>\n",
       "      <td>-0.000037</td>\n",
       "      <td>4952.240598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000</td>\n",
       "      <td>05/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>6267</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000389</td>\n",
       "      <td>0.002572</td>\n",
       "      <td>-0.000628</td>\n",
       "      <td>-0.000278</td>\n",
       "      <td>-0.001221</td>\n",
       "      <td>0.000540</td>\n",
       "      <td>0.000499</td>\n",
       "      <td>-0.000087</td>\n",
       "      <td>-0.000236</td>\n",
       "      <td>6538.379156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000</td>\n",
       "      <td>06/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>5953</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000154</td>\n",
       "      <td>0.000616</td>\n",
       "      <td>0.003896</td>\n",
       "      <td>0.000200</td>\n",
       "      <td>-0.001384</td>\n",
       "      <td>-0.000087</td>\n",
       "      <td>-0.000091</td>\n",
       "      <td>0.000085</td>\n",
       "      <td>0.000273</td>\n",
       "      <td>7872.339310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID        Date  IsHoliday  HasPromotions  NearestCompetitor  Region  \\\n",
       "0     1000  01/01/2018          0              0                326       7   \n",
       "1     1000  02/01/2018          0              0                326       7   \n",
       "2     1000  03/01/2018          0              0                326       7   \n",
       "3     1000  05/01/2018          0              0                326       7   \n",
       "4     1000  06/01/2018          0              0                326       7   \n",
       "\n",
       "   NumberOfSales  Region_AreaKM2  Region_GDP  Region_PopulationK  \\\n",
       "0           8540            9643       17130                2770   \n",
       "1          10364            9643       17130                2770   \n",
       "2           4676            9643       17130                2770   \n",
       "3           6267            9643       17130                2770   \n",
       "4           5953            9643       17130                2770   \n",
       "\n",
       "        ...              p4        p5        p6        p7        p8        p9  \\\n",
       "0       ...       -0.000163 -0.002012  0.002307  0.001049 -0.001226  0.000372   \n",
       "1       ...       -0.000491 -0.002070  0.002708 -0.000669 -0.001426 -0.000215   \n",
       "2       ...       -0.000582 -0.002125  0.003129 -0.000895 -0.001529 -0.000557   \n",
       "3       ...       -0.000389  0.002572 -0.000628 -0.000278 -0.001221  0.000540   \n",
       "4       ...       -0.000154  0.000616  0.003896  0.000200 -0.001384 -0.000087   \n",
       "\n",
       "        p10       p11       p12  _NumberOfSales  \n",
       "0  0.000095  0.000836 -0.000170     9908.500000  \n",
       "1 -0.000540  0.000367 -0.000163     6420.166667  \n",
       "2  0.000031 -0.000107 -0.000037     4952.240598  \n",
       "3  0.000499 -0.000087 -0.000236     6538.379156  \n",
       "4 -0.000091  0.000085  0.000273     7872.339310  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./dataset/test_m12_53_RFR_on_prep.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>StoreID</th>\n",
       "      <th>Month</th>\n",
       "      <th>Target</th>\n",
       "      <th>NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>182917</td>\n",
       "      <td>201451.830901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>2</td>\n",
       "      <td>166161</td>\n",
       "      <td>184721.490504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>95745</td>\n",
       "      <td>83185.502132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1001</td>\n",
       "      <td>2</td>\n",
       "      <td>88423</td>\n",
       "      <td>72102.648063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>121995</td>\n",
       "      <td>134334.706754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Month  Target  NumberOfSales\n",
       "0     1000      1  182917  201451.830901\n",
       "1     1000      2  166161  184721.490504\n",
       "2     1001      1   95745   83185.502132\n",
       "3     1001      2   88423   72102.648063\n",
       "4     1002      1  121995  134334.706754"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = apply_BIP_submission_format(df)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### real_submit=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>StoreID</th>\n",
       "      <th>Date</th>\n",
       "      <th>IsHoliday</th>\n",
       "      <th>HasPromotions</th>\n",
       "      <th>NearestCompetitor</th>\n",
       "      <th>Region</th>\n",
       "      <th>NumberOfSales</th>\n",
       "      <th>Region_AreaKM2</th>\n",
       "      <th>Region_GDP</th>\n",
       "      <th>Region_PopulationK</th>\n",
       "      <th>...</th>\n",
       "      <th>p4</th>\n",
       "      <th>p5</th>\n",
       "      <th>p6</th>\n",
       "      <th>p7</th>\n",
       "      <th>p8</th>\n",
       "      <th>p9</th>\n",
       "      <th>p10</th>\n",
       "      <th>p11</th>\n",
       "      <th>p12</th>\n",
       "      <th>_NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>01/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>8540</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000163</td>\n",
       "      <td>-0.002012</td>\n",
       "      <td>0.002307</td>\n",
       "      <td>0.001049</td>\n",
       "      <td>-0.001226</td>\n",
       "      <td>0.000372</td>\n",
       "      <td>0.000095</td>\n",
       "      <td>0.000836</td>\n",
       "      <td>-0.000170</td>\n",
       "      <td>9908.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>02/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>10364</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000491</td>\n",
       "      <td>-0.002070</td>\n",
       "      <td>0.002708</td>\n",
       "      <td>-0.000669</td>\n",
       "      <td>-0.001426</td>\n",
       "      <td>-0.000215</td>\n",
       "      <td>-0.000540</td>\n",
       "      <td>0.000367</td>\n",
       "      <td>-0.000163</td>\n",
       "      <td>6420.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000</td>\n",
       "      <td>03/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>4676</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000582</td>\n",
       "      <td>-0.002125</td>\n",
       "      <td>0.003129</td>\n",
       "      <td>-0.000895</td>\n",
       "      <td>-0.001529</td>\n",
       "      <td>-0.000557</td>\n",
       "      <td>0.000031</td>\n",
       "      <td>-0.000107</td>\n",
       "      <td>-0.000037</td>\n",
       "      <td>4952.240598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000</td>\n",
       "      <td>05/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>6267</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000389</td>\n",
       "      <td>0.002572</td>\n",
       "      <td>-0.000628</td>\n",
       "      <td>-0.000278</td>\n",
       "      <td>-0.001221</td>\n",
       "      <td>0.000540</td>\n",
       "      <td>0.000499</td>\n",
       "      <td>-0.000087</td>\n",
       "      <td>-0.000236</td>\n",
       "      <td>6538.379156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000</td>\n",
       "      <td>06/01/2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>326</td>\n",
       "      <td>7</td>\n",
       "      <td>5953</td>\n",
       "      <td>9643</td>\n",
       "      <td>17130</td>\n",
       "      <td>2770</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.000154</td>\n",
       "      <td>0.000616</td>\n",
       "      <td>0.003896</td>\n",
       "      <td>0.000200</td>\n",
       "      <td>-0.001384</td>\n",
       "      <td>-0.000087</td>\n",
       "      <td>-0.000091</td>\n",
       "      <td>0.000085</td>\n",
       "      <td>0.000273</td>\n",
       "      <td>7872.339310</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 43 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID        Date  IsHoliday  HasPromotions  NearestCompetitor  Region  \\\n",
       "0     1000  01/01/2018          0              0                326       7   \n",
       "1     1000  02/01/2018          0              0                326       7   \n",
       "2     1000  03/01/2018          0              0                326       7   \n",
       "3     1000  05/01/2018          0              0                326       7   \n",
       "4     1000  06/01/2018          0              0                326       7   \n",
       "\n",
       "   NumberOfSales  Region_AreaKM2  Region_GDP  Region_PopulationK  \\\n",
       "0           8540            9643       17130                2770   \n",
       "1          10364            9643       17130                2770   \n",
       "2           4676            9643       17130                2770   \n",
       "3           6267            9643       17130                2770   \n",
       "4           5953            9643       17130                2770   \n",
       "\n",
       "        ...              p4        p5        p6        p7        p8        p9  \\\n",
       "0       ...       -0.000163 -0.002012  0.002307  0.001049 -0.001226  0.000372   \n",
       "1       ...       -0.000491 -0.002070  0.002708 -0.000669 -0.001426 -0.000215   \n",
       "2       ...       -0.000582 -0.002125  0.003129 -0.000895 -0.001529 -0.000557   \n",
       "3       ...       -0.000389  0.002572 -0.000628 -0.000278 -0.001221  0.000540   \n",
       "4       ...       -0.000154  0.000616  0.003896  0.000200 -0.001384 -0.000087   \n",
       "\n",
       "        p10       p11       p12  _NumberOfSales  \n",
       "0  0.000095  0.000836 -0.000170     9908.500000  \n",
       "1 -0.000540  0.000367 -0.000163     6420.166667  \n",
       "2  0.000031 -0.000107 -0.000037     4952.240598  \n",
       "3  0.000499 -0.000087 -0.000236     6538.379156  \n",
       "4 -0.000091  0.000085  0.000273     7872.339310  \n",
       "\n",
       "[5 rows x 43 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./dataset/test_m12_53_RFR_on_prep.csv')\n",
    "df.head()"
   ]
  },
  {
   "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>StoreID</th>\n",
       "      <th>Month</th>\n",
       "      <th>NumberOfSales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "      <td>201451.830901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>2</td>\n",
       "      <td>184721.490504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>1</td>\n",
       "      <td>83185.502132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1001</td>\n",
       "      <td>2</td>\n",
       "      <td>72102.648063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1002</td>\n",
       "      <td>1</td>\n",
       "      <td>134334.706754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   StoreID  Month  NumberOfSales\n",
       "0     1000      1  201451.830901\n",
       "1     1000      2  184721.490504\n",
       "2     1001      1   83185.502132\n",
       "3     1001      2   72102.648063\n",
       "4     1002      1  134334.706754"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = apply_BIP_submission_format(df, real_submit=True)\n",
    "df.head()"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}