rossmann-store-sales.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "%reload_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from fastai.structured import *\n",
    "from fastai.column_data import *\n",
    "np.set_printoptions(threshold=50, edgeitems=20)\n",
    "\n",
    "PATH='data/'\n",
    "\n",
    "def count_nans(df, col):\n",
    "    print(df[df[col].isnull()].count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df\t\t rossmann.tgz\t\t    store.csv\t      test.csv.zip\r\n",
      "googletrend.csv  sample_submission.csv\t    store.csv.zip     train.csv\r\n",
      "joined\t\t sample_submission.csv.zip  store_states.csv  train.csv.zip\r\n",
      "joined_test\t state_names.csv\t    test.csv\t      weather.csv\r\n"
     ]
    }
   ],
   "source": [
    "!ls {PATH}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def concat_csv(dirname): #implementation used to get the above data set\n",
    "    path = f'{PATH}{dirname}'\n",
    "    filenames=glob(f\"{PATH}/*.csv\")\n",
    "    \n",
    "    wrote_header = False\n",
    "    with open(f\"{path}.csv\",\"w\") as outputfile: #??\n",
    "            for filename in filenames:\n",
    "                name = filename.split(\".\")[0]\n",
    "                with open(filename) as f:\n",
    "                    line = f.readline()\n",
    "                    if not wrote_header:\n",
    "                        wrote_header = True\n",
    "                        outputfile.write(\"file,\"+line)\n",
    "                    for line in f:\n",
    "                        outputfile.write(name + \",\" + line)\n",
    "                    outputfile.write(\"\\n\")\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# concat_csvs('googletrend')\n",
    "# concat_csvs('weather')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_names = ['train','store','store_states','state_names',\n",
    "              'googletrend','weather','test']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Feature Space:\n",
    "\n",
    "- train: Training set provided by competition\n",
    "- store: List of stores\n",
    "- store_states: mapping of store to the German state they are in\n",
    "- List of German state names\n",
    "- googletrend: trend of certain google keywords over time, found by users to correlate well w/ given data\n",
    "- weather: weather\n",
    "- test: testing set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [pd.read_csv(f'{PATH}{fname}.csv',low_memory=False) for fname in table_names]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[         Store  DayOfWeek        Date  Sales  Customers  Open  Promo  \\\n",
       " 0            1          5  2015-07-31   5263        555     1      1   \n",
       " 1            2          5  2015-07-31   6064        625     1      1   \n",
       " 2            3          5  2015-07-31   8314        821     1      1   \n",
       " 3            4          5  2015-07-31  13995       1498     1      1   \n",
       " 4            5          5  2015-07-31   4822        559     1      1   \n",
       " 5            6          5  2015-07-31   5651        589     1      1   \n",
       " 6            7          5  2015-07-31  15344       1414     1      1   \n",
       " 7            8          5  2015-07-31   8492        833     1      1   \n",
       " 8            9          5  2015-07-31   8565        687     1      1   \n",
       " 9           10          5  2015-07-31   7185        681     1      1   \n",
       " 10          11          5  2015-07-31  10457       1236     1      1   \n",
       " 11          12          5  2015-07-31   8959        962     1      1   \n",
       " 12          13          5  2015-07-31   8821        568     1      1   \n",
       " 13          14          5  2015-07-31   6544        710     1      1   \n",
       " 14          15          5  2015-07-31   9191        766     1      1   \n",
       " 15          16          5  2015-07-31  10231        979     1      1   \n",
       " 16          17          5  2015-07-31   8430        946     1      1   \n",
       " 17          18          5  2015-07-31  10071        936     1      1   \n",
       " 18          19          5  2015-07-31   8234        718     1      1   \n",
       " 19          20          5  2015-07-31   9593        974     1      1   \n",
       " 20          21          5  2015-07-31   9515        682     1      1   \n",
       " 21          22          5  2015-07-31   6566        633     1      1   \n",
       " 22          23          5  2015-07-31   7273        560     1      1   \n",
       " 23          24          5  2015-07-31  14190       1082     1      1   \n",
       " 24          25          5  2015-07-31  14180       1586     1      1   \n",
       " 25          26          5  2015-07-31   7818        611     1      1   \n",
       " 26          27          5  2015-07-31  13213       1263     1      1   \n",
       " 27          28          5  2015-07-31   7301        663     1      1   \n",
       " 28          29          5  2015-07-31   9411        737     1      1   \n",
       " 29          30          5  2015-07-31   4776        625     1      1   \n",
       " ...        ...        ...         ...    ...        ...   ...    ...   \n",
       " 1017179   1086          2  2013-01-01      0          0     0      0   \n",
       " 1017180   1087          2  2013-01-01      0          0     0      0   \n",
       " 1017181   1088          2  2013-01-01      0          0     0      0   \n",
       " 1017182   1089          2  2013-01-01      0          0     0      0   \n",
       " 1017183   1090          2  2013-01-01      0          0     0      0   \n",
       " 1017184   1091          2  2013-01-01      0          0     0      0   \n",
       " 1017185   1092          2  2013-01-01      0          0     0      0   \n",
       " 1017186   1093          2  2013-01-01      0          0     0      0   \n",
       " 1017187   1094          2  2013-01-01      0          0     0      0   \n",
       " 1017188   1095          2  2013-01-01      0          0     0      0   \n",
       " 1017189   1096          2  2013-01-01      0          0     0      0   \n",
       " 1017190   1097          2  2013-01-01   5961       1405     1      0   \n",
       " 1017191   1098          2  2013-01-01      0          0     0      0   \n",
       " 1017192   1099          2  2013-01-01      0          0     0      0   \n",
       " 1017193   1100          2  2013-01-01      0          0     0      0   \n",
       " 1017194   1101          2  2013-01-01      0          0     0      0   \n",
       " 1017195   1102          2  2013-01-01      0          0     0      0   \n",
       " 1017196   1103          2  2013-01-01      0          0     0      0   \n",
       " 1017197   1104          2  2013-01-01      0          0     0      0   \n",
       " 1017198   1105          2  2013-01-01      0          0     0      0   \n",
       " 1017199   1106          2  2013-01-01      0          0     0      0   \n",
       " 1017200   1107          2  2013-01-01      0          0     0      0   \n",
       " 1017201   1108          2  2013-01-01      0          0     0      0   \n",
       " 1017202   1109          2  2013-01-01      0          0     0      0   \n",
       " 1017203   1110          2  2013-01-01      0          0     0      0   \n",
       " 1017204   1111          2  2013-01-01      0          0     0      0   \n",
       " 1017205   1112          2  2013-01-01      0          0     0      0   \n",
       " 1017206   1113          2  2013-01-01      0          0     0      0   \n",
       " 1017207   1114          2  2013-01-01      0          0     0      0   \n",
       " 1017208   1115          2  2013-01-01      0          0     0      0   \n",
       " \n",
       "         StateHoliday  SchoolHoliday  \n",
       " 0                  0              1  \n",
       " 1                  0              1  \n",
       " 2                  0              1  \n",
       " 3                  0              1  \n",
       " 4                  0              1  \n",
       " 5                  0              1  \n",
       " 6                  0              1  \n",
       " 7                  0              1  \n",
       " 8                  0              1  \n",
       " 9                  0              1  \n",
       " 10                 0              1  \n",
       " 11                 0              1  \n",
       " 12                 0              0  \n",
       " 13                 0              1  \n",
       " 14                 0              1  \n",
       " 15                 0              1  \n",
       " 16                 0              1  \n",
       " 17                 0              1  \n",
       " 18                 0              1  \n",
       " 19                 0              0  \n",
       " 20                 0              1  \n",
       " 21                 0              0  \n",
       " 22                 0              1  \n",
       " 23                 0              1  \n",
       " 24                 0              1  \n",
       " 25                 0              1  \n",
       " 26                 0              1  \n",
       " 27                 0              1  \n",
       " 28                 0              1  \n",
       " 29                 0              1  \n",
       " ...              ...            ...  \n",
       " 1017179            a              1  \n",
       " 1017180            a              1  \n",
       " 1017181            a              1  \n",
       " 1017182            a              1  \n",
       " 1017183            a              1  \n",
       " 1017184            a              1  \n",
       " 1017185            a              1  \n",
       " 1017186            a              1  \n",
       " 1017187            a              1  \n",
       " 1017188            a              1  \n",
       " 1017189            a              1  \n",
       " 1017190            a              1  \n",
       " 1017191            a              1  \n",
       " 1017192            a              1  \n",
       " 1017193            a              1  \n",
       " 1017194            a              1  \n",
       " 1017195            a              1  \n",
       " 1017196            a              1  \n",
       " 1017197            a              1  \n",
       " 1017198            a              1  \n",
       " 1017199            a              1  \n",
       " 1017200            a              1  \n",
       " 1017201            a              1  \n",
       " 1017202            a              1  \n",
       " 1017203            a              1  \n",
       " 1017204            a              1  \n",
       " 1017205            a              1  \n",
       " 1017206            a              1  \n",
       " 1017207            a              1  \n",
       " 1017208            a              1  \n",
       " \n",
       " [1017209 rows x 9 columns],\n",
       "       Store StoreType Assortment  CompetitionDistance  \\\n",
       " 0         1         c          a               1270.0   \n",
       " 1         2         a          a                570.0   \n",
       " 2         3         a          a              14130.0   \n",
       " 3         4         c          c                620.0   \n",
       " 4         5         a          a              29910.0   \n",
       " 5         6         a          a                310.0   \n",
       " 6         7         a          c              24000.0   \n",
       " 7         8         a          a               7520.0   \n",
       " 8         9         a          c               2030.0   \n",
       " 9        10         a          a               3160.0   \n",
       " 10       11         a          c                960.0   \n",
       " 11       12         a          c               1070.0   \n",
       " 12       13         d          a                310.0   \n",
       " 13       14         a          a               1300.0   \n",
       " 14       15         d          c               4110.0   \n",
       " 15       16         a          c               3270.0   \n",
       " 16       17         a          a                 50.0   \n",
       " 17       18         d          c              13840.0   \n",
       " 18       19         a          c               3240.0   \n",
       " 19       20         d          a               2340.0   \n",
       " 20       21         c          c                550.0   \n",
       " 21       22         a          a               1040.0   \n",
       " 22       23         d          a               4060.0   \n",
       " 23       24         a          c               4590.0   \n",
       " 24       25         c          a                430.0   \n",
       " 25       26         d          a               2300.0   \n",
       " 26       27         a          a                 60.0   \n",
       " 27       28         a          a               1200.0   \n",
       " 28       29         d          c               2170.0   \n",
       " 29       30         a          a                 40.0   \n",
       " ...     ...       ...        ...                  ...   \n",
       " 1085   1086         a          a                180.0   \n",
       " 1086   1087         d          c               2210.0   \n",
       " 1087   1088         a          a               4300.0   \n",
       " 1088   1089         d          a               5220.0   \n",
       " 1089   1090         a          a                330.0   \n",
       " 1090   1091         a          c               9990.0   \n",
       " 1091   1092         a          a                300.0   \n",
       " 1092   1093         c          c              10450.0   \n",
       " 1093   1094         d          a               2380.0   \n",
       " 1094   1095         a          a                690.0   \n",
       " 1095   1096         a          c               1130.0   \n",
       " 1096   1097         b          b                720.0   \n",
       " 1097   1098         a          a               1830.0   \n",
       " 1098   1099         a          c                200.0   \n",
       " 1099   1100         a          a                540.0   \n",
       " 1100   1101         d          c               4060.0   \n",
       " 1101   1102         a          a                850.0   \n",
       " 1102   1103         d          c               1340.0   \n",
       " 1103   1104         d          a                260.0   \n",
       " 1104   1105         c          c                330.0   \n",
       " 1105   1106         a          c               5330.0   \n",
       " 1106   1107         a          a               1400.0   \n",
       " 1107   1108         a          a                540.0   \n",
       " 1108   1109         c          a               3490.0   \n",
       " 1109   1110         c          c                900.0   \n",
       " 1110   1111         a          a               1900.0   \n",
       " 1111   1112         c          c               1880.0   \n",
       " 1112   1113         a          c               9260.0   \n",
       " 1113   1114         a          c                870.0   \n",
       " 1114   1115         d          c               5350.0   \n",
       " \n",
       "       CompetitionOpenSinceMonth  CompetitionOpenSinceYear  Promo2  \\\n",
       " 0                           9.0                    2008.0       0   \n",
       " 1                          11.0                    2007.0       1   \n",
       " 2                          12.0                    2006.0       1   \n",
       " 3                           9.0                    2009.0       0   \n",
       " 4                           4.0                    2015.0       0   \n",
       " 5                          12.0                    2013.0       0   \n",
       " 6                           4.0                    2013.0       0   \n",
       " 7                          10.0                    2014.0       0   \n",
       " 8                           8.0                    2000.0       0   \n",
       " 9                           9.0                    2009.0       0   \n",
       " 10                         11.0                    2011.0       1   \n",
       " 11                          NaN                       NaN       1   \n",
       " 12                          NaN                       NaN       1   \n",
       " 13                          3.0                    2014.0       1   \n",
       " 14                          3.0                    2010.0       1   \n",
       " 15                          NaN                       NaN       0   \n",
       " 16                         12.0                    2005.0       1   \n",
       " 17                          6.0                    2010.0       1   \n",
       " 18                          NaN                       NaN       1   \n",
       " 19                          5.0                    2009.0       1   \n",
       " 20                         10.0                    1999.0       1   \n",
       " 21                          NaN                       NaN       1   \n",
       " 22                          8.0                    2005.0       0   \n",
       " 23                          3.0                    2000.0       1   \n",
       " 24                          4.0                    2003.0       0   \n",
       " 25                          NaN                       NaN       0   \n",
       " 26                          1.0                    2005.0       1   \n",
       " 27                         10.0                    2014.0       1   \n",
       " 28                          NaN                       NaN       0   \n",
       " 29                          2.0                    2014.0       1   \n",
       " ...                         ...                       ...     ...   \n",
       " 1085                       11.0                    2013.0       1   \n",
       " 1086                       11.0                    2011.0       0   \n",
       " 1087                        3.0                    2009.0       1   \n",
       " 1088                        5.0                    2009.0       0   \n",
       " 1089                        NaN                       NaN       1   \n",
       " 1090                        NaN                       NaN       0   \n",
       " 1091                        7.0                    2000.0       1   \n",
       " 1092                        6.0                    2009.0       0   \n",
       " 1093                        3.0                    2013.0       1   \n",
       " 1094                        6.0                    2007.0       1   \n",
       " 1095                        NaN                       NaN       1   \n",
       " 1096                        3.0                    2002.0       0   \n",
       " 1097                       11.0                    2004.0       0   \n",
       " 1098                        4.0                    2013.0       1   \n",
       " 1099                        NaN                       NaN       1   \n",
       " 1100                        9.0                    2012.0       0   \n",
       " 1101                       11.0                    2012.0       1   \n",
       " 1102                       10.0                    2006.0       1   \n",
       " 1103                        2.0                    2012.0       1   \n",
       " 1104                       11.0                    2008.0       1   \n",
       " 1105                        9.0                    2011.0       1   \n",
       " 1106                        6.0                    2012.0       1   \n",
       " 1107                        4.0                    2004.0       0   \n",
       " 1108                        4.0                    2011.0       1   \n",
       " 1109                        9.0                    2010.0       0   \n",
       " 1110                        6.0                    2014.0       1   \n",
       " 1111                        4.0                    2006.0       0   \n",
       " 1112                        NaN                       NaN       0   \n",
       " 1113                        NaN                       NaN       0   \n",
       " 1114                        NaN                       NaN       1   \n",
       " \n",
       "       Promo2SinceWeek  Promo2SinceYear     PromoInterval  \n",
       " 0                 NaN              NaN               NaN  \n",
       " 1                13.0           2010.0   Jan,Apr,Jul,Oct  \n",
       " 2                14.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 3                 NaN              NaN               NaN  \n",
       " 4                 NaN              NaN               NaN  \n",
       " 5                 NaN              NaN               NaN  \n",
       " 6                 NaN              NaN               NaN  \n",
       " 7                 NaN              NaN               NaN  \n",
       " 8                 NaN              NaN               NaN  \n",
       " 9                 NaN              NaN               NaN  \n",
       " 10                1.0           2012.0   Jan,Apr,Jul,Oct  \n",
       " 11               13.0           2010.0   Jan,Apr,Jul,Oct  \n",
       " 12               45.0           2009.0   Feb,May,Aug,Nov  \n",
       " 13               40.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 14               14.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 15                NaN              NaN               NaN  \n",
       " 16               26.0           2010.0   Jan,Apr,Jul,Oct  \n",
       " 17               14.0           2012.0   Jan,Apr,Jul,Oct  \n",
       " 18               22.0           2011.0  Mar,Jun,Sept,Dec  \n",
       " 19               40.0           2014.0   Jan,Apr,Jul,Oct  \n",
       " 20               45.0           2009.0   Jan,Apr,Jul,Oct  \n",
       " 21               22.0           2012.0   Jan,Apr,Jul,Oct  \n",
       " 22                NaN              NaN               NaN  \n",
       " 23               40.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 24                NaN              NaN               NaN  \n",
       " 25                NaN              NaN               NaN  \n",
       " 26                5.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 27                6.0           2015.0  Mar,Jun,Sept,Dec  \n",
       " 28                NaN              NaN               NaN  \n",
       " 29               10.0           2014.0  Mar,Jun,Sept,Dec  \n",
       " ...               ...              ...               ...  \n",
       " 1085             18.0           2011.0   Feb,May,Aug,Nov  \n",
       " 1086              NaN              NaN               NaN  \n",
       " 1087             27.0           2013.0   Jan,Apr,Jul,Oct  \n",
       " 1088              NaN              NaN               NaN  \n",
       " 1089             14.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 1090              NaN              NaN               NaN  \n",
       " 1091             40.0           2014.0   Jan,Apr,Jul,Oct  \n",
       " 1092              NaN              NaN               NaN  \n",
       " 1093             40.0           2014.0   Jan,Apr,Jul,Oct  \n",
       " 1094             14.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 1095             10.0           2014.0  Mar,Jun,Sept,Dec  \n",
       " 1096              NaN              NaN               NaN  \n",
       " 1097              NaN              NaN               NaN  \n",
       " 1098             14.0           2013.0   Jan,Apr,Jul,Oct  \n",
       " 1099             14.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 1100              NaN              NaN               NaN  \n",
       " 1101             40.0           2014.0   Jan,Apr,Jul,Oct  \n",
       " 1102              5.0           2013.0   Feb,May,Aug,Nov  \n",
       " 1103             14.0           2011.0   Jan,Apr,Jul,Oct  \n",
       " 1104              5.0           2013.0   Feb,May,Aug,Nov  \n",
       " 1105             31.0           2013.0   Jan,Apr,Jul,Oct  \n",
       " 1106             13.0           2010.0   Jan,Apr,Jul,Oct  \n",
       " 1107              NaN              NaN               NaN  \n",
       " 1108             22.0           2012.0   Jan,Apr,Jul,Oct  \n",
       " 1109              NaN              NaN               NaN  \n",
       " 1110             31.0           2013.0   Jan,Apr,Jul,Oct  \n",
       " 1111              NaN              NaN               NaN  \n",
       " 1112              NaN              NaN               NaN  \n",
       " 1113              NaN              NaN               NaN  \n",
       " 1114             22.0           2012.0  Mar,Jun,Sept,Dec  \n",
       " \n",
       " [1115 rows x 10 columns],\n",
       "       Store  State\n",
       " 0         1     HE\n",
       " 1         2     TH\n",
       " 2         3     NW\n",
       " 3         4     BE\n",
       " 4         5     SN\n",
       " 5         6     SN\n",
       " 6         7     SH\n",
       " 7         8     SH\n",
       " 8         9     NW\n",
       " 9        10  HB,NI\n",
       " 10       11     SH\n",
       " 11       12     SH\n",
       " 12       13     BY\n",
       " 13       14     NW\n",
       " 14       15     SH\n",
       " 15       16     BW\n",
       " 16       17     TH\n",
       " 17       18     TH\n",
       " 18       19     NW\n",
       " 19       20     BY\n",
       " 20       21     BW\n",
       " 21       22     BY\n",
       " 22       23     RP\n",
       " 23       24     NW\n",
       " 24       25     NW\n",
       " 25       26     TH\n",
       " 26       27     NW\n",
       " 27       28     ST\n",
       " 28       29     NW\n",
       " 29       30     NW\n",
       " ...     ...    ...\n",
       " 1085   1086     HE\n",
       " 1086   1087  HB,NI\n",
       " 1087   1088     NW\n",
       " 1088   1089     NW\n",
       " 1089   1090     SN\n",
       " 1090   1091     SH\n",
       " 1091   1092     BY\n",
       " 1092   1093     SN\n",
       " 1093   1094     BY\n",
       " 1094   1095     TH\n",
       " 1095   1096     NW\n",
       " 1096   1097     RP\n",
       " 1097   1098     SN\n",
       " 1098   1099     SH\n",
       " 1099   1100     NW\n",
       " 1100   1101     SH\n",
       " 1101   1102     BY\n",
       " 1102   1103     HE\n",
       " 1103   1104     BY\n",
       " 1104   1105     NW\n",
       " 1105   1106     SH\n",
       " 1106   1107     BY\n",
       " 1107   1108     SN\n",
       " 1108   1109     BY\n",
       " 1109   1110     BE\n",
       " 1110   1111     NW\n",
       " 1111   1112     NW\n",
       " 1112   1113     SH\n",
       " 1113   1114     HH\n",
       " 1114   1115     HE\n",
       " \n",
       " [1115 rows x 2 columns],\n",
       "                 StateName  State\n",
       " 0       BadenWuerttemberg     BW\n",
       " 1                  Bayern     BY\n",
       " 2                  Berlin     BE\n",
       " 3             Brandenburg     BB\n",
       " 4                  Bremen     HB\n",
       " 5                 Hamburg     HH\n",
       " 6                  Hessen     HE\n",
       " 7   MecklenburgVorpommern     MV\n",
       " 8           Niedersachsen  HB,NI\n",
       " 9      NordrheinWestfalen     NW\n",
       " 10         RheinlandPfalz     RP\n",
       " 11               Saarland     SL\n",
       " 12                Sachsen     SN\n",
       " 13          SachsenAnhalt     ST\n",
       " 14      SchleswigHolstein     SH\n",
       " 15             Thueringen     TH,\n",
       "                 file                     week  trend\n",
       " 0     Rossmann_DE_SN  2012-12-02 - 2012-12-08     96\n",
       " 1     Rossmann_DE_SN  2012-12-09 - 2012-12-15     95\n",
       " 2     Rossmann_DE_SN  2012-12-16 - 2012-12-22     91\n",
       " 3     Rossmann_DE_SN  2012-12-23 - 2012-12-29     48\n",
       " 4     Rossmann_DE_SN  2012-12-30 - 2013-01-05     67\n",
       " 5     Rossmann_DE_SN  2013-01-06 - 2013-01-12     71\n",
       " 6     Rossmann_DE_SN  2013-01-13 - 2013-01-19     53\n",
       " 7     Rossmann_DE_SN  2013-01-20 - 2013-01-26     58\n",
       " 8     Rossmann_DE_SN  2013-01-27 - 2013-02-02     55\n",
       " 9     Rossmann_DE_SN  2013-02-03 - 2013-02-09     55\n",
       " 10    Rossmann_DE_SN  2013-02-10 - 2013-02-16     58\n",
       " 11    Rossmann_DE_SN  2013-02-17 - 2013-02-23     65\n",
       " 12    Rossmann_DE_SN  2013-02-24 - 2013-03-02     48\n",
       " 13    Rossmann_DE_SN  2013-03-03 - 2013-03-09     51\n",
       " 14    Rossmann_DE_SN  2013-03-10 - 2013-03-16     50\n",
       " 15    Rossmann_DE_SN  2013-03-17 - 2013-03-23     58\n",
       " 16    Rossmann_DE_SN  2013-03-24 - 2013-03-30     54\n",
       " 17    Rossmann_DE_SN  2013-03-31 - 2013-04-06     55\n",
       " 18    Rossmann_DE_SN  2013-04-07 - 2013-04-13     64\n",
       " 19    Rossmann_DE_SN  2013-04-14 - 2013-04-20     47\n",
       " 20    Rossmann_DE_SN  2013-04-21 - 2013-04-27     51\n",
       " 21    Rossmann_DE_SN  2013-04-28 - 2013-05-04     47\n",
       " 22    Rossmann_DE_SN  2013-05-05 - 2013-05-11     57\n",
       " 23    Rossmann_DE_SN  2013-05-12 - 2013-05-18     64\n",
       " 24    Rossmann_DE_SN  2013-05-19 - 2013-05-25     54\n",
       " 25    Rossmann_DE_SN  2013-05-26 - 2013-06-01     67\n",
       " 26    Rossmann_DE_SN  2013-06-02 - 2013-06-08     45\n",
       " 27    Rossmann_DE_SN  2013-06-09 - 2013-06-15     55\n",
       " 28    Rossmann_DE_SN  2013-06-16 - 2013-06-22     67\n",
       " 29    Rossmann_DE_SN  2013-06-23 - 2013-06-29     56\n",
       " ...              ...                      ...    ...\n",
       " 2042  Rossmann_DE_SL  2015-03-08 - 2015-03-14     60\n",
       " 2043  Rossmann_DE_SL  2015-03-15 - 2015-03-21     61\n",
       " 2044  Rossmann_DE_SL  2015-03-22 - 2015-03-28     80\n",
       " 2045  Rossmann_DE_SL  2015-03-29 - 2015-04-04     61\n",
       " 2046  Rossmann_DE_SL  2015-04-05 - 2015-04-11     67\n",
       " 2047  Rossmann_DE_SL  2015-04-12 - 2015-04-18     58\n",
       " 2048  Rossmann_DE_SL  2015-04-19 - 2015-04-25     67\n",
       " 2049  Rossmann_DE_SL  2015-04-26 - 2015-05-02     86\n",
       " 2050  Rossmann_DE_SL  2015-05-03 - 2015-05-09     70\n",
       " 2051  Rossmann_DE_SL  2015-05-10 - 2015-05-16     77\n",
       " 2052  Rossmann_DE_SL  2015-05-17 - 2015-05-23     77\n",
       " 2053  Rossmann_DE_SL  2015-05-24 - 2015-05-30     78\n",
       " 2054  Rossmann_DE_SL  2015-05-31 - 2015-06-06     95\n",
       " 2055  Rossmann_DE_SL  2015-06-07 - 2015-06-13     90\n",
       " 2056  Rossmann_DE_SL  2015-06-14 - 2015-06-20    100\n",
       " 2057  Rossmann_DE_SL  2015-06-21 - 2015-06-27     70\n",
       " 2058  Rossmann_DE_SL  2015-06-28 - 2015-07-04     83\n",
       " 2059  Rossmann_DE_SL  2015-07-05 - 2015-07-11     53\n",
       " 2060  Rossmann_DE_SL  2015-07-12 - 2015-07-18     53\n",
       " 2061  Rossmann_DE_SL  2015-07-19 - 2015-07-25     50\n",
       " 2062  Rossmann_DE_SL  2015-07-26 - 2015-08-01     69\n",
       " 2063  Rossmann_DE_SL  2015-08-02 - 2015-08-08     88\n",
       " 2064  Rossmann_DE_SL  2015-08-09 - 2015-08-15     68\n",
       " 2065  Rossmann_DE_SL  2015-08-16 - 2015-08-22     81\n",
       " 2066  Rossmann_DE_SL  2015-08-23 - 2015-08-29     48\n",
       " 2067  Rossmann_DE_SL  2015-08-30 - 2015-09-05     95\n",
       " 2068  Rossmann_DE_SL  2015-09-06 - 2015-09-12     47\n",
       " 2069  Rossmann_DE_SL  2015-09-13 - 2015-09-19     80\n",
       " 2070  Rossmann_DE_SL  2015-09-20 - 2015-09-26     57\n",
       " 2071  Rossmann_DE_SL  2015-09-27 - 2015-10-03      0\n",
       " \n",
       " [2072 rows x 3 columns],\n",
       "                      file        Date  Max_TemperatureC  Mean_TemperatureC  \\\n",
       " 0      NordrheinWestfalen  2013-01-01                 8                  4   \n",
       " 1      NordrheinWestfalen  2013-01-02                 7                  4   \n",
       " 2      NordrheinWestfalen  2013-01-03                11                  8   \n",
       " 3      NordrheinWestfalen  2013-01-04                 9                  9   \n",
       " 4      NordrheinWestfalen  2013-01-05                 8                  8   \n",
       " 5      NordrheinWestfalen  2013-01-06                 9                  8   \n",
       " 6      NordrheinWestfalen  2013-01-07                 9                  8   \n",
       " 7      NordrheinWestfalen  2013-01-08                 8                  7   \n",
       " 8      NordrheinWestfalen  2013-01-09                 6                  6   \n",
       " 9      NordrheinWestfalen  2013-01-10                 6                  4   \n",
       " 10     NordrheinWestfalen  2013-01-11                 2                 -1   \n",
       " 11     NordrheinWestfalen  2013-01-12                 2                 -2   \n",
       " 12     NordrheinWestfalen  2013-01-13                 1                 -3   \n",
       " 13     NordrheinWestfalen  2013-01-14                -2                 -3   \n",
       " 14     NordrheinWestfalen  2013-01-15                -1                 -4   \n",
       " 15     NordrheinWestfalen  2013-01-16                -3                 -3   \n",
       " 16     NordrheinWestfalen  2013-01-17                -2                 -3   \n",
       " 17     NordrheinWestfalen  2013-01-18                 0                 -2   \n",
       " 18     NordrheinWestfalen  2013-01-19                -3                 -4   \n",
       " 19     NordrheinWestfalen  2013-01-20                 0                 -3   \n",
       " 20     NordrheinWestfalen  2013-01-21                 0                 -1   \n",
       " 21     NordrheinWestfalen  2013-01-22                 1                 -1   \n",
       " 22     NordrheinWestfalen  2013-01-23                -1                 -2   \n",
       " 23     NordrheinWestfalen  2013-01-24                -2                 -4   \n",
       " 24     NordrheinWestfalen  2013-01-25                -2                 -2   \n",
       " 25     NordrheinWestfalen  2013-01-26                 0                 -2   \n",
       " 26     NordrheinWestfalen  2013-01-27                 4                  2   \n",
       " 27     NordrheinWestfalen  2013-01-28                 6                  3   \n",
       " 28     NordrheinWestfalen  2013-01-29                12                  8   \n",
       " 29     NordrheinWestfalen  2013-01-30                13                 11   \n",
       " ...                   ...         ...               ...                ...   \n",
       " 15810            Saarland  2015-08-19                22                 14   \n",
       " 15811            Saarland  2015-08-20                25                 18   \n",
       " 15812            Saarland  2015-08-21                26                 19   \n",
       " 15813            Saarland  2015-08-22                26                 19   \n",
       " 15814            Saarland  2015-08-23                27                 20   \n",
       " 15815            Saarland  2015-08-24                19                 17   \n",
       " 15816            Saarland  2015-08-25                21                 17   \n",
       " 15817            Saarland  2015-08-26                26                 19   \n",
       " 15818            Saarland  2015-08-27                29                 23   \n",
       " 15819            Saarland  2015-08-28                19                 18   \n",
       " 15820            Saarland  2015-08-29                30                 22   \n",
       " 15821            Saarland  2015-08-30                32                 25   \n",
       " 15822            Saarland  2015-08-31                31                 25   \n",
       " 15823            Saarland  2015-09-01                21                 18   \n",
       " 15824            Saarland  2015-09-02                20                 15   \n",
       " 15825            Saarland  2015-09-03                20                 15   \n",
       " 15826            Saarland  2015-09-04                18                 12   \n",
       " 15827            Saarland  2015-09-05                16                 10   \n",
       " 15828            Saarland  2015-09-06                16                 11   \n",
       " 15829            Saarland  2015-09-07                16                 10   \n",
       " 15830            Saarland  2015-09-08                19                 14   \n",
       " 15831            Saarland  2015-09-09                20                 14   \n",
       " 15832            Saarland  2015-09-10                20                 14   \n",
       " 15833            Saarland  2015-09-11                21                 14   \n",
       " 15834            Saarland  2015-09-12                23                 17   \n",
       " 15835            Saarland  2015-09-13                21                 17   \n",
       " 15836            Saarland  2015-09-14                18                 14   \n",
       " 15837            Saarland  2015-09-15                16                 12   \n",
       " 15838            Saarland  2015-09-16                19                 15   \n",
       " 15839            Saarland  2015-09-17                14                 13   \n",
       " \n",
       "        Min_TemperatureC  Dew_PointC  MeanDew_PointC  Min_DewpointC  \\\n",
       " 0                     2           7               5              1   \n",
       " 1                     1           5               3              2   \n",
       " 2                     6          10               8              4   \n",
       " 3                     8           9               9              8   \n",
       " 4                     7           8               7              6   \n",
       " 5                     7           8               7              6   \n",
       " 6                     7           8               7              5   \n",
       " 7                     5           6               5              4   \n",
       " 8                     5           6               4              3   \n",
       " 9                     3           4               3              1   \n",
       " 10                   -3           2              -3             -5   \n",
       " 11                   -7          -3              -6             -8   \n",
       " 12                   -6          -3              -6             -8   \n",
       " 13                   -5          -2              -5             -8   \n",
       " 14                   -7          -2              -4             -7   \n",
       " 15                   -4          -3              -4             -5   \n",
       " 16                   -4          -3              -4             -6   \n",
       " 17                   -5          -3              -4             -7   \n",
       " 18                   -5          -5              -7             -9   \n",
       " 19                   -7          -2              -6             -9   \n",
       " 20                   -2          -1              -2             -3   \n",
       " 21                   -3          -2              -3             -4   \n",
       " 22                   -3          -3              -4             -6   \n",
       " 23                   -6          -4              -6             -8   \n",
       " 24                   -3          -3              -4             -6   \n",
       " 25                   -4          -1              -4             -6   \n",
       " 26                   -1           3               1             -2   \n",
       " 27                    0           3               2             -1   \n",
       " 28                    3          10               8              3   \n",
       " 29                    9          11               8              4   \n",
       " ...                 ...         ...             ...            ...   \n",
       " 15810                 8          12               8              6   \n",
       " 15811                11          13               9              5   \n",
       " 15812                12          12              10              7   \n",
       " 15813                13          12               8              5   \n",
       " 15814                13          17              11              6   \n",
       " 15815                15          17              14              9   \n",
       " 15816                12          10               8              5   \n",
       " 15817                12          13               9              7   \n",
       " 15818                18          17              13             10   \n",
       " 15819                16          18              17             15   \n",
       " 15820                15          20              17             15   \n",
       " 15821                18          19              16             12   \n",
       " 15822                19          14              12              9   \n",
       " 15823                14          18              15             12   \n",
       " 15824                10          13              11              7   \n",
       " 15825                10          13               9              5   \n",
       " 15826                 7           9               7              5   \n",
       " 15827                 5           9               7              5   \n",
       " 15828                 7           9               6              4   \n",
       " 15829                 5          11               8              4   \n",
       " 15830                 9          11               9              7   \n",
       " 15831                 9          11               7              5   \n",
       " 15832                 8           7               4              2   \n",
       " 15833                 7           8               6              2   \n",
       " 15834                10          16              11              5   \n",
       " 15835                12          16              14             12   \n",
       " 15836                11          15              12              7   \n",
       " 15837                 9          11               8              7   \n",
       " 15838                11          16              13             10   \n",
       " 15839                12          14              12             10   \n",
       " \n",
       "        Max_Humidity  Mean_Humidity       ...        Max_VisibilityKm  \\\n",
       " 0                94             87       ...                    31.0   \n",
       " 1                93             85       ...                    31.0   \n",
       " 2               100             93       ...                    31.0   \n",
       " 3               100             94       ...                    11.0   \n",
       " 4               100             94       ...                    10.0   \n",
       " 5               100             92       ...                    18.0   \n",
       " 6                98             88       ...                    26.0   \n",
       " 7               100             87       ...                    18.0   \n",
       " 8               100             91       ...                    19.0   \n",
       " 9                96             86       ...                    26.0   \n",
       " 10              100             85       ...                    31.0   \n",
       " 11              100             79       ...                    31.0   \n",
       " 12               86             75       ...                    31.0   \n",
       " 13               99             85       ...                    18.0   \n",
       " 14              100             86       ...                    26.0   \n",
       " 15               99             91       ...                     5.0   \n",
       " 16               94             88       ...                     7.0   \n",
       " 17               94             82       ...                    31.0   \n",
       " 18               80             74       ...                    26.0   \n",
       " 19               94             84       ...                    19.0   \n",
       " 20              100             91       ...                     7.0   \n",
       " 21               94             86       ...                     7.0   \n",
       " 22               90             80       ...                     7.0   \n",
       " 23               93             82       ...                    14.0   \n",
       " 24               95             83       ...                    26.0   \n",
       " 25              100             81       ...                    26.0   \n",
       " 26               95             86       ...                    26.0   \n",
       " 27               93             84       ...                    31.0   \n",
       " 28               96             88       ...                    31.0   \n",
       " 29               94             79       ...                    31.0   \n",
       " ...             ...            ...       ...                     ...   \n",
       " 15810           100             66       ...                    19.0   \n",
       " 15811            94             58       ...                    31.0   \n",
       " 15812            82             57       ...                    31.0   \n",
       " 15813            72             49       ...                    31.0   \n",
       " 15814            97             61       ...                    14.0   \n",
       " 15815            98             83       ...                    31.0   \n",
       " 15816            77             57       ...                    31.0   \n",
       " 15817            72             51       ...                    10.0   \n",
       " 15818            94             55       ...                    26.0   \n",
       " 15819           100             93       ...                    31.0   \n",
       " 15820           100             74       ...                    31.0   \n",
       " 15821            96             58       ...                    31.0   \n",
       " 15822            60             40       ...                     NaN   \n",
       " 15823            99             83       ...                    26.0   \n",
       " 15824           100             79       ...                    31.0   \n",
       " 15825            94             68       ...                    31.0   \n",
       " 15826            96             71       ...                    31.0   \n",
       " 15827           100             76       ...                    31.0   \n",
       " 15828            97             74       ...                    31.0   \n",
       " 15829            99             82       ...                    31.0   \n",
       " 15830            95             72       ...                    31.0   \n",
       " 15831            93             65       ...                    31.0   \n",
       " 15832            76             53       ...                    31.0   \n",
       " 15833            87             58       ...                    31.0   \n",
       " 15834            94             69       ...                    31.0   \n",
       " 15835           100             88       ...                    31.0   \n",
       " 15836            99             85       ...                    31.0   \n",
       " 15837            93             77       ...                    31.0   \n",
       " 15838            97             90       ...                    31.0   \n",
       " 15839            99             92       ...                    31.0   \n",
       " \n",
       "        Mean_VisibilityKm  Min_VisibilitykM  Max_Wind_SpeedKm_h  \\\n",
       " 0                   12.0               4.0                  39   \n",
       " 1                   14.0              10.0                  24   \n",
       " 2                    8.0               2.0                  26   \n",
       " 3                    5.0               2.0                  23   \n",
       " 4                    6.0               3.0                  16   \n",
       " 5                    7.0               5.0                  16   \n",
       " 6                    8.0               2.0                  16   \n",
       " 7                    9.0               3.0                  21   \n",
       " 8                    8.0               2.0                  21   \n",
       " 9                   12.0               6.0                  16   \n",
       " 10                  12.0               9.0                  21   \n",
       " 11                  17.0              10.0                  16   \n",
       " 12                  13.0              10.0                  21   \n",
       " 13                   8.0               2.0                  23   \n",
       " 14                   7.0               1.0                  27   \n",
       " 15                   3.0               2.0                  14   \n",
       " 16                   4.0               3.0                  16   \n",
       " 17                  11.0               8.0                  21   \n",
       " 18                  13.0              10.0                  26   \n",
       " 19                   8.0               1.0                  24   \n",
       " 20                   5.0               2.0                  16   \n",
       " 21                   6.0               5.0                  11   \n",
       " 22                   5.0               5.0                  14   \n",
       " 23                  10.0               6.0                  21   \n",
       " 24                   9.0               1.0                  23   \n",
       " 25                  11.0               3.0                  34   \n",
       " 26                  12.0               5.0                  35   \n",
       " 27                  15.0              10.0                  32   \n",
       " 28                  12.0               6.0                  45   \n",
       " 29                  12.0               5.0                  47   \n",
       " ...                  ...               ...                 ...   \n",
       " 15810               10.0               3.0                  14   \n",
       " 15811               24.0              10.0                  10   \n",
       " 15812               31.0              31.0                  14   \n",
       " 15813               31.0              31.0                  21   \n",
       " 15814               10.0               4.0                  37   \n",
       " 15815               12.0               3.0                  32   \n",
       " 15816               11.0              10.0                  40   \n",
       " 15817               10.0              10.0                  21   \n",
       " 15818               11.0              10.0                  29   \n",
       " 15819                8.0               2.0                  18   \n",
       " 15820                8.0               0.0                  13   \n",
       " 15821               28.0              26.0                  19   \n",
       " 15822                NaN               NaN                  34   \n",
       " 15823               11.0               1.0                  34   \n",
       " 15824                5.0               0.0                  14   \n",
       " 15825               16.0              10.0                  23   \n",
       " 15826               12.0              10.0                  26   \n",
       " 15827               12.0               6.0                  24   \n",
       " 15828               13.0              10.0                  19   \n",
       " 15829               14.0               7.0                  14   \n",
       " 15830               14.0               8.0                  18   \n",
       " 15831               18.0              10.0                  23   \n",
       " 15832               24.0              10.0                  26   \n",
       " 15833               20.0              10.0                  21   \n",
       " 15834               13.0              10.0                  21   \n",
       " 15835               15.0              10.0                  27   \n",
       " 15836               13.0               4.0                  32   \n",
       " 15837               12.0              10.0                  34   \n",
       " 15838               10.0               4.0                  32   \n",
       " 15839               14.0               8.0                  27   \n",
       " \n",
       "        Mean_Wind_SpeedKm_h  Max_Gust_SpeedKm_h  Precipitationmm  CloudCover  \\\n",
       " 0                       26                58.0             5.08         6.0   \n",
       " 1                       16                 NaN             0.00         6.0   \n",
       " 2                       21                 NaN             1.02         7.0   \n",
       " 3                       14                 NaN             0.25         7.0   \n",
       " 4                       10                 NaN             0.00         7.0   \n",
       " 5                       11                 NaN             0.00         7.0   \n",
       " 6                       10                 NaN             0.00         7.0   \n",
       " 7                       13                 NaN             0.00         7.0   \n",
       " 8                       14                 NaN             4.06         8.0   \n",
       " 9                       11                 NaN             0.00         6.0   \n",
       " 10                       8                 NaN             0.00         4.0   \n",
       " 11                       6                 NaN             0.00         2.0   \n",
       " 12                      11                 NaN             0.00         4.0   \n",
       " 13                       8                 NaN             0.00         6.0   \n",
       " 14                      18                 NaN             0.76         7.0   \n",
       " 15                      11                 NaN             0.25         7.0   \n",
       " 16                      10                 NaN             0.00         7.0   \n",
       " 17                      11                 NaN             0.00         6.0   \n",
       " 18                      18                 NaN             0.00         7.0   \n",
       " 19                      19                 NaN             2.03         7.0   \n",
       " 20                      11                 NaN             0.00         7.0   \n",
       " 21                       6                 NaN             0.00         7.0   \n",
       " 22                       8                 NaN             0.00         6.0   \n",
       " 23                      13                 NaN             0.00         6.0   \n",
       " 24                       8                40.0             0.00         7.0   \n",
       " 25                      24                48.0             0.00         7.0   \n",
       " 26                      21                53.0             3.05         7.0   \n",
       " 27                      18                 NaN             0.25         6.0   \n",
       " 28                      31                61.0             6.10         7.0   \n",
       " 29                      32                60.0             3.05         5.0   \n",
       " ...                    ...                 ...              ...         ...   \n",
       " 15810                    5                 NaN             0.00         3.0   \n",
       " 15811                    5                 NaN             0.00         1.0   \n",
       " 15812                   10                 NaN             0.00         NaN   \n",
       " 15813                   13                 NaN             0.00         NaN   \n",
       " 15814                   14                55.0             7.11         6.0   \n",
       " 15815                   13                48.0             7.11         6.0   \n",
       " 15816                   27                64.0             0.00         5.0   \n",
       " 15817                   13                 NaN             0.00         5.0   \n",
       " 15818                   14                39.0             0.00         7.0   \n",
       " 15819                   11                 NaN             5.08         7.0   \n",
       " 15820                    8                 NaN             0.00         5.0   \n",
       " 15821                   10                 NaN             0.00         NaN   \n",
       " 15822                   13                50.0             0.00         NaN   \n",
       " 15823                    8                 NaN             5.08         5.0   \n",
       " 15824                    5                 NaN             0.00         5.0   \n",
       " 15825                    8                 NaN             0.00         4.0   \n",
       " 15826                    8                 NaN             0.00         4.0   \n",
       " 15827                   10                 NaN             0.25         6.0   \n",
       " 15828                   13                 NaN             0.00         5.0   \n",
       " 15829                    6                 NaN             0.25         6.0   \n",
       " 15830                    8                 NaN             0.00         4.0   \n",
       " 15831                   16                 NaN             0.00         2.0   \n",
       " 15832                   18                42.0             0.00         1.0   \n",
       " 15833                   13                 NaN             0.00         6.0   \n",
       " 15834                   11                 NaN             1.02         6.0   \n",
       " 15835                   10                50.0             1.02         7.0   \n",
       " 15836                   16                53.0             9.91         5.0   \n",
       " 15837                   14                 NaN             0.00         5.0   \n",
       " 15838                   14                45.0            20.07         7.0   \n",
       " 15839                   16                47.0             6.10         6.0   \n",
       " \n",
       "                   Events  WindDirDegrees  \n",
       " 0                   Rain             215  \n",
       " 1                   Rain             225  \n",
       " 2                   Rain             240  \n",
       " 3                   Rain             263  \n",
       " 4                   Rain             268  \n",
       " 5                   Rain             258  \n",
       " 6                   Rain             238  \n",
       " 7                   Rain             230  \n",
       " 8                   Rain             222  \n",
       " 9                   Rain             256  \n",
       " 10         Fog-Rain-Snow               1  \n",
       " 11                   NaN             106  \n",
       " 12                   NaN              47  \n",
       " 13                  Snow             273  \n",
       " 14                  Snow              88  \n",
       " 15                  Snow             348  \n",
       " 16             Rain-Snow             353  \n",
       " 17              Fog-Snow              64  \n",
       " 18                   NaN              61  \n",
       " 19             Rain-Snow              55  \n",
       " 20                  Snow             118  \n",
       " 21                  Snow              11  \n",
       " 22                  Snow              17  \n",
       " 23                   NaN              21  \n",
       " 24         Fog-Rain-Snow              80  \n",
       " 25             Rain-Snow             158  \n",
       " 26             Rain-Snow             174  \n",
       " 27                  Rain             190  \n",
       " 28                  Rain             216  \n",
       " 29     Rain-Thunderstorm             233  \n",
       " ...                  ...             ...  \n",
       " 15810                Fog              81  \n",
       " 15811                NaN              48  \n",
       " 15812                NaN              72  \n",
       " 15813                NaN              75  \n",
       " 15814               Rain             124  \n",
       " 15815               Rain             177  \n",
       " 15816               Rain             231  \n",
       " 15817                NaN             186  \n",
       " 15818               Rain             224  \n",
       " 15819               Rain             266  \n",
       " 15820                Fog              88  \n",
       " 15821                NaN             183  \n",
       " 15822                NaN             222  \n",
       " 15823               Rain             281  \n",
       " 15824           Fog-Rain             305  \n",
       " 15825                NaN             272  \n",
       " 15826                NaN             269  \n",
       " 15827           Fog-Rain             278  \n",
       " 15828               Rain             299  \n",
       " 15829               Rain             291  \n",
       " 15830                NaN              49  \n",
       " 15831                NaN              61  \n",
       " 15832                NaN              62  \n",
       " 15833                NaN              71  \n",
       " 15834               Rain             185  \n",
       " 15835               Rain             113  \n",
       " 15836  Rain-Thunderstorm             213  \n",
       " 15837               Rain             193  \n",
       " 15838  Rain-Thunderstorm             147  \n",
       " 15839               Rain             202  \n",
       " \n",
       " [15840 rows x 24 columns],\n",
       "           Id  Store  DayOfWeek        Date  Open  Promo StateHoliday  \\\n",
       " 0          1      1          4  2015-09-17   1.0      1            0   \n",
       " 1          2      3          4  2015-09-17   1.0      1            0   \n",
       " 2          3      7          4  2015-09-17   1.0      1            0   \n",
       " 3          4      8          4  2015-09-17   1.0      1            0   \n",
       " 4          5      9          4  2015-09-17   1.0      1            0   \n",
       " 5          6     10          4  2015-09-17   1.0      1            0   \n",
       " 6          7     11          4  2015-09-17   1.0      1            0   \n",
       " 7          8     12          4  2015-09-17   1.0      1            0   \n",
       " 8          9     13          4  2015-09-17   1.0      1            0   \n",
       " 9         10     14          4  2015-09-17   1.0      1            0   \n",
       " 10        11     15          4  2015-09-17   1.0      1            0   \n",
       " 11        12     16          4  2015-09-17   1.0      1            0   \n",
       " 12        13     19          4  2015-09-17   1.0      1            0   \n",
       " 13        14     20          4  2015-09-17   1.0      1            0   \n",
       " 14        15     21          4  2015-09-17   1.0      1            0   \n",
       " 15        16     22          4  2015-09-17   1.0      1            0   \n",
       " 16        17     23          4  2015-09-17   1.0      1            0   \n",
       " 17        18     24          4  2015-09-17   1.0      1            0   \n",
       " 18        19     25          4  2015-09-17   1.0      1            0   \n",
       " 19        20     27          4  2015-09-17   1.0      1            0   \n",
       " 20        21     29          4  2015-09-17   1.0      1            0   \n",
       " 21        22     30          4  2015-09-17   1.0      1            0   \n",
       " 22        23     31          4  2015-09-17   1.0      1            0   \n",
       " 23        24     32          4  2015-09-17   1.0      1            0   \n",
       " 24        25     33          4  2015-09-17   1.0      1            0   \n",
       " 25        26     35          4  2015-09-17   1.0      1            0   \n",
       " 26        27     36          4  2015-09-17   1.0      1            0   \n",
       " 27        28     38          4  2015-09-17   1.0      1            0   \n",
       " 28        29     39          4  2015-09-17   1.0      1            0   \n",
       " 29        30     40          4  2015-09-17   1.0      1            0   \n",
       " ...      ...    ...        ...         ...   ...    ...          ...   \n",
       " 41058  41059   1077          6  2015-08-01   1.0      0            0   \n",
       " 41059  41060   1078          6  2015-08-01   1.0      0            0   \n",
       " 41060  41061   1079          6  2015-08-01   1.0      0            0   \n",
       " 41061  41062   1080          6  2015-08-01   1.0      0            0   \n",
       " 41062  41063   1083          6  2015-08-01   1.0      0            0   \n",
       " 41063  41064   1084          6  2015-08-01   1.0      0            0   \n",
       " 41064  41065   1086          6  2015-08-01   1.0      0            0   \n",
       " 41065  41066   1087          6  2015-08-01   1.0      0            0   \n",
       " 41066  41067   1088          6  2015-08-01   1.0      0            0   \n",
       " 41067  41068   1089          6  2015-08-01   1.0      0            0   \n",
       " 41068  41069   1091          6  2015-08-01   1.0      0            0   \n",
       " 41069  41070   1092          6  2015-08-01   1.0      0            0   \n",
       " 41070  41071   1094          6  2015-08-01   1.0      0            0   \n",
       " 41071  41072   1096          6  2015-08-01   1.0      0            0   \n",
       " 41072  41073   1097          6  2015-08-01   1.0      0            0   \n",
       " 41073  41074   1099          6  2015-08-01   1.0      0            0   \n",
       " 41074  41075   1100          6  2015-08-01   1.0      0            0   \n",
       " 41075  41076   1101          6  2015-08-01   1.0      0            0   \n",
       " 41076  41077   1102          6  2015-08-01   1.0      0            0   \n",
       " 41077  41078   1103          6  2015-08-01   1.0      0            0   \n",
       " 41078  41079   1104          6  2015-08-01   1.0      0            0   \n",
       " 41079  41080   1105          6  2015-08-01   1.0      0            0   \n",
       " 41080  41081   1106          6  2015-08-01   1.0      0            0   \n",
       " 41081  41082   1107          6  2015-08-01   1.0      0            0   \n",
       " 41082  41083   1109          6  2015-08-01   1.0      0            0   \n",
       " 41083  41084   1111          6  2015-08-01   1.0      0            0   \n",
       " 41084  41085   1112          6  2015-08-01   1.0      0            0   \n",
       " 41085  41086   1113          6  2015-08-01   1.0      0            0   \n",
       " 41086  41087   1114          6  2015-08-01   1.0      0            0   \n",
       " 41087  41088   1115          6  2015-08-01   1.0      0            0   \n",
       " \n",
       "        SchoolHoliday  \n",
       " 0                  0  \n",
       " 1                  0  \n",
       " 2                  0  \n",
       " 3                  0  \n",
       " 4                  0  \n",
       " 5                  0  \n",
       " 6                  0  \n",
       " 7                  0  \n",
       " 8                  0  \n",
       " 9                  0  \n",
       " 10                 0  \n",
       " 11                 0  \n",
       " 12                 0  \n",
       " 13                 0  \n",
       " 14                 0  \n",
       " 15                 0  \n",
       " 16                 0  \n",
       " 17                 0  \n",
       " 18                 0  \n",
       " 19                 0  \n",
       " 20                 0  \n",
       " 21                 0  \n",
       " 22                 0  \n",
       " 23                 0  \n",
       " 24                 0  \n",
       " 25                 0  \n",
       " 26                 0  \n",
       " 27                 0  \n",
       " 28                 0  \n",
       " 29                 0  \n",
       " ...              ...  \n",
       " 41058              0  \n",
       " 41059              0  \n",
       " 41060              1  \n",
       " 41061              0  \n",
       " 41062              1  \n",
       " 41063              1  \n",
       " 41064              1  \n",
       " 41065              1  \n",
       " 41066              0  \n",
       " 41067              0  \n",
       " 41068              0  \n",
       " 41069              0  \n",
       " 41070              0  \n",
       " 41071              0  \n",
       " 41072              0  \n",
       " 41073              0  \n",
       " 41074              0  \n",
       " 41075              0  \n",
       " 41076              0  \n",
       " 41077              1  \n",
       " 41078              0  \n",
       " 41079              0  \n",
       " 41080              0  \n",
       " 41081              0  \n",
       " 41082              0  \n",
       " 41083              0  \n",
       " 41084              0  \n",
       " 41085              0  \n",
       " 41086              0  \n",
       " 41087              1  \n",
       " \n",
       " [41088 rows x 8 columns]]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "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>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Customers</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>5263</td>\n",
       "      <td>555</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>6064</td>\n",
       "      <td>625</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>8314</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>13995</td>\n",
       "      <td>1498</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>4822</td>\n",
       "      <td>559</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  DayOfWeek        Date  Sales  Customers  Open  Promo StateHoliday  \\\n",
       "0      1          5  2015-07-31   5263        555     1      1            0   \n",
       "1      2          5  2015-07-31   6064        625     1      1            0   \n",
       "2      3          5  2015-07-31   8314        821     1      1            0   \n",
       "3      4          5  2015-07-31  13995       1498     1      1            0   \n",
       "4      5          5  2015-07-31   4822        559     1      1            0   \n",
       "\n",
       "   SchoolHoliday  \n",
       "0              1  \n",
       "1              1  \n",
       "2              1  \n",
       "3              1  \n",
       "4              1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Store</th>\n",
       "      <th>StoreType</th>\n",
       "      <th>Assortment</th>\n",
       "      <th>CompetitionDistance</th>\n",
       "      <th>CompetitionOpenSinceMonth</th>\n",
       "      <th>CompetitionOpenSinceYear</th>\n",
       "      <th>Promo2</th>\n",
       "      <th>Promo2SinceWeek</th>\n",
       "      <th>Promo2SinceYear</th>\n",
       "      <th>PromoInterval</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "      <td>1270.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>570.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>14130.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>1</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>c</td>\n",
       "      <td>c</td>\n",
       "      <td>620.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>29910.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store StoreType Assortment  CompetitionDistance  CompetitionOpenSinceMonth  \\\n",
       "0      1         c          a               1270.0                        9.0   \n",
       "1      2         a          a                570.0                       11.0   \n",
       "2      3         a          a              14130.0                       12.0   \n",
       "3      4         c          c                620.0                        9.0   \n",
       "4      5         a          a              29910.0                        4.0   \n",
       "\n",
       "   CompetitionOpenSinceYear  Promo2  Promo2SinceWeek  Promo2SinceYear  \\\n",
       "0                    2008.0       0              NaN              NaN   \n",
       "1                    2007.0       1             13.0           2010.0   \n",
       "2                    2006.0       1             14.0           2011.0   \n",
       "3                    2009.0       0              NaN              NaN   \n",
       "4                    2015.0       0              NaN              NaN   \n",
       "\n",
       "     PromoInterval  \n",
       "0              NaN  \n",
       "1  Jan,Apr,Jul,Oct  \n",
       "2  Jan,Apr,Jul,Oct  \n",
       "3              NaN  \n",
       "4              NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Store</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>TH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>NW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>SN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store State\n",
       "0      1    HE\n",
       "1      2    TH\n",
       "2      3    NW\n",
       "3      4    BE\n",
       "4      5    SN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>StateName</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BadenWuerttemberg</td>\n",
       "      <td>BW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bayern</td>\n",
       "      <td>BY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Berlin</td>\n",
       "      <td>BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brandenburg</td>\n",
       "      <td>BB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bremen</td>\n",
       "      <td>HB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           StateName State\n",
       "0  BadenWuerttemberg    BW\n",
       "1             Bayern    BY\n",
       "2             Berlin    BE\n",
       "3        Brandenburg    BB\n",
       "4             Bremen    HB"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>file</th>\n",
       "      <th>week</th>\n",
       "      <th>trend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-02 - 2012-12-08</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-09 - 2012-12-15</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-16 - 2012-12-22</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-23 - 2012-12-29</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-30 - 2013-01-05</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             file                     week  trend\n",
       "0  Rossmann_DE_SN  2012-12-02 - 2012-12-08     96\n",
       "1  Rossmann_DE_SN  2012-12-09 - 2012-12-15     95\n",
       "2  Rossmann_DE_SN  2012-12-16 - 2012-12-22     91\n",
       "3  Rossmann_DE_SN  2012-12-23 - 2012-12-29     48\n",
       "4  Rossmann_DE_SN  2012-12-30 - 2013-01-05     67"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>file</th>\n",
       "      <th>Date</th>\n",
       "      <th>Max_TemperatureC</th>\n",
       "      <th>Mean_TemperatureC</th>\n",
       "      <th>Min_TemperatureC</th>\n",
       "      <th>Dew_PointC</th>\n",
       "      <th>MeanDew_PointC</th>\n",
       "      <th>Min_DewpointC</th>\n",
       "      <th>Max_Humidity</th>\n",
       "      <th>Mean_Humidity</th>\n",
       "      <th>...</th>\n",
       "      <th>Max_VisibilityKm</th>\n",
       "      <th>Mean_VisibilityKm</th>\n",
       "      <th>Min_VisibilitykM</th>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <th>Max_Gust_SpeedKm_h</th>\n",
       "      <th>Precipitationmm</th>\n",
       "      <th>CloudCover</th>\n",
       "      <th>Events</th>\n",
       "      <th>WindDirDegrees</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NordrheinWestfalen</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>94</td>\n",
       "      <td>87</td>\n",
       "      <td>...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>39</td>\n",
       "      <td>26</td>\n",
       "      <td>58.0</td>\n",
       "      <td>5.08</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Rain</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NordrheinWestfalen</td>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>93</td>\n",
       "      <td>85</td>\n",
       "      <td>...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>24</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Rain</td>\n",
       "      <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NordrheinWestfalen</td>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>100</td>\n",
       "      <td>93</td>\n",
       "      <td>...</td>\n",
       "      <td>31.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.02</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Rain</td>\n",
       "      <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NordrheinWestfalen</td>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>100</td>\n",
       "      <td>94</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>23</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.25</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Rain</td>\n",
       "      <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NordrheinWestfalen</td>\n",
       "      <td>2013-01-05</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>100</td>\n",
       "      <td>94</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Rain</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 file        Date  Max_TemperatureC  Mean_TemperatureC  \\\n",
       "0  NordrheinWestfalen  2013-01-01                 8                  4   \n",
       "1  NordrheinWestfalen  2013-01-02                 7                  4   \n",
       "2  NordrheinWestfalen  2013-01-03                11                  8   \n",
       "3  NordrheinWestfalen  2013-01-04                 9                  9   \n",
       "4  NordrheinWestfalen  2013-01-05                 8                  8   \n",
       "\n",
       "   Min_TemperatureC  Dew_PointC  MeanDew_PointC  Min_DewpointC  Max_Humidity  \\\n",
       "0                 2           7               5              1            94   \n",
       "1                 1           5               3              2            93   \n",
       "2                 6          10               8              4           100   \n",
       "3                 8           9               9              8           100   \n",
       "4                 7           8               7              6           100   \n",
       "\n",
       "   Mean_Humidity       ...        Max_VisibilityKm  Mean_VisibilityKm  \\\n",
       "0             87       ...                    31.0               12.0   \n",
       "1             85       ...                    31.0               14.0   \n",
       "2             93       ...                    31.0                8.0   \n",
       "3             94       ...                    11.0                5.0   \n",
       "4             94       ...                    10.0                6.0   \n",
       "\n",
       "   Min_VisibilitykM  Max_Wind_SpeedKm_h  Mean_Wind_SpeedKm_h  \\\n",
       "0               4.0                  39                   26   \n",
       "1              10.0                  24                   16   \n",
       "2               2.0                  26                   21   \n",
       "3               2.0                  23                   14   \n",
       "4               3.0                  16                   10   \n",
       "\n",
       "   Max_Gust_SpeedKm_h  Precipitationmm  CloudCover  Events  WindDirDegrees  \n",
       "0                58.0             5.08         6.0    Rain             215  \n",
       "1                 NaN             0.00         6.0    Rain             225  \n",
       "2                 NaN             1.02         7.0    Rain             240  \n",
       "3                 NaN             0.25         7.0    Rain             263  \n",
       "4                 NaN             0.00         7.0    Rain             268  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  Store  DayOfWeek        Date  Open  Promo StateHoliday  SchoolHoliday\n",
       "0   1      1          4  2015-09-17   1.0      1            0              0\n",
       "1   2      3          4  2015-09-17   1.0      1            0              0\n",
       "2   3      7          4  2015-09-17   1.0      1            0              0\n",
       "3   4      8          4  2015-09-17   1.0      1            0              0\n",
       "4   5      9          4  2015-09-17   1.0      1            0              0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#use head() to display tables\n",
    "for t in tables: display(t.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#summarize aggregate information to each able accross each field\n",
    "# for t in tables: display(DataFrameSummary(t).summary())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Date Cleaning/Feature Engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "train, store, store_states, state_names, googletrend, weather, test = tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1017209, 41088)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(train),len(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#trun state Holiday to booleans\n",
    "train.StateHoliday = train.StateHoliday!='0'\n",
    "test.StateHoliday = test.StateHoliday!='0'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "Name: StateHoliday, dtype: bool"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.StateHoliday.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Pandas does joins using the merge method. The suffixes argument describes the naming convention for duplicate fields. \n",
    "#We've elected to leave the duplicate field names on the left untouched, and append a \"_y\" to those on the right.\n",
    "\n",
    "def join_df(left, right, left_on, right_on=None, suffix='_y'):\n",
    "    if right_on is None: right_on = left_on\n",
    "    return left.merge(right, how='left', left_on = left_on, right_on=right_on,\n",
    "                            suffixes=('',suffix))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather = join_df(weather, state_names, 'file', 'StateName')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>file</th>\n",
       "      <th>week</th>\n",
       "      <th>trend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-02 - 2012-12-08</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-09 - 2012-12-15</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-16 - 2012-12-22</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-23 - 2012-12-29</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-30 - 2013-01-05</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             file                     week  trend\n",
       "0  Rossmann_DE_SN  2012-12-02 - 2012-12-08     96\n",
       "1  Rossmann_DE_SN  2012-12-09 - 2012-12-15     95\n",
       "2  Rossmann_DE_SN  2012-12-16 - 2012-12-22     91\n",
       "3  Rossmann_DE_SN  2012-12-23 - 2012-12-29     48\n",
       "4  Rossmann_DE_SN  2012-12-30 - 2013-01-05     67"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "googletrend.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "googletrend['Date'] = googletrend.week.str.split(' - ', expand=True)[0]\n",
    "googletrend['State'] = googletrend.file.str.split('_', expand=True)[2]\n",
    "#Use a boolean list to select all the NI values in the State column\n",
    "#Using the .loc[rows, cols] feature to locate and replace selected values to 'HB,NI'\n",
    "googletrend.loc[googletrend.State=='NI', 'State'] = 'HB,NI'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(weather, 'Date', drop=False)\n",
    "add_datepart(googletrend, 'Date', drop=False)\n",
    "add_datepart(train, 'Date', drop=False)\n",
    "add_datepart(test, 'Date', drop=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "trend_de = googletrend[googletrend.file == 'Rossmann_DE']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store = join_df(store, store_states, \"Store\")\n",
    "len(store[store.State.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 0)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = join_df(train, store, \"Store\")\n",
    "joined_test = join_df(test, store, \"Store\")\n",
    "len(joined[joined.StoreType.isnull()]), len(joined_test[joined_test.StoreType.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>file</th>\n",
       "      <th>week</th>\n",
       "      <th>trend</th>\n",
       "      <th>Date</th>\n",
       "      <th>State</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Week</th>\n",
       "      <th>Day</th>\n",
       "      <th>Dayofweek</th>\n",
       "      <th>Dayofyear</th>\n",
       "      <th>Is_month_end</th>\n",
       "      <th>Is_month_start</th>\n",
       "      <th>Is_quarter_end</th>\n",
       "      <th>Is_quarter_start</th>\n",
       "      <th>Is_year_end</th>\n",
       "      <th>Is_year_start</th>\n",
       "      <th>Elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-02 - 2012-12-08</td>\n",
       "      <td>96</td>\n",
       "      <td>2012-12-02</td>\n",
       "      <td>SN</td>\n",
       "      <td>2012</td>\n",
       "      <td>12</td>\n",
       "      <td>48</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>337</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1354406400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-09 - 2012-12-15</td>\n",
       "      <td>95</td>\n",
       "      <td>2012-12-09</td>\n",
       "      <td>SN</td>\n",
       "      <td>2012</td>\n",
       "      <td>12</td>\n",
       "      <td>49</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>344</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1355011200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-16 - 2012-12-22</td>\n",
       "      <td>91</td>\n",
       "      <td>2012-12-16</td>\n",
       "      <td>SN</td>\n",
       "      <td>2012</td>\n",
       "      <td>12</td>\n",
       "      <td>50</td>\n",
       "      <td>16</td>\n",
       "      <td>6</td>\n",
       "      <td>351</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1355616000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-23 - 2012-12-29</td>\n",
       "      <td>48</td>\n",
       "      <td>2012-12-23</td>\n",
       "      <td>SN</td>\n",
       "      <td>2012</td>\n",
       "      <td>12</td>\n",
       "      <td>51</td>\n",
       "      <td>23</td>\n",
       "      <td>6</td>\n",
       "      <td>358</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1356220800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "      <td>2012-12-30 - 2013-01-05</td>\n",
       "      <td>67</td>\n",
       "      <td>2012-12-30</td>\n",
       "      <td>SN</td>\n",
       "      <td>2012</td>\n",
       "      <td>12</td>\n",
       "      <td>52</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>365</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>1356825600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             file                     week  trend       Date State  Year  \\\n",
       "0  Rossmann_DE_SN  2012-12-02 - 2012-12-08     96 2012-12-02    SN  2012   \n",
       "1  Rossmann_DE_SN  2012-12-09 - 2012-12-15     95 2012-12-09    SN  2012   \n",
       "2  Rossmann_DE_SN  2012-12-16 - 2012-12-22     91 2012-12-16    SN  2012   \n",
       "3  Rossmann_DE_SN  2012-12-23 - 2012-12-29     48 2012-12-23    SN  2012   \n",
       "4  Rossmann_DE_SN  2012-12-30 - 2013-01-05     67 2012-12-30    SN  2012   \n",
       "\n",
       "   Month  Week  Day  Dayofweek  Dayofyear  Is_month_end  Is_month_start  \\\n",
       "0     12    48    2          6        337         False           False   \n",
       "1     12    49    9          6        344         False           False   \n",
       "2     12    50   16          6        351         False           False   \n",
       "3     12    51   23          6        358         False           False   \n",
       "4     12    52   30          6        365         False           False   \n",
       "\n",
       "   Is_quarter_end  Is_quarter_start  Is_year_end  Is_year_start     Elapsed  \n",
       "0           False             False        False          False  1354406400  \n",
       "1           False             False        False          False  1355011200  \n",
       "2           False             False        False          False  1355616000  \n",
       "3           False             False        False          False  1356220800  \n",
       "4           False             False        False          False  1356825600  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "googletrend.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Date</th>\n",
       "      <th>Sales</th>\n",
       "      <th>Customers</th>\n",
       "      <th>Open</th>\n",
       "      <th>Promo</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>Year</th>\n",
       "      <th>...</th>\n",
       "      <th>StoreType</th>\n",
       "      <th>Assortment</th>\n",
       "      <th>CompetitionDistance</th>\n",
       "      <th>CompetitionOpenSinceMonth</th>\n",
       "      <th>CompetitionOpenSinceYear</th>\n",
       "      <th>Promo2</th>\n",
       "      <th>Promo2SinceWeek</th>\n",
       "      <th>Promo2SinceYear</th>\n",
       "      <th>PromoInterval</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>5263</td>\n",
       "      <td>555</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>...</td>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "      <td>1270.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>6064</td>\n",
       "      <td>625</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>...</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>570.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>1</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "      <td>TH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>8314</td>\n",
       "      <td>821</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>...</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>14130.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2006.0</td>\n",
       "      <td>1</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "      <td>NW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>13995</td>\n",
       "      <td>1498</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>...</td>\n",
       "      <td>c</td>\n",
       "      <td>c</td>\n",
       "      <td>620.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2009.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2015-07-31</td>\n",
       "      <td>4822</td>\n",
       "      <td>559</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2015</td>\n",
       "      <td>...</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>29910.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Store  DayOfWeek       Date  Sales  Customers  Open  Promo  StateHoliday  \\\n",
       "0      1          5 2015-07-31   5263        555     1      1         False   \n",
       "1      2          5 2015-07-31   6064        625     1      1         False   \n",
       "2      3          5 2015-07-31   8314        821     1      1         False   \n",
       "3      4          5 2015-07-31  13995       1498     1      1         False   \n",
       "4      5          5 2015-07-31   4822        559     1      1         False   \n",
       "\n",
       "   SchoolHoliday  Year  ...    StoreType  Assortment  CompetitionDistance  \\\n",
       "0              1  2015  ...            c           a               1270.0   \n",
       "1              1  2015  ...            a           a                570.0   \n",
       "2              1  2015  ...            a           a              14130.0   \n",
       "3              1  2015  ...            c           c                620.0   \n",
       "4              1  2015  ...            a           a              29910.0   \n",
       "\n",
       "   CompetitionOpenSinceMonth  CompetitionOpenSinceYear  Promo2  \\\n",
       "0                        9.0                    2008.0       0   \n",
       "1                       11.0                    2007.0       1   \n",
       "2                       12.0                    2006.0       1   \n",
       "3                        9.0                    2009.0       0   \n",
       "4                        4.0                    2015.0       0   \n",
       "\n",
       "   Promo2SinceWeek  Promo2SinceYear    PromoInterval  State  \n",
       "0              NaN              NaN              NaN     HE  \n",
       "1             13.0           2010.0  Jan,Apr,Jul,Oct     TH  \n",
       "2             14.0           2011.0  Jan,Apr,Jul,Oct     NW  \n",
       "3              NaN              NaN              NaN     BE  \n",
       "4              NaN              NaN              NaN     SN  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 0)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = join_df(joined, googletrend, ['State', 'Year', 'Week'])\n",
    "joined_test = join_df(joined_test, googletrend, ['State', 'Year', 'Week'])\n",
    "len(joined[joined.trend.isnull()]), len(joined_test[joined_test.trend.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 0)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = joined.merge(trend_de, 'left', ['Year', 'Week'], suffixes=('','_DE'))\n",
    "joined_test = joined_test.merge(trend_de, 'left', ['Year', 'Week'], suffixes=('','_DE'))\n",
    "len(joined[joined.trend_DE.isnull()]), len(joined_test[joined_test.trend_DE.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 0)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined = join_df(joined, weather, ['State','Date'])\n",
    "joined_test = join_df(joined_test, weather, [\"State\",\"Date\"])\n",
    "len(joined[joined.Mean_TemperatureC.isnull()]), len(joined_test[joined_test.Mean_TemperatureC.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in (joined, joined_test):\n",
    "    for c in df.columns:\n",
    "        if c.endswith('_y'):\n",
    "            if c in df.columns: df.drop(c, inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dealing with missing values\n",
    "for df in (joined, joined_test):\n",
    "    df['CompetitionOpenSinceYear'] = df.CompetitionOpenSinceYear.fillna(1900).astype(np.int32)\n",
    "    df['CompetitionOpenSinceMonth'] = df.CompetitionOpenSinceMonth.fillna(1).astype(np.int32)\n",
    "    df['Promo2SinceYear'] = df.Promo2SinceYear.fillna(1900).astype(np.int32)\n",
    "    df['Promo2SinceWeek'] = df.Promo2SinceWeek.fillna(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in (joined, joined_test):\n",
    "    df['CompetitionOpenSince'] = pd.to_datetime(dict(year=df.CompetitionOpenSinceYear,\n",
    "                                                    month=df.CompetitionOpenSinceMonth, day=15))\n",
    "    df[\"CompetitionDaysOpen\"] = df.Date.subtract(df.CompetitionOpenSince).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in (joined,joined_test): #?? Why assign value of zeros\n",
    "    df.loc[df.CompetitionDaysOpen<0, \"CompetitionDaysOpen\"] = 0\n",
    "    df.loc[df.CompetitionOpenSinceYear<1990, \"CompetitionDaysOpen\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([24,  3, 19,  9,  0, 16, 17,  7, 15, 22, 11, 13,  2, 23, 12,  4, 10,  1, 14, 20,  8, 18,  6, 21,  5])"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for df in (joined, joined_test):## issue here, inconsistent result from original notebook\n",
    "    df[\"CompetitionMonthsOpen\"] = df[\"CompetitionDaysOpen\"]//30\n",
    "    df.loc[df.CompetitionMonthsOpen>24, \"CompetitionMonthsOpen\"] = 24\n",
    "joined.CompetitionMonthsOpen.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in (joined,joined_test):\n",
    "    df['Promo2Since'] = pd.to_datetime(df.apply(lambda x: Week(\n",
    "                    x.Promo2SinceYear, x.Promo2SinceWeek).monday(), axis=1).astype(pd.datetime))\n",
    "    df[\"Promo2Days\"] = df.Date.subtract(df[\"Promo2Since\"]).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "for df in (joined,joined_test):\n",
    "    df.loc[df.Promo2Days<0, \"Promo2Days\"] = 0\n",
    "    df.loc[df.Promo2SinceYear<1990, \"Promo2Days\"] = 0\n",
    "    df[\"Promo2Weeks\"] = df[\"Promo2Days\"]//7\n",
    "    df.loc[df.Promo2Weeks<0, \"Promo2Weeks\"] = 0\n",
    "    df.loc[df.Promo2Weeks>25, \"Promo2Weeks\"] = 25\n",
    "    df.Promo2Weeks.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined.to_feather(f'{PATH}joined')\n",
    "joined_test.to_feather(f'{PATH}joined_test')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Durations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_elapsed(fld, pre): #??\n",
    "    day1 = np.timedelta64(1, 'D')\n",
    "    last_date = np.datetime64()\n",
    "    last_store = 0\n",
    "    res = []\n",
    "\n",
    "    for s,v,d in zip(df.Store.values,df[fld].values, df.Date.values):\n",
    "        if s != last_store:\n",
    "            last_date = np.datetime64()\n",
    "            last_store = s\n",
    "        if v: last_date = d\n",
    "        res.append(((d-last_date).astype('timedelta64[D]') / day1))\n",
    "    df[pre+fld] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\"Date\", \"Store\", \"Promo\", \"StateHoliday\", \"SchoolHoliday\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = train[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = test[columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "fld = 'SchoolHoliday'\n",
    "df = df.sort_values(['Store', 'Date'])\n",
    "get_elapsed(fld, 'After')\n",
    "df = df.sort_values(['Store', 'Date'], ascending=[True, False])\n",
    "get_elapsed(fld, 'Before')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "fld = 'StateHoliday'\n",
    "df = df.sort_values(['Store', 'Date'])\n",
    "get_elapsed(fld, 'After')\n",
    "df = df.sort_values(['Store', 'Date'], ascending=[True, False])\n",
    "get_elapsed(fld, 'Before')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "fld = 'Promo'\n",
    "df = df.sort_values(['Store', 'Date'])\n",
    "get_elapsed(fld, 'After')\n",
    "df = df.sort_values(['Store', 'Date'], ascending=[True, False])\n",
    "get_elapsed(fld, 'Before')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(\"Date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = [\"SchoolHoliday\", \"StateHoliday\", \"Promo\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "for o in [\"Before\", \"After\"]:\n",
    "    for p in columns:\n",
    "        a = o + p\n",
    "        df[a] = df[a].fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "bwd = df[['Store']+columns].sort_index().groupby(\"Store\").rolling(7, min_periods=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "fwd = df[['Store']+columns].sort_index(ascending=False\n",
    "                                      ).groupby(\"Store\").rolling(7, min_periods=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "bwd.drop('Store',1,inplace=True)\n",
    "bwd.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "fwd.drop('Store',1,inplace=True)\n",
    "fwd.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(bwd, 'left', ['Date', 'Store'], suffixes=['', '_bw'])\n",
    "df = df.merge(fwd, 'left', ['Date', 'Store'], suffixes=['', '_fw'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(columns,1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "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>Store</th>\n",
       "      <th>AfterSchoolHoliday</th>\n",
       "      <th>BeforeSchoolHoliday</th>\n",
       "      <th>AfterStateHoliday</th>\n",
       "      <th>BeforeStateHoliday</th>\n",
       "      <th>AfterPromo</th>\n",
       "      <th>BeforePromo</th>\n",
       "      <th>SchoolHoliday_bw</th>\n",
       "      <th>StateHoliday_bw</th>\n",
       "      <th>Promo_bw</th>\n",
       "      <th>SchoolHoliday_fw</th>\n",
       "      <th>StateHoliday_fw</th>\n",
       "      <th>Promo_fw</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-09-17</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-09-16</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-15</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-09-14</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-09-13</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date  Store  AfterSchoolHoliday  BeforeSchoolHoliday  \\\n",
       "0 2015-09-17      1                  13                    0   \n",
       "1 2015-09-16      1                  12                    0   \n",
       "2 2015-09-15      1                  11                    0   \n",
       "3 2015-09-14      1                  10                    0   \n",
       "4 2015-09-13      1                   9                    0   \n",
       "\n",
       "   AfterStateHoliday  BeforeStateHoliday  AfterPromo  BeforePromo  \\\n",
       "0                  0                   0           0            0   \n",
       "1                  0                   0           0            0   \n",
       "2                  0                   0           0            0   \n",
       "3                  0                   0           0            0   \n",
       "4                  0                   0           9           -1   \n",
       "\n",
       "   SchoolHoliday_bw  StateHoliday_bw  Promo_bw  SchoolHoliday_fw  \\\n",
       "0               0.0              0.0       4.0               0.0   \n",
       "1               0.0              0.0       3.0               0.0   \n",
       "2               0.0              0.0       2.0               0.0   \n",
       "3               0.0              0.0       1.0               0.0   \n",
       "4               0.0              0.0       0.0               0.0   \n",
       "\n",
       "   StateHoliday_fw  Promo_fw  \n",
       "0              0.0       1.0  \n",
       "1              0.0       2.0  \n",
       "2              0.0       3.0  \n",
       "3              0.0       4.0  \n",
       "4              0.0       4.0  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_feather(f'{PATH}df')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_feather(f'{PATH}df')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"Date\"] = pd.to_datetime(df.Date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'Store', 'AfterSchoolHoliday', 'BeforeSchoolHoliday',\n",
       "       'AfterStateHoliday', 'BeforeStateHoliday', 'AfterPromo', 'BeforePromo',\n",
       "       'SchoolHoliday_bw', 'StateHoliday_bw', 'Promo_bw', 'SchoolHoliday_fw',\n",
       "       'StateHoliday_fw', 'Promo_fw'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = join_df(joined, df, ['Store', 'Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_test = join_df(joined_test, df, ['Store', 'Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = joined[joined.Sales!=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined.reset_index(inplace=True)\n",
    "joined_test.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined.to_feather(f'{PATH}joined')\n",
    "joined_test.to_feather(f'{PATH}joined_test')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = pd.read_feather(f'{PATH}joined')\n",
    "joined_test = pd.read_feather(f'{PATH}joined_test')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Store</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DayOfWeek</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>2015-07-31 00:00:00</td>\n",
       "      <td>2015-07-31 00:00:00</td>\n",
       "      <td>2015-07-31 00:00:00</td>\n",
       "      <td>2015-07-31 00:00:00</td>\n",
       "      <td>2015-07-31 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales</th>\n",
       "      <td>5263</td>\n",
       "      <td>6064</td>\n",
       "      <td>8314</td>\n",
       "      <td>13995</td>\n",
       "      <td>4822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customers</th>\n",
       "      <td>555</td>\n",
       "      <td>625</td>\n",
       "      <td>821</td>\n",
       "      <td>1498</td>\n",
       "      <td>559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Open</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <td>2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>2015</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Week</th>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Day</th>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dayofweek</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dayofyear</th>\n",
       "      <td>212</td>\n",
       "      <td>212</td>\n",
       "      <td>212</td>\n",
       "      <td>212</td>\n",
       "      <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_month_end</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_month_start</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_quarter_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_quarter_start</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_year_end</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_year_start</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elapsed</th>\n",
       "      <td>1438300800</td>\n",
       "      <td>1438300800</td>\n",
       "      <td>1438300800</td>\n",
       "      <td>1438300800</td>\n",
       "      <td>1438300800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType</th>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assortment</th>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionDistance</th>\n",
       "      <td>1270</td>\n",
       "      <td>570</td>\n",
       "      <td>14130</td>\n",
       "      <td>620</td>\n",
       "      <td>29910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionOpenSinceMonth</th>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionOpenSinceYear</th>\n",
       "      <td>2008</td>\n",
       "      <td>2007</td>\n",
       "      <td>2006</td>\n",
       "      <td>2009</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2SinceWeek</th>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2SinceYear</th>\n",
       "      <td>1900</td>\n",
       "      <td>2010</td>\n",
       "      <td>2011</td>\n",
       "      <td>1900</td>\n",
       "      <td>1900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PromoInterval</th>\n",
       "      <td>None</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <td>HE</td>\n",
       "      <td>TH</td>\n",
       "      <td>NW</td>\n",
       "      <td>BE</td>\n",
       "      <td>SN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>file</th>\n",
       "      <td>Rossmann_DE_HE</td>\n",
       "      <td>Rossmann_DE_TH</td>\n",
       "      <td>Rossmann_DE_NW</td>\n",
       "      <td>Rossmann_DE_BE</td>\n",
       "      <td>Rossmann_DE_SN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week</th>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trend</th>\n",
       "      <td>85</td>\n",
       "      <td>80</td>\n",
       "      <td>86</td>\n",
       "      <td>74</td>\n",
       "      <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>file_DE</th>\n",
       "      <td>Rossmann_DE</td>\n",
       "      <td>Rossmann_DE</td>\n",
       "      <td>Rossmann_DE</td>\n",
       "      <td>Rossmann_DE</td>\n",
       "      <td>Rossmann_DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>week_DE</th>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "      <td>2015-08-02 - 2015-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trend_DE</th>\n",
       "      <td>83</td>\n",
       "      <td>83</td>\n",
       "      <td>83</td>\n",
       "      <td>83</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date_DE</th>\n",
       "      <td>2015-08-02 00:00:00</td>\n",
       "      <td>2015-08-02 00:00:00</td>\n",
       "      <td>2015-08-02 00:00:00</td>\n",
       "      <td>2015-08-02 00:00:00</td>\n",
       "      <td>2015-08-02 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 0                        1  \\\n",
       "index                                            0                        1   \n",
       "Store                                            1                        2   \n",
       "DayOfWeek                                        5                        5   \n",
       "Date                           2015-07-31 00:00:00      2015-07-31 00:00:00   \n",
       "Sales                                         5263                     6064   \n",
       "Customers                                      555                      625   \n",
       "Open                                             1                        1   \n",
       "Promo                                            1                        1   \n",
       "StateHoliday                                 False                    False   \n",
       "SchoolHoliday                                    1                        1   \n",
       "Year                                          2015                     2015   \n",
       "Month                                            7                        7   \n",
       "Week                                            31                       31   \n",
       "Day                                             31                       31   \n",
       "Dayofweek                                        4                        4   \n",
       "Dayofyear                                      212                      212   \n",
       "Is_month_end                                  True                     True   \n",
       "Is_month_start                               False                    False   \n",
       "Is_quarter_end                               False                    False   \n",
       "Is_quarter_start                             False                    False   \n",
       "Is_year_end                                  False                    False   \n",
       "Is_year_start                                False                    False   \n",
       "Elapsed                                 1438300800               1438300800   \n",
       "StoreType                                        c                        a   \n",
       "Assortment                                       a                        a   \n",
       "CompetitionDistance                           1270                      570   \n",
       "CompetitionOpenSinceMonth                        9                       11   \n",
       "CompetitionOpenSinceYear                      2008                     2007   \n",
       "Promo2                                           0                        1   \n",
       "Promo2SinceWeek                                  1                       13   \n",
       "Promo2SinceYear                               1900                     2010   \n",
       "PromoInterval                                 None          Jan,Apr,Jul,Oct   \n",
       "State                                           HE                       TH   \n",
       "file                                Rossmann_DE_HE           Rossmann_DE_TH   \n",
       "week                       2015-08-02 - 2015-08-08  2015-08-02 - 2015-08-08   \n",
       "trend                                           85                       80   \n",
       "file_DE                                Rossmann_DE              Rossmann_DE   \n",
       "week_DE                    2015-08-02 - 2015-08-08  2015-08-02 - 2015-08-08   \n",
       "trend_DE                                        83                       83   \n",
       "Date_DE                        2015-08-02 00:00:00      2015-08-02 00:00:00   \n",
       "\n",
       "                                                 2                        3  \\\n",
       "index                                            2                        3   \n",
       "Store                                            3                        4   \n",
       "DayOfWeek                                        5                        5   \n",
       "Date                           2015-07-31 00:00:00      2015-07-31 00:00:00   \n",
       "Sales                                         8314                    13995   \n",
       "Customers                                      821                     1498   \n",
       "Open                                             1                        1   \n",
       "Promo                                            1                        1   \n",
       "StateHoliday                                 False                    False   \n",
       "SchoolHoliday                                    1                        1   \n",
       "Year                                          2015                     2015   \n",
       "Month                                            7                        7   \n",
       "Week                                            31                       31   \n",
       "Day                                             31                       31   \n",
       "Dayofweek                                        4                        4   \n",
       "Dayofyear                                      212                      212   \n",
       "Is_month_end                                  True                     True   \n",
       "Is_month_start                               False                    False   \n",
       "Is_quarter_end                               False                    False   \n",
       "Is_quarter_start                             False                    False   \n",
       "Is_year_end                                  False                    False   \n",
       "Is_year_start                                False                    False   \n",
       "Elapsed                                 1438300800               1438300800   \n",
       "StoreType                                        a                        c   \n",
       "Assortment                                       a                        c   \n",
       "CompetitionDistance                          14130                      620   \n",
       "CompetitionOpenSinceMonth                       12                        9   \n",
       "CompetitionOpenSinceYear                      2006                     2009   \n",
       "Promo2                                           1                        0   \n",
       "Promo2SinceWeek                                 14                        1   \n",
       "Promo2SinceYear                               2011                     1900   \n",
       "PromoInterval                      Jan,Apr,Jul,Oct                     None   \n",
       "State                                           NW                       BE   \n",
       "file                                Rossmann_DE_NW           Rossmann_DE_BE   \n",
       "week                       2015-08-02 - 2015-08-08  2015-08-02 - 2015-08-08   \n",
       "trend                                           86                       74   \n",
       "file_DE                                Rossmann_DE              Rossmann_DE   \n",
       "week_DE                    2015-08-02 - 2015-08-08  2015-08-02 - 2015-08-08   \n",
       "trend_DE                                        83                       83   \n",
       "Date_DE                        2015-08-02 00:00:00      2015-08-02 00:00:00   \n",
       "\n",
       "                                                 4  \n",
       "index                                            4  \n",
       "Store                                            5  \n",
       "DayOfWeek                                        5  \n",
       "Date                           2015-07-31 00:00:00  \n",
       "Sales                                         4822  \n",
       "Customers                                      559  \n",
       "Open                                             1  \n",
       "Promo                                            1  \n",
       "StateHoliday                                 False  \n",
       "SchoolHoliday                                    1  \n",
       "Year                                          2015  \n",
       "Month                                            7  \n",
       "Week                                            31  \n",
       "Day                                             31  \n",
       "Dayofweek                                        4  \n",
       "Dayofyear                                      212  \n",
       "Is_month_end                                  True  \n",
       "Is_month_start                               False  \n",
       "Is_quarter_end                               False  \n",
       "Is_quarter_start                             False  \n",
       "Is_year_end                                  False  \n",
       "Is_year_start                                False  \n",
       "Elapsed                                 1438300800  \n",
       "StoreType                                        a  \n",
       "Assortment                                       a  \n",
       "CompetitionDistance                          29910  \n",
       "CompetitionOpenSinceMonth                        4  \n",
       "CompetitionOpenSinceYear                      2015  \n",
       "Promo2                                           0  \n",
       "Promo2SinceWeek                                  1  \n",
       "Promo2SinceYear                               1900  \n",
       "PromoInterval                                 None  \n",
       "State                                           SN  \n",
       "file                                Rossmann_DE_SN  \n",
       "week                       2015-08-02 - 2015-08-08  \n",
       "trend                                           82  \n",
       "file_DE                                Rossmann_DE  \n",
       "week_DE                    2015-08-02 - 2015-08-08  \n",
       "trend_DE                                        83  \n",
       "Date_DE                        2015-08-02 00:00:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined.head().T.head(40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "844338"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_vars = ['Store', 'DayOfWeek', 'Year', 'Month', 'Day', 'StateHoliday', 'CompetitionMonthsOpen',\n",
    "    'Promo2Weeks', 'StoreType', 'Assortment', 'PromoInterval', 'CompetitionOpenSinceYear', 'Promo2SinceYear',\n",
    "    'State', 'Week', 'Events', 'Promo_fw', 'Promo_bw', 'StateHoliday_fw', 'StateHoliday_bw',\n",
    "    'SchoolHoliday_fw', 'SchoolHoliday_bw']\n",
    "\n",
    "contin_vars = ['CompetitionDistance', 'Max_TemperatureC', 'Mean_TemperatureC', 'Min_TemperatureC',\n",
    "   'Max_Humidity', 'Mean_Humidity', 'Min_Humidity', 'Max_Wind_SpeedKm_h', \n",
    "   'Mean_Wind_SpeedKm_h', 'CloudCover', 'trend', 'trend_DE',\n",
    "   'AfterStateHoliday', 'BeforeStateHoliday', 'Promo', 'SchoolHoliday']\n",
    "\n",
    "n = len(joined); n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dep = 'Sales'\n",
    "joined = joined[cat_vars+contin_vars+[dep, 'Date']].copy() #?????"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_test[dep] = 0\n",
    "joined_test = joined_test[cat_vars+contin_vars+[dep, 'Date', 'Id']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "for v in cat_vars: joined[v] = joined[v].astype('category').cat.as_ordered()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "apply_cats(joined_test, joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "for v in contin_vars:\n",
    "    joined[v] = joined[v].fillna(0).astype('float32')\n",
    "    joined_test[v] = joined_test[v].fillna(0).astype('float32')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## run on a sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150000"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#run on a sample\n",
    "idxs = get_cv_idxs(n, val_pct=150000/n) #validation percentage\n",
    "joined_samp = joined.iloc[idxs].set_index(\"Date\")\n",
    "samp_size = len(joined_samp); samp_size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## run on full dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(844338, 39)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#to run on full dataset\n",
    "sample_size = n\n",
    "joined_samp = joined.set_index(\"Date\")\n",
    "joined_samp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>CompetitionMonthsOpen</th>\n",
       "      <th>Promo2Weeks</th>\n",
       "      <th>StoreType</th>\n",
       "      <th>Assortment</th>\n",
       "      <th>...</th>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <th>CloudCover</th>\n",
       "      <th>trend</th>\n",
       "      <th>trend_DE</th>\n",
       "      <th>AfterStateHoliday</th>\n",
       "      <th>BeforeStateHoliday</th>\n",
       "      <th>Promo</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <th>Sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-07-31</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2015</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>False</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>c</td>\n",
       "      <td>a</td>\n",
       "      <td>...</td>\n",
       "      <td>24.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-07-31</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2015</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>False</td>\n",
       "      <td>24</td>\n",
       "      <td>25</td>\n",
       "      <td>a</td>\n",
       "      <td>a</td>\n",
       "      <td>...</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6064</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Store DayOfWeek  Year Month Day StateHoliday CompetitionMonthsOpen  \\\n",
       "Date                                                                            \n",
       "2015-07-31     1         5  2015     7  31        False                    24   \n",
       "2015-07-31     2         5  2015     7  31        False                    24   \n",
       "\n",
       "           Promo2Weeks StoreType Assortment  ...  Max_Wind_SpeedKm_h  \\\n",
       "Date                                         ...                       \n",
       "2015-07-31           0         c          a  ...                24.0   \n",
       "2015-07-31          25         a          a  ...                14.0   \n",
       "\n",
       "           Mean_Wind_SpeedKm_h CloudCover trend trend_DE AfterStateHoliday  \\\n",
       "Date                                                                         \n",
       "2015-07-31                11.0        1.0  85.0     83.0               0.0   \n",
       "2015-07-31                11.0        4.0  80.0     83.0               0.0   \n",
       "\n",
       "           BeforeStateHoliday Promo SchoolHoliday Sales  \n",
       "Date                                                     \n",
       "2015-07-31                0.0   1.0           1.0  5263  \n",
       "2015-07-31                0.0   1.0           1.0  6064  \n",
       "\n",
       "[2 rows x 39 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_samp.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "df, y, nas, mapper = proc_df(joined_samp, 'Sales', do_scale=True) #do_scale\n",
    "yl = np.log(y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'Date'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   3063\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3064\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3065\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'Date'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-48-fac8469061c8>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mjoined_test\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mjoined_test\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mset_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Date\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mset_index\u001b[0;34m(self, keys, drop, append, inplace, verify_integrity)\u001b[0m\n\u001b[1;32m   3907\u001b[0m                 \u001b[0mnames\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3908\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3909\u001b[0;31m                 \u001b[0mlevel\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mframe\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mcol\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3910\u001b[0m                 \u001b[0mnames\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcol\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3911\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0mdrop\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   2686\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_getitem_multilevel\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2687\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2688\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_getitem_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2689\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2690\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_getitem_column\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m_getitem_column\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   2693\u001b[0m         \u001b[0;31m# get column\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2694\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_unique\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2695\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_item_cache\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2696\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2697\u001b[0m         \u001b[0;31m# duplicate columns & possible reduce dimensionality\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36m_get_item_cache\u001b[0;34m(self, item)\u001b[0m\n\u001b[1;32m   2484\u001b[0m         \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcache\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2485\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mres\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2486\u001b[0;31m             \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2487\u001b[0m             \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_box_item_values\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalues\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2488\u001b[0m             \u001b[0mcache\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mres\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/internals.py\u001b[0m in \u001b[0;36mget\u001b[0;34m(self, item, fastpath)\u001b[0m\n\u001b[1;32m   4113\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4114\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misna\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 4115\u001b[0;31m                 \u001b[0mloc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mitem\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   4116\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   4117\u001b[0m                 \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0marange\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0misna\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitems\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/fastai/lib/python3.6/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mget_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   3064\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3065\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3066\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_loc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_maybe_cast_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3067\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3068\u001b[0m         \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmethod\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtolerance\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtolerance\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/index.pyx\u001b[0m in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'Date'"
     ]
    }
   ],
   "source": [
    "joined_test = joined_test.set_index(\"Date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test, _, nas, mapper = proc_df(joined_test, \"Sales\", do_scale=True, skip_flds=['Id'], mapper=mapper, na_dict=nas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>Store</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>StateHoliday</th>\n",
       "      <th>CompetitionMonthsOpen</th>\n",
       "      <th>Promo2Weeks</th>\n",
       "      <th>StoreType</th>\n",
       "      <th>Assortment</th>\n",
       "      <th>...</th>\n",
       "      <th>Min_Humidity</th>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <th>CloudCover</th>\n",
       "      <th>trend</th>\n",
       "      <th>trend_DE</th>\n",
       "      <th>AfterStateHoliday</th>\n",
       "      <th>BeforeStateHoliday</th>\n",
       "      <th>Promo</th>\n",
       "      <th>SchoolHoliday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2015-07-31</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.620066</td>\n",
       "      <td>0.149027</td>\n",
       "      <td>-0.142774</td>\n",
       "      <td>-1.844823</td>\n",
       "      <td>1.732492</td>\n",
       "      <td>1.724334</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.113717</td>\n",
       "      <td>2.04105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-07-31</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.264031</td>\n",
       "      <td>-0.960613</td>\n",
       "      <td>-0.142774</td>\n",
       "      <td>-0.488722</td>\n",
       "      <td>1.294578</td>\n",
       "      <td>1.724334</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.113717</td>\n",
       "      <td>2.04105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Store  DayOfWeek  Year  Month  Day  StateHoliday  \\\n",
       "Date                                                           \n",
       "2015-07-31      1          5     3      7   31             1   \n",
       "2015-07-31      2          5     3      7   31             1   \n",
       "\n",
       "            CompetitionMonthsOpen  Promo2Weeks  StoreType  Assortment  \\\n",
       "Date                                                                    \n",
       "2015-07-31                     25            1          3           1   \n",
       "2015-07-31                     25           26          1           1   \n",
       "\n",
       "                ...        Min_Humidity  Max_Wind_SpeedKm_h  \\\n",
       "Date            ...                                           \n",
       "2015-07-31      ...           -1.620066            0.149027   \n",
       "2015-07-31      ...           -1.264031           -0.960613   \n",
       "\n",
       "            Mean_Wind_SpeedKm_h  CloudCover     trend  trend_DE  \\\n",
       "Date                                                              \n",
       "2015-07-31            -0.142774   -1.844823  1.732492  1.724334   \n",
       "2015-07-31            -0.142774   -0.488722  1.294578  1.724334   \n",
       "\n",
       "            AfterStateHoliday  BeforeStateHoliday     Promo  SchoolHoliday  \n",
       "Date                                                                        \n",
       "2015-07-31                0.0                 0.0  1.113717        2.04105  \n",
       "2015-07-31                0.0                 0.0  1.113717        2.04105  \n",
       "\n",
       "[2 rows x 38 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_ratio = 0.75\n",
    "train_size = int(samp_size * train_ratio); train_size\n",
    "val_idx = list(range(train_size, len(df)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(112500, 844338)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_size, len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Better option for picking validation set is using the exact same length of tie period oas the test set values\n",
    "val_idx = np.flatnonzero( #flatnonzero returns indices that are non-zero in the falttened version of a\n",
    "    (df.index<=datetime.datetime(2014,9,17)) & (df.index>=datetime.datetime(2014,8,1))) #only return Ture when both range conditions are satisfied"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Deep Learning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Implement competition metrics --- root-mean-square pervent error in this case\n",
    "def inv_y(a): return np.exp(a)\n",
    "\n",
    "def exp_rmspe(y_pred, targ):\n",
    "    targ = inv_y(targ)\n",
    "    pct_var = (targ - inv_y(y_pred))/targ\n",
    "    return math.sqrt((pct_var**2).mean())\n",
    "\n",
    "max_log_y = np.max(yl)\n",
    "y_range = (0, max_log_y*1.2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modeldata object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "#ModelData object ??\n",
    "md = ColumnarModelData.from_data_frame(PATH, val_idx, df, yl.astype(np.float32), cat_flds=cat_vars, bs=128, test_df=df_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#show cardinality for each categories\n",
    "\n",
    "cat_sz = [(c,len(joined_samp[c].cat.categories)+1) for c in cat_vars]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Store', 1116),\n",
       " ('DayOfWeek', 8),\n",
       " ('Year', 4),\n",
       " ('Month', 13),\n",
       " ('Day', 32),\n",
       " ('StateHoliday', 3),\n",
       " ('CompetitionMonthsOpen', 26),\n",
       " ('Promo2Weeks', 27),\n",
       " ('StoreType', 5),\n",
       " ('Assortment', 4),\n",
       " ('PromoInterval', 4),\n",
       " ('CompetitionOpenSinceYear', 24),\n",
       " ('Promo2SinceYear', 9),\n",
       " ('State', 13),\n",
       " ('Week', 53),\n",
       " ('Events', 22),\n",
       " ('Promo_fw', 1),\n",
       " ('Promo_bw', 1),\n",
       " ('StateHoliday_fw', 1),\n",
       " ('StateHoliday_bw', 1),\n",
       " ('SchoolHoliday_fw', 1),\n",
       " ('SchoolHoliday_bw', 1)]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cat_sz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1116, 50),\n",
       " (8, 4),\n",
       " (4, 2),\n",
       " (13, 7),\n",
       " (32, 16),\n",
       " (3, 2),\n",
       " (26, 13),\n",
       " (27, 14),\n",
       " (5, 3),\n",
       " (4, 2),\n",
       " (4, 2),\n",
       " (24, 12),\n",
       " (9, 5),\n",
       " (13, 7),\n",
       " (53, 27),\n",
       " (22, 11),\n",
       " (1, 1),\n",
       " (1, 1),\n",
       " (1, 1),\n",
       " (1, 1),\n",
       " (1, 1),\n",
       " (1, 1)]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# set embeing sizes\n",
    "emb_szs = [(c, min(50,(c+1)//2)) for _,c in cat_sz] #use roughly half the cardinality as the embeding matrix size\n",
    "# _,c because cat_sz is a tuple e.g.('Store', 1116) and what we are interested in is the second value\n",
    "emb_szs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = md.get_learner(emb_szs, len(df.columns) - len(cat_vars), 0.04, 1, [1000, 500], [0.001, 0.01], y_range = y_range)\n",
    "lr = 1e-3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "99debbf260c14cb48c34a3a1ee74f123",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=1), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 60%|█████▉    | 670/1120 [00:08<00:05, 82.74it/s, loss=0.324] \n",
      "                                                              \r"
     ]
    }
   ],
   "source": [
    "m.lr_find()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYUAAAEOCAYAAABmVAtTAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvhp/UCwAAIABJREFUeJzt3Xd4lfX9//Hn+2SSTUjCSNgb2UZEBBSLVquCE7XWule1am1r7W5tf1+tVlvraKXU2uFCrRZcOKoiKkiYMgQiQxJWWCEhZJycz++Pc4wRAwTMnTvJeT2u61w5934nN5zX+dzjc5tzDhEREYCA3wWIiEjLoVAQEZE6CgUREamjUBARkToKBRERqaNQEBGROgoFERGpo1AQEZE6CgUREamjUBARkTqxfhdwuLKyslyPHj38LkNEpFVZsGDBdudc9qHma3Wh0KNHDwoKCvwuQ0SkVTGzDY2ZT4ePRESkjkJBRETqKBRERKSOQkFEROooFEREpI5CQURE6kRNKBTv3sdLSzf7XYaISIsWNaHw38XF3PDEQnburfa7FBGRFitqQiG/eyYACzbs8rkSEZGWK2pCYWheOnExRsGGnX6XIiLSYkVNKCTGxTA4N50F69VSEBE5kKgJBYD87u1ZWlxKVbDW71JERFqkqAqFo7tnUh0Msay41O9SRERapCgLhfYAFOgQkohIg6IqFLJTE+jRIYkCXYEkItKgqAoFCB9CWrhhF845v0sREWlxoi4U8nu0Z8featbvqPC7FBGRFifqQuGz8woLdQhJRORLoi4U+mSnkJoQy8JPFQoiIvuLulAIBIyR3dvzwdodfpciItLiRF0oAIzrm8Xakr0U7dJ5BRGR+qIyFE7olw3A7NXbfa5ERKRlicpQ6JOTQpf0RGavLvG7FBGRFiUqQ8HMGN8vm/cKt1NTG/K7HBGRFiMqQwFgfL9syqqCLN642+9SRERajKgNheN7ZxEwdAhJRKSeqA2F9KQ4RnRrr1AQEaknakMBYHzfbJYWl+q5zSIiEdEdCv2ycA7eXaPWgogIRHkoDM3LICMpTvcriIhERHUoxASMsX2yeHdNibrSFhHB41Aws1PNbJWZFZrZ7Q1Mv8zMSsxsceR1lZf1NGR8v2y2lVXx8Zay5t60iEiL41komFkM8BBwGjAIuMjMBjUw69POueGR1zSv6jmQ8X3DXV68o6uQREQ8bSmMAgqdc2udc9XAU8BkD7d3RDqlJ9K/Y6ouTRURwdtQyAU21hsuiozb37lmttTMnjWzrh7Wc0An9M+mYP0uKqqDfmxeRKTF8DIUrIFx+5/NnQn0cM4NBd4A/tHgisyuMbMCMysoKWn6b/Tj+2ZTXRtirp6xICJRzstQKALqf/PPAzbVn8E5t8M5VxUZ/CtwdEMrcs5Ndc7lO+fys7Ozm7zQ/B7tSYwL6NJUEYl6XobCfKCvmfU0s3jgQmBG/RnMrHO9wUnASg/rOaDEuBhG9+qgk80iEvU8CwXnXBC4EZhF+MN+unNuuZndYWaTIrPdZGbLzWwJcBNwmVf1HMoJ/bJZt30vG3fqaWwiEr1ivVy5c+5l4OX9xv2i3vsfAz/2sobGGt/v80tTvzW6u8/ViIj4I6rvaK6vV1YyuRntdGmqiEQ1hULEZ09je/+THXoam4hELYVCPSf0y6K8KsjCDbv8LkVExBcKhXrG9MkiJmDMVlfaIhKlFAr1pCXGMSwvnfc/0U1sIhKdFAr7GdM7i6VFpeyprPG7FBGRZqdQ2M9JA3OoDTlmLdvidykiIs1OobCfEV0z6NEhif8sLPa7FBGRZqdQ2I+ZcdaIXOau28Gm3fv8LkdEpFkpFBpwzog8nIMXFqu1ICLRRaHQgG4dksjv3p7nFxbr2c0iElUUCgdw9shc1mwrZ/mmPX6XIiLSbBQKB3DGkC7ExwR0wllEoopC4QDSk+I4aUAOM5ZsIqi+kEQkSigUDuLskblsL6/i3UI9kU1EooNC4SAm9M8hIymO53UISUSihELhIOJjA5wxtDOvrdhCeVXQ73JERDynUDiEs0fkUVkT4rXl6vZCRNo+hcIhjOiaQce0BN5YudXvUkREPKdQOIRAwDhpQEdmr95OVbDW73JERDylUGiEiQNzKK8KMnftTr9LERHxlEKhEY7vk0Vmcjx/efsTv0sREfGUQqEREuNiuHZ8Lz5Yu4PCbeV+lyMi4hmFQiOdPTKXmIDx+LwNfpciIuIZhUIj5aQmctbwXJ6Y9yllelSniLRRCoXD8M1ju1IVDPHmym1+lyIi4gmFwmEY0bU9ndISeXHpZr9LERHxhELhMAQCxjeGdGb26hIdQhKRNkmhcJhOH9qJ6tqQ7nAWkTZJoXCYRnRtT7fMJO57fTUV1eokT0TaFoXCYQoEjDvPGcLGnfuYuWST3+WIiDQphcIRGNO7A31zUnhi3qd+lyIi0qQ8DQUzO9XMVplZoZndfpD5zjMzZ2b5XtbTVMyMbx7bjSVFpSwrLvW7HBGRJuNZKJhZDPAQcBowCLjIzAY1MF8qcBMwz6tavHDOiDwSYgM8rtaCiLQhXrYURgGFzrm1zrlq4ClgcgPz/Qa4G6j0sJYml54Uxzkjc3luYRFb97Sq0kVEDsjLUMgFNtYbLoqMq2NmI4CuzrkXPazDM9ef0IfakOORd9b6XYqISJPwMhSsgXGubqJZAPgD8P1DrsjsGjMrMLOCkpKSJizxq+nWIYmzR+Ty+LwNlJRV+V2OiMhX5mUoFAFd6w3nAfWv4UwFBgNvm9l6YDQwo6GTzc65qc65fOdcfnZ2toclH74bJvShpjbEtHfVWhCR1s/LUJgP9DWznmYWD1wIzPhsonOu1DmX5Zzr4ZzrAcwFJjnnCjysqcn1zEpm8vBc/vnBBnaUq7UgIq2bZ6HgnAsCNwKzgJXAdOfccjO7w8wmebVdP9wwoQ+VwVr+Nmed36WIiHwlsV6u3Dn3MvDyfuN+cYB5T/SyFi/1yUnh9CGd+cf767lmfC8ykuL9LklE5IjojuYm8t2T+rK3upZ/fqAns4lI66VQaCL9O6Uyvl82j8/bQE1tyO9yRESOiEKhCV02pjtb91Tx8kd6CI+ItE4KhSZ0Yr8cBnRK5e5XV1FZU+t3OSIih02h0IQCAePnZwyiePc+np6/8dALiIi0MAqFJjamdwdG9cjk4bcL1VoQkVZHodDEzIxbTu7L1j1VTC9Qa0FEWheFggeO69WBwblpPL+o2O9SREQOi0LBA2bGyQM7sXjjbtZt3+t3OSIijaZQ8MiUY/JIS4zjhscXUhXUuQURaR0UCh7pnN6Ou88byorNe5heUOR3OSIijaJQ8NApgzoyolsGj7zzie5yFpFWQaHgITPjhhP7ULRrHzOXbDr0AiIiPlMoeOykAeG7nB9++xNCIXfoBUREfKRQ8FggYFx/Ym8Kt5Xz2oqtfpcjInJQCoVmcPqQznTvkMT9b67RuQURadEUCs0gNibAj08bwMrNe3jgf4V+lyMickAKhWZy6uDOnD0il4ffKmTVljK/yxERaVCjQsHMbjazNAv7m5ktNLNTvC6urfn5GYNISYzl5y8s00lnEWmRGttSuMI5twc4BcgGLgfu8qyqNiozOZ6ffGMgH67fyaPvrfO7HBGRL2lsKFjk5zeAvzvnltQbJ4fh/KPzmDiwI3fPWsXGnRV+lyMi8gWNDYUFZvYa4VCYZWapgC6jOQJmxm/OOgoD7nt9td/liIh8QWND4UrgduAY51wFEEf4EJIcgc7p7bhibE+eX1TMsuJSv8sREanT2FA4DljlnNttZt8Cfgbo0+wruP7E3rRPiuMHzyxhT2WN3+WIiACND4U/AxVmNgy4DdgA/NOzqqJAWmIcD1w0ksJt5Vz7zwVUB3U0TkT819hQCDrnHDAZuN85dz+Q6l1Z0WFs3yx+d+5QPli7Q1cjiUiL0NhQKDOzHwOXAC+ZWQzh8wryFZ0buRrpgTfXsHVPpd/liEiUa2woXABUEb5fYQuQC9zjWVVR5hdnDKIm5Lhj5grCDTIREX80KhQiQfA4kG5mZwCVzjmdU2gi3TokcfPX+vLSR5t57P31fpcjIlGssd1cTAE+BM4HpgDzzOw8LwuLNtef0JuTB3Xk/720kgUbdvpdjohEqcYePvop4XsULnXOfRsYBfzcu7KiTyBg/P78YXTJaMcNjy9iR3mV3yWJSBRqbCgEnHPb6g3vaMyyZnaqma0ys0Izu72B6deZ2UdmttjM5pjZoEbW0yalt4vj4YtHsrOimlueXqxO80Sk2TU2FF41s1lmdpmZXQa8BLx8sAUiVyg9BJwGDAIuauBD/wnn3BDn3HDgbuC+w6q+DRqcm84vzhjEu2u288jstX6XIyJRprEnmn8ITAWGAsOAqc65Hx1isVFAoXNurXOuGniK8H0O9de7p95gMqCvxsDFx3bj9CGd+d2rH3PWQ+9RtEsd54lI84ht7IzOueeA5w5j3bnAxnrDRcCx+89kZjcAtwLxwEmHsf42y8y4/8Lh9MhK4q/vruPqfy7giauOpX1yvN+liUgbd9CWgpmVmdmeBl5lZrbnYMvScNfaX2oJOOcecs71Bn5EuE+lhuq4xswKzKygpKTkEJttG2JjAvzw6wP488UjWbO1jAumfsDeqqDfZYlIG3fQUHDOpTrn0hp4pTrn0g6x7iKga73hPGDTQeZ/CjjrAHVMdc7lO+fys7OzD7HZtuVrAzvy6GXHULitnNueW6qb20TEU14+o3k+0NfMeppZPHAhMKP+DGbWt97g6cAaD+tptcb3y+a2Uwfw0tLNnPXw+3y85VCNNBGRI+NZKDjngsCNwCxgJTDdObfczO4ws0mR2W40s+VmtpjweYVLvaqntbt2fC/OGZHLko27mfKXD1iwYZffJYlIG2St7XBEfn6+Kygo8LsM3xTtquBb0+axdU8V/7hiFKN6Zvpdkoi0Ama2wDmXf6j5vDx8JB7Ia5/EM9eNoXNGItf/ewGbS/f5XZKItCEKhVYoOzWBqZfkU1lTy/X/XkhVsNbvkkSkjVAotFJ9clL4/fnDWLxxNzc8vkhPbhORJqFQaMVOG9KZn50+kDdWbuVXM5cTrFUwiMhX0+g7mqVlumpcLz4pKeeJeZ8SHxPg52cMIibQ0H2DIiKHplBoA+48ZyhmxmPvr2f2mhKeuGo0ndIT/S5LRFohHT5qI3515lH8etJRbC2t5PLH5lNSpucxiMjhUyi0EfGxAS4d04OHv3U067fv5bp/L6CyRlclicjhUSi0MSf0y+b35w9jwYZdTH7wPbbtqfS7JBFpRRQKbdDpQzvz12/ns3FXBRdOncvKzeorSUQaR6HQRp08qCOPXT6K8qogUx75gLlrd/hdkoi0AgqFNmxUz0z+850xZKUkcOHUufz4Px/5XZKItHAKhTYur30SM248nnNG5vLkh5/y+1mr/C5JRI5Acz2WV6EQBVIT4/jduUM57+g8HnyrkH99sN7vkkSkET7rxXpbWSXj736Lv81Z5/k2dfNalIiLCfB/Zw9hd0U1v5yxnKLd+5g4sCPH9FDX2yIt1U1PLWbmks8fWHlCvyzPt6mWQhSJjw3wxwtHMKF/Do+8s5Ypj3zAz174iPXb9/pdmojsZ83WsrpAGJybxjkjcumTk+r5dtVSiDIpCbH87bJjKK2o4dczl/Pkhxt55aMt/OvKYxnU5VCP3RaR5rJqaxkAL900lqO6pDfbdtVSiFLpSXHcd8FwXv/eeOJjA3xz2lyWFZf6XZaIROworwYgJ7V5+zFTKES5XtkpPHXNaJLiYrh42jzm6X4GkRZhx95qzKB9UlyzblehIHTvkMzT1x5HSkIsF0ydy/SCjX6XJBL1dpRXkdEujtiY5v2YVigIAF0zk3j55nEM6JTKbc8u5aG3Cv0uSSSq7dxbTYeUhGbfrkJB6qS3i+PZ68dwxtDO3DNrFdPeXet3SSJRa0d5NZnJ8c2+XV19JF+QkhDLfVOGUxty/PallSwtKuWOyUeRkdT8/zhFotmeyhq6ZiY1+3bVUpAviY8N8OA3R3LLxL68smwz37j/XWavLqm7u1JEvFcVDJEYF9Ps21UoSINiAsYtE/vxizOPYnt5Nd9+9EN+8vwyBYNIM6mqqSUhtvk/onX4SA7qktHdOf/oPO565WMee389NbUhfjXpKFIS9E9HxEvVtSGFgrRMiXEx/OKMQYSc419zNzBr2Rb+dNEIJgzI8bs0kTarqiZEQqwOH0kLFQgYd0wezBNXjSYlMZbLH5vPVf8o0HOgRTxSFQyRENf8H9EKBTksx/XuwMs3jeOHX+/PGyu38q1p81i+Sd1jiDSlUMj5dvhIoSCHrX1yPDdM6MO95w/jk5JyJj34Hr99cQUV1UG/SxNpE6prQ0D4SsDmplCQI3bu0Xm89YMTmZKfx7Q567j00Q/ZUlrpd1kirV5VTTgUdE5BWp2MpHjuPGco9184nCVFpZz75/fV26rIV1QVDJ+ra3OHj8zsVDNbZWaFZnZ7A9NvNbMVZrbUzN40s+5e1iPemTw8l+euG0NtyHHR1LkUbiv3uySRVqsq+FlLoQ2FgpnFAA8BpwGDgIvMbNB+sy0C8p1zQ4Fngbu9qke8NyQvnee+M4a42ABnPjCH255dwjurS9hcus/v0kRalbqWQhu7o3kUUOicW+ucqwaeAibXn8E595ZzriIyOBfI87AeaQa5Ge2Yfu1xTB7ehReXbubSRz/kxHveZvr8jdSGdDe0SGNU1rTBlgKQC9TvmL8oMu5ArgRe8bAeaSZ9clK469yhzPvJ13js8mMYmpfObc8t5bK/f1j3DUhEDqxNHj4CrIFxDX5VNLNvAfnAPQeYfo2ZFZhZQUlJSROWKF5KTYzjxP45PHn1aO6YfBTvrtnOlY8VsGtvtd+libRo1cG2efVREdC13nAesGn/mcxsIvBTYJJzrqqhFTnnpjrn8p1z+dnZ2Z4UK96JjQnw7eN6cPd5Q/lw3U4mPTSH9wq3+12WSIv1+TmFttVSmA/0NbOeZhYPXAjMqD+DmY0AHiEcCNs8rEVagCn5XXnq2tFUB0NcPG0eD7y5Rr2uijSgTR4+cs4FgRuBWcBKYLpzbrmZ3WFmkyKz3QOkAM+Y2WIzm3GA1UkbMbJbe/73/ROZNKwL976+mtP/NIfCbWV+lyXSovgZCp72kuqcexl4eb9xv6j3fqKX25eWKTkhlvumDOPYXpnc+9pqznzgPW47tT/j+mbRJyfV7/JEfBeMdHMRF9PGQkHkQGJjAlx8bHdOHtiR701fzK9nrgBgWF46J/TP4cJjutIlo53PVYr4oyYSCrEKBYk2OWmJ/PvKY/mouJT563fx3IIiHvzfGh6ds45bJvZl0vAu5KQm+l2mSLOqqQ2fa4sLNHQRp7cUCuI7M2NoXgZD8zK4cmxPNu6s4EfPLeW3L63kd69+TH73TI7p0Z7vTOjjyzNrRZpbjY+Hj9QhnrQ4XTOT+PeVx/LE1ccyeXgum0r38af/FXL2w++ztkR9KknbF4y0FGJj1FIQAcJPehvTO4sxvbMA+N/HW/n+9CWc/IfZTOifww+/3p9+HVMwa/7/NCJeqwnpRLPIQZ00oCOv3Dyex95fz+PzNvD1P85mSG46Jw3I4dIxPchMjve7RJEmUxOMnFPQ4SORA+uUnsjtpw3gjVtP4JaJfSmvCvLA/9Zwyh9m89/FxXVdA4i0dsFQiIBBjA8nmhUK0up0TEvklon9eOsHJ/LSTeNIaxfLzU8t5tT7Z/PWKt0YL61fTa3z5XJUUChIKzewcxov3zSOhy8eiXNw+d/nc8I9b/HComK/SxM5YjW1IV8uRwWdU5A2IDEuhm8M6czEgR15ZsFGphcUccvTi1lWXMrFo7vTMyvZ7xJFDkuwNkScD11cgFoK0obEx4bvkp5+7WjOHZnHtDnr+HrkfINIa1Jd64gNKBREmkRCbAz3ThnGu7dNYHi3DG5+ajHH3fkmLy79Us/tIi1SsDZEvA/3KIBCQdqwrplJ/OvKUfzyzEFkJsdz4xOLuOuVj9ldoYf8SMsWDOlEs4gnEmJjuPz4nvznO2OYkp/HX975hOPu/B/PLijyuzSRA6quDRGnloKIdxJiY7j7vGG8ess4hnfN4AfPLOHW6YsprajxuzSRLwnWhny5cQ0UChJlBnRK4++XH8O1J/Ri5pJNTH5oDsuKS/0uS+QLwvcpqKUg0iwS42L48WkDefLq0ZRX1XLmg3O4/4011Ib0aFBpGWrUUhBpfvk9Mnnj1vGcNTyXP7yxmov+OpeNOyv8LkuEYK0jTpekijS/jKR47psyjN+fP4wVm/Yw6UEdThL/1dSGiIvV4SMRX5gZ5x2dx8zvjiUpPpYLHvmAmUt0T4P4pyakm9dEfNczK5lnrz+O/p1S+e6Ti/j5C8soq9TVSdL8aoI6pyDSInROb8fT1x7HVWN78u95Gzjp3nf4x/vrCdaqW25pPsGQ7lMQaTHiYgL87IxBvPCd4+memcQvZyznin8UULpPrQZpHkF1nS3S8gzrmsGz14/hznOG8MEn2zn7ofeYuWRT3UPVRbxSFQwRr1AQaZkuGtWNx68aTWVNLd99chEXT5tHSVmV32VJG1ZRHSQ5IcaXbSsURBphVM9MZt82gXvOG8rSot1M1qWr4qGK6lqS4v153I1CQaSRYmMCnJ/flWevG0NNyHHmg3O47dklbCur9Ls0aUOCtSGqgiGS49VSEGkVBuem88atJ3D1uF48v6iYCfe8zb2vrVI4SJOoqKkFoJ1CQaT1SG8Xx0++MZDXv3cC4/pm8+BbhYy96y1++MwSlhWX6hJWOWIVVeFQSE7w5/CRntEs8hX0yErmL5cczdqScv7+3nqeWbCRZxYUkZYYS277JGIDxqiemYzrm8W4vtnEePww9tJ9Nby+Yiu9s5OJDQRITYwlMyWe7WVVVFTXsmdfDYGA0SktkazUBOJijIRYf76RSsP2VgcBSPKppaBQEGkCvbJT+M1Zg7n15H68+NFm5qwpoTbkKKsM8q+5G/jbnHV0TEtgbJ9sUhNj6ZKRSGpiHJOGdWmSb4Sf7qjgzldW8sqyLYe1XMBgTO8sJg7MYXy/bHpmJWPmz01TElbXUvDpRLOnWzWzU4H7gRhgmnPurv2mjwf+CAwFLnTOPetlPSJea58czyWju3PJ6O5146qDIV5fsZUXFhfz2vItBEOOfZHjxr/873KG5KUzaVgXBuem0Sm9HXExRmZSPGWVQeJiA6QcJDT2VgWZOnstD75VSG3IMbpXJlPyu5IYFxPeTnWQ3RU1ZCbH44BumUlUBUNs3VNJaUUNa7fv5YNPtvOrmSsA6JKeyI9OG8DYPll0SEnw9G8lDatrKfh0SapnoWBmMcBDwMlAETDfzGY451bUm+1T4DLgB17VIeK3+NgApw/tzOlDOwPhkNhbFWTt9r28tmILb39cwi9nLD/g8gM6pTJpeBdyM9rRITmB0n01LCnazYINu1j46S6cg15ZyfzpohEMzk0/ohoLt5WzYMNOHnt/Azc/tRiAEd0yGNUjkwkDcujXMZXM5PgjWrccnopIKLTFlsIooNA5txbAzJ4CJgN1oeCcWx+ZprNyEjXiYwPEx8ZzdHI8R3dvz+2nDqBo1z7WbCtj0+5KakOObWWVdEhOYFdFNe+sLuHuV1d9aT3t4mI4e0Quo3t14NTBnUhLjDvimvrkpNAnJ4VzRuYxf/1OFm7YxesrtvLoe+t4ZPZaAMb2yeKKsT3o2j6JXtkpnp8fiVZ76040t7GWApALbKw3XAQc6+H2RFolM6NrZhJdM5ManP79U/pTuK2cmtoQm0v3sWFHBcf27MDAzqlNfvw/LibAmN5ZjOmdxY0n9WV3RTWLNu5mWVEpf313LVc8VlA3b5+cFIbkpjOubxZj+2SRk5bYpLVEI+ccf3xjNYBvN695udWG/rUe0fMOzewa4BqAbt26fZWaRFqlPjkpAAzsnNas281IimdC/xwm9M/hynE9WVa8h7Ul5Sz8dBcbd+7j3TXbeX5RMQDDu2Zwfn4em3bvIyk+lkGd08ht346eWcm+dQPd2sxes51PSvZy3tF5dE73J2S9DIUioGu94TzgiJ5c4pybCkwFyM/P14N0RXyQFB/LqJ6ZjOqZyYWjwl/OgrUh3lldQuG2cp6av5GfPr/sS8vFxwbISU0gN6Md/Tul0q9jKif2zyavfcMto2j16Y4Kbn16MR3TEvi/s4f4dhWYl6EwH+hrZj2BYuBC4Jsebk9EmllsTICvDezI1wZ25OpxvVi5ZQ+79tYwoHMqy4pLKSmrYs22crbtqWTjrn08v7CYsqogZjCmdwe6d0imY2oi7ZPjGNUzk745qRTtqiC9XRwZSdFzYrumNsTV/yxgx95q/t/Zg4mP9a9l5VkoOOeCZnYjMIvwJamPOueWm9kdQIFzboaZHQM8D7QHzjSzXzvnjvKqJhHxTiBgHNXl86ufTuyf86V5nHNs2FHB84uKeX5RMUuLSimrDDa4vrTEWLp1SKJnVgoju2VwylGdyM1o1+h6QiFHIGDsrQqycVcFm3dX8vT8jSwt2s34ftkc1SV8eGtUzw51l/2W7quhNuRonxTn2Tf1DTv2khgXQ8g55q3dyZ7KGt5ZVcKqrWVMveRoTjmqkyfbbSxzrnUdjcnPz3cFBQWHnlFEWjznHFXBECVlVcxdu4NXlm3h+D5ZdeHx6c4KCreVU7x7HxA+bzFxYA5HdUlnSF46WfvdS7GnsoZ7Z61i5eYylm0qpToYIhj6/DMuKT6GjHZxlFUGKasKh1F8TICslHgS42NYt30vzoW7MemdncyAzmmM6pHJyYM6fuWbDNdv38vvXv24wRsM42MDXDm2Jz86dcBX2sbBmNkC51z+IedTKIhIS7d++15e+mgzry3fwpKicJflMQHjtMGdGN8vm9VbylixeQ8fFYdbHmbhS2j7dUwFwmESEzC+NjCHhNgYakOOHXurKNxazjurSygpr2Ln3mpGdG1PSmIsa0vKWb5pD6u2lLGvppZ2cTGcOrhT3RViXz+qI/07pjbq6WiVNbU8PX8j98xaRVWwlktG96BbZjsCAWNYXgbxsQH65qR4/qQ1hYKItEm7K6pZWlTKnMIEsRfrAAAI30lEQVTtPPnhp3WHnwbnptG/YxqThndhRLeMr3TfxmdCIUfBhl08v6iYF5du+sKhroTYAAM6pzG4SxpDctMZnJtO344pJMTGsLl0Hx9vLmPm0k18uG4nRbv2kZkczzPXHUfv7JSvXNeRUCiISJtXHQyxpbSSYChEL48/bGsjh6G2l4cPdS0rLmVZ8R6Wbfr8vEhcjNG/UyqfbNvLvppaEuMC9OuYytXjenHyoI4kxvnX+aBCQUSkGYRCjo27KlhWvIelxbspWL+LTmmJnDmsM8f27ED7FtI9SGNDQb2kioh8BYGA0b1DMt07JNf1b9Wa6TZDERGpo1AQEZE6CgUREamjUBARkToKBRERqaNQEBGROgoFERGpo1AQEZE6re6OZjMrATYcYrZ0oPQwV92YZQ41z8GmH2haQ+P3H5cFbD9EbV44kr9jU6xD++LLtC+0L77qvujunMs+5Facc23uBUz1YplDzXOw6Qea1tD4/ccRfv5Eq/g7al9oX2hftNx90ZhXWz18NNOjZQ41z8GmH2haQ+OPpH4vNEUd2hdNQ/tC+6Ip9sUhtbrDR9HIzApcIzqyEu9pX7Qc2hfeaKsthbZmqt8FSB3ti5ZD+8IDaimIiEgdtRRERKSOQkFEROooFEREpI5CoQ0ws2QzW2BmZ/hdSzQzs4Fm9hcze9bMrve7nmhmZmeZ2V/N7L9mdorf9bQmCgUfmdmjZrbNzJbtN/5UM1tlZoVmdnsjVvUjYLo3VUaHptgXzrmVzrnrgCmALpU8Qk20L15wzl0NXAZc4GG5bY6uPvKRmY0HyoF/OucGR8bFAKuBk4EiYD5wERAD3LnfKq4AhhK+3T8R2O6ce7F5qm9bmmJfOOe2mdkk4HbgQefcE81Vf1vSVPsisty9wOPOuYXNVH6rF+t3AdHMOTfbzHrsN3oUUOicWwtgZk8Bk51zdwJfOjxkZhOAZGAQsM/MXnbOhTwtvA1qin0RWc8MYIaZvQQoFI5AE/2/MOAu4BUFwuFRKLQ8ucDGesNFwLEHmtk591MAM7uMcEtBgdB0DmtfmNmJwDlAAvCyp5VFn8PaF8B3gYlAupn1cc79xcvi2hKFQstjDYw75DE+59xjTV9K1DusfeGcext426tiotzh7os/AX/yrpy2SyeaW54ioGu94Txgk0+1RDvti5ZD+6KZKBRanvlAXzPraWbxwIXADJ9rilbaFy2H9kUzUSj4yMyeBD4A+ptZkZld6ZwLAjcCs4CVwHTn3HI/64wG2hcth/aFv3RJqoiI1FFLQURE6igURESkjkJBRETqKBRERKSOQkFEROooFEREpI5CQTxnZuXNsI1JjexmvCm3eaKZjTmC5UaY2bTI+8vM7MGmr+7wmVmP/burbmCebDN7tblqkuanUJBWI9J9coOcczOcc3d5sM2D9Q92InDYoQD8BHjgiArymXOuBNhsZsf7XYt4Q6EgzcrMfmhm881sqZn9ut74FyJPj1tuZtfUG19uZneY2TzgODNbb2a/NrOFZvaRmQ2IzFf3jdvMHjOzP5nZ+2a21szOi4wPmNnDkW28aGYvfzZtvxrfNrP/M7N3gJvN7Ewzm2dmi8zsDTPrGOna+Trge2a22MzGRb5FPxf5/eY39MFpZqnAUOfckgamdTezNyN/mzfNrFtkfG8zmxtZ5x0Ntbws/PS9l8xsiZktM7MLIuOPifwdlpjZh2aWGmkRvBv5Gy5sqLVjZjFmdk+9fXVtvckvABc3uIOl9XPO6aWXpy+gPPLzFGAq4R4vA8CLwPjItMzIz3bAMqBDZNgBU+qtaz3w3cj77wDTIu8vI/xgG4DHgGci2xhEuB9+gPMId2kdADoBu4DzGqj3beDhesPt+fzu/6uAeyPvfwX8oN58TwBjI++7ASsbWPcE4Ll6w/XrnglcGnl/BfBC5P2LwEWR99d99vfcb73nAn+tN5wOxANrgWMi49II94ycBCRGxvUFCiLvewDLIu+vAX4WeZ8AFAA9I8O5wEd+/7vSy5uXus6W5nRK5LUoMpxC+ENpNnCTmZ0dGd81Mn4HUAs8t996/hP5uYDw8wsa8oILP1tihZl1jIwbCzwTGb/FzN46SK1P13ufBzxtZp0Jf9CuO8AyE4FB4ee7AJBmZqnOubJ683QGSg6w/HH1fp9/AXfXG39W5P0TwO8bWPYj4Pdm9jvgRefcu2Y2BNjsnJsP4JzbA+FWBfCgmQ0n/Pft18D6TgGG1mtJpRPeJ+uAbUCXA/wO0sopFKQ5GXCnc+6RL4wMP5xmInCcc67CzN4m/HhRgErnXO1+66mK/KzlwP+Gq+q9t/1+Nsbeeu8fAO5zzs2I1PqrAywTIPw77DvIevfx+e92KI3umMw5t9rMjga+AdxpZq8RPszT0Dq+B2wFhkVqrmxgHiPcIpvVwLREwr+HtEE6pyDNaRZwhZmlAJhZrpnlEP4WuisSCAOA0R5tfw5wbuTcQkfCJ4obIx0ojry/tN74MiC13vBrhHvyBCDyTXx/K4E+B9jO+4S7hIbwMfs5kfdzCR8eot70LzCzLkCFc+7fhFsSI4GPgS5mdkxkntTIifN0wi2IEHAJ4ecc728WcL2ZxUWW7RdpYUC4ZXHQq5Sk9VIoSLNxzr1G+PDHB2b2EfAs4Q/VV4FYM1sK/Ibwh6AXniP8sJZlwCPAPKC0Ecv9CnjGzN4FttcbPxM4+7MTzcBNQH7kxOwKwsf/v8A59zHhR0Sm7j8tsvzlkb/DJcDNkfG3ALea2YeEDz81VPMQ4EMzWwz8FPitc64auAB4wMyWAK8T/pb/MHCpmc0l/AG/t4H1TQNWAAsjl6k+wuetsgnASw0sI22Aus6WqGJmKc65cjPrAHwIHO+c29LMNXwPKHPOTWvk/EnAPuecM7MLCZ90nuxpkQevZzYw2Tm3y68axDs6pyDR5kUzyyB8wvg3zR0IEX8Gzj+M+Y8mfGLYgN2Er0zyhZllEz6/okBoo9RSEBGROjqnICIidRQKIiJSR6EgIiJ1FAoiIlJHoSAiInUUCiIiUuf/AzwqH9MZcgijAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "m.sched.plot(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = md.get_learner(emb_szs, len(df.columns) - len(cat_vars),\n",
    "                  0.04, 1, [1000, 500], [0.001, 0.01], y_range = y_range)\n",
    "lr = 1e-3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d0cc7e3aac7d4c2a8dab64177a2f0d4c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=3), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                       \n",
      "    0      0.014089   0.021295   0.129942  \n",
      "    1      0.011806   0.016502   0.120824                        \n",
      "    2      0.010203   0.015588   0.119857                         \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.01559]), 0.1198567494047878]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 3, metrics=[exp_rmspe])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3c6f940c85dc4b15ae5ad15b68c80bb6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=5), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                       \n",
      "    0      0.010296   0.013474   0.112145  \n",
      "    1      0.009513   0.012977   0.11214                          \n",
      "    2      0.009342   0.013095   0.111908                         \n",
      "    3      0.009306   0.013023   0.112649                         \n",
      "    4      0.008682   0.013234   0.114345                         \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.01323]), 0.11434512689714119]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 5, metrics=[exp_rmspe], cycle_len=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3e75eee13bdc4c0bb864d1734c012546",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=8), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                       \n",
      "    0      0.010718   0.014173   0.115072  \n",
      "    1      0.008873   0.013927   0.118267                         \n",
      "    2      0.007339   0.012774   0.113778                         \n",
      "    3      0.006558   0.012617   0.112438                         \n",
      "    4      0.01032    0.019577   0.153089                         \n",
      "    5      0.007801   0.013147   0.118725                         \n",
      "    6      0.006369   0.012889   0.11408                          \n",
      "    7      0.005997   0.012658   0.113588                         \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.01266]), 0.11358757128211414]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 2, metrics=[exp_rmspe], cycle_len=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = md.get_learner(emb_szs, len(df.columns) - len(cat_vars),\n",
    "                  0.04, 1, [1000, 500], [0.001, 0.01], y_range=y_range)\n",
    "lr = 1e-3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'module' object is not subscriptable",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-61-55ddffea7a04>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mm\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmetrics\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mexp_rmspe\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m: 'module' object is not subscriptable"
     ]
    }
   ],
   "source": [
    "m.fit(lr, 1, metrics=[exp_rmspe])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4a34f28c5aa64e5ba74b7ca9306379f7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=3), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                        \n",
      "    0      0.009813   0.017133   0.124261  \n",
      "    1      0.008248   0.015283   0.121715                         \n",
      "    2      0.008776   0.015498   0.121297                         \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.0155]), 0.12129690642782659]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 3, metrics=[exp_rmspe])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e920059a03734eefb12833763b96beee",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=3), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                        \n",
      "    0      0.006108   0.013962   0.113754  \n",
      "    1      0.006336   0.013518   0.111646                         \n",
      "    2      0.006919   0.013953   0.1134                           \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.01395]), 0.11340041852298481]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 3, metrics=[exp_rmspe], cycle_len=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = md.get_learner(emb_szs, len(df.columns) - len(cat_vars),\n",
    "                  0.04, 1, [1000,500], [0.001,0.01], y_range=y_range)\n",
    "lr = 1e-3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1b32dafc564e4552a3b7d828ac3e9d4f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=3), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                       \n",
      "    0      0.014698   0.014288   0.113555  \n",
      "    1      0.012173   0.014629   0.125514                        \n",
      "    2      0.009847   0.012786   0.114577                         \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.01279]), 0.11457740156719856]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 3, metrics=[exp_rmspe])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "28926e55d09743dfbee3124e438a6cc8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, description='Epoch', max=3), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "epoch      trn_loss   val_loss   exp_rmspe                        \n",
      "    0      0.007137   0.011044   0.102224  \n",
      "    1      0.007191   0.010912   0.101248                         \n",
      "    2      0.007305   0.010799   0.100987                         \n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[array([0.0108]), 0.10098699530221293]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m.fit(lr, 3, metrics=[exp_rmspe], cycle_len=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "m.save('val0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "m.load('val0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "x,y = m.predict_with_targs()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10428997777785391"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_rmspe(x,y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_test=m.predict(True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_test = np.exp(pred_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_test['Sales'] = pred_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_fn=f'{PATH}tmp/sub.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_test[['Id','Sales']].to_csv(csv_fn, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<a href='data/tmp/sub.csv' target='_blank'>data/tmp/sub.csv</a><br>"
      ],
      "text/plain": [
       "/home/paperspace/kaggle/competitions/rossmann-store-sales/data/tmp/sub.csv"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "FileLink(csv_fn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "((val,trn), (y_val,y_trn)) = split_by_idx(val_idx, df.values, yl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "m = RandomForestRegressor(n_estimators=40, max_features=0.99, min_samples_leaf=2,\n",
    "                          n_jobs=-1, oob_score=True)\n",
    "m.fit(trn, y_trn);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.9797295699030157,\n",
       " 0.9228527263699181,\n",
       " 0.9149876946486658,\n",
       " 0.11937084479998575)"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "preds = m.predict(val)\n",
    "m.score(trn, y_trn), m.score(val, y_val), m.oob_score_, exp_rmspe(preds, y_val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}