rossmann-sales-prediction/data-preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext autoreload\n",
    "%autoreload 2\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PosixPath('data')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = Path('data')\n",
    "path.mkdir(parents=True, exist_ok=True)\n",
    "path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!wget http://files.fast.ai/part2/lesson14/rossmann.tgz -P {path}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!tar -xvzf {path}/rossmann.tgz -C {path}\n",
    "#!rm {path}/rossmann.tgz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!rm {path}/sample_submission.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PosixPath('data/googletrend.csv'),\n",
       " PosixPath('data/state_names.csv'),\n",
       " PosixPath('data/store.csv'),\n",
       " PosixPath('data/store_states.csv'),\n",
       " PosixPath('data/test.csv'),\n",
       " PosixPath('data/train.csv'),\n",
       " PosixPath('data/weather.csv')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_paths = sorted(list(path.glob('*.csv')))\n",
    "table_paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = [pd.read_csv(table_path, low_memory=False) for table_path in table_paths]\n",
    "googletrend_df, state_names_df, store_df, store_states_df, test_df, train_df, weather_df = tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Train and test data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1017209, 9), (41088, 8))"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2013-01-01 00:00:00'), Timestamp('2015-07-31 00:00:00'))"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df['Date'] = pd.to_datetime(train_df.Date)\n",
    "\n",
    "train_df.Date.min(), train_df.Date.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2015-08-01 00:00:00'), Timestamp('2015-09-17 00:00:00'))"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df['Date'] = pd.to_datetime(test_df.Date)\n",
    "\n",
    "test_df.Date.min(), test_df.Date.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Customers', 'Sales'], dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.columns.difference(test_df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_df.drop('Id', axis=1, inplace=True)\n",
    "test_df['Customers'] = np.nan\n",
    "test_df['Sales'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2013-01-01 00:00:00'), Timestamp('2015-09-17 00:00:00'))"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge train and test to apply preprocessors on both of them at time\n",
    "df = train_df.append(test_df, sort=False, ignore_index=True, verify_integrity=True)\n",
    "\n",
    "df.Date.min(), df.Date.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 0 and 1 to boolean\n",
    "df['Open'] = df['Open'] != 0\n",
    "df['Promo'] = df['Promo'] != 0\n",
    "df['StateHoliday'] = df['StateHoliday'] != '0' # object\n",
    "df['SchoolHoliday'] = df['SchoolHoliday'] != 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def add_datepart(df):\n",
    "    # Decompose date column into various parts for the purpose of constructing categoricals\n",
    "    \n",
    "    date_col = df['Date']\n",
    "    targ_pre = re.sub('[Dd]ate$', '', 'Date')\n",
    "    attr = ['Year', 'Month', 'Week', 'Day', 'Dayofweek', 'Dayofyear',\n",
    "            'Is_month_end', 'Is_month_start', 'Is_quarter_end', \n",
    "            'Is_quarter_start', 'Is_year_end', 'Is_year_start']\n",
    "    for n in attr: \n",
    "        df[targ_pre + n] = getattr(date_col.dt, n.lower())\n",
    "    df[targ_pre + 'Elapsed'] = date_col.astype(np.int64) // 10 ** 9\n",
    "    \n",
    "add_datepart(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('DayOfWeek', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Store                                 1\n",
       "Date                2014-12-31 00:00:00\n",
       "Sales                              2605\n",
       "Customers                           327\n",
       "Open                               True\n",
       "Promo                             False\n",
       "StateHoliday                      False\n",
       "SchoolHoliday                      True\n",
       "Year                               2014\n",
       "Month                                12\n",
       "Week                                  1\n",
       "Day                                  31\n",
       "Dayofweek                             2\n",
       "Dayofyear                           365\n",
       "Is_month_end                       True\n",
       "Is_month_start                    False\n",
       "Is_quarter_end                     True\n",
       "Is_quarter_start                  False\n",
       "Is_year_end                        True\n",
       "Is_year_start                     False\n",
       "Elapsed                      1419984000\n",
       "Name: 236380, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query('Date==20141231').iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_columns = df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Store data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def left_outer_join(left, right, left_on, right_on=None, suffixes=('', '_y')):\n",
    "    # Left outer join which makes unmatched values on the right side zero\n",
    "    # The suffixes argument describes the naming convention for duplicate fields\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, suffixes=suffixes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Populate stores with state name\n",
    "store_df = left_outer_join(store_df, store_states_df, \"Store\")\n",
    "\n",
    "store_df.State.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "store_df['Promo2'] = store_df['Promo2'] != 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge sales data with store information\n",
    "df = left_outer_join(df, store_df, \"Store\")\n",
    "\n",
    "df.StoreType.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Assortment                       a\n",
       "CompetitionDistance           1270\n",
       "CompetitionOpenSinceMonth        9\n",
       "CompetitionOpenSinceYear      2008\n",
       "Promo2                       False\n",
       "Promo2SinceWeek                NaN\n",
       "Promo2SinceYear                NaN\n",
       "PromoInterval                  NaN\n",
       "State                           HE\n",
       "StoreType                        c\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.columns.difference(base_columns)].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_columns = df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Google Trends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['HE', 'TH', 'NW', 'BE', 'SN', 'SH', 'HB,NI', 'BY', 'BW', 'RP',\n",
       "       'ST', 'HH'], dtype=object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "store_states_df['State'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract date and state for merging purposes\n",
    "googletrend_df['Date'] = googletrend_df.week.str.split(' - ', expand=True)[0]\n",
    "googletrend_df['State'] = googletrend_df.file.str.split('_', expand=True)[2]\n",
    "\n",
    "# Match the rest of the data\n",
    "googletrend_df.loc[googletrend_df.State=='NI', \"State\"] = 'HB,NI'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "googletrend_df['Date'] = pd.to_datetime(googletrend_df['Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "add_datepart(googletrend_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge with google trends across states\n",
    "df = left_outer_join(df, googletrend_df, [\"State\", \"Year\", \"Week\"])\n",
    "\n",
    "df.trend.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge with google trends across Germany\n",
    "trend_de_df = googletrend_df[googletrend_df.file == 'Rossmann_DE']\n",
    "df = left_outer_join(df, trend_de_df, [\"Year\", \"Week\"], suffixes=('', '_DE'))\n",
    "\n",
    "df.trend_DE.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove redundant columns\n",
    "for c in df.columns:\n",
    "    if c in ['file', 'week'] or c.endswith('_y') or (c.endswith('_DE') and c != 'trend_DE'):\n",
    "        if c in df.columns: df.drop(c, inplace=True, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trend       85\n",
       "trend_DE    83\n",
       "Name: 0, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.columns.difference(base_columns)].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_columns = df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Weather data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather_df = left_outer_join(weather_df, state_names_df, \"file\", \"StateName\")\n",
    "\n",
    "weather_df.StateName.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_df['Date'] = pd.to_datetime(weather_df.Date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge with weather data\n",
    "df = left_outer_join(df, weather_df, [\"State\", \"Date\"])\n",
    "\n",
    "df.Mean_TemperatureC.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['file', 'StateName'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CloudCover                       1\n",
       "Dew_PointC                       9\n",
       "Events                         Fog\n",
       "Max_Gust_SpeedKm_h             NaN\n",
       "Max_Humidity                    98\n",
       "Max_Sea_Level_PressurehPa     1021\n",
       "Max_TemperatureC                23\n",
       "Max_VisibilityKm                31\n",
       "Max_Wind_SpeedKm_h              24\n",
       "MeanDew_PointC                   6\n",
       "Mean_Humidity                   54\n",
       "Mean_Sea_Level_PressurehPa    1018\n",
       "Mean_TemperatureC               16\n",
       "Mean_VisibilityKm               15\n",
       "Mean_Wind_SpeedKm_h             11\n",
       "Min_DewpointC                    3\n",
       "Min_Humidity                    18\n",
       "Min_Sea_Level_PressurehPa     1015\n",
       "Min_TemperatureC                 8\n",
       "Min_VisibilitykM                10\n",
       "Precipitationmm                  0\n",
       "WindDirDegrees                  13\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.columns.difference(base_columns)].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_columns = df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Promo and competition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pick an arbitrary signal value that doesn't otherwise appear in the data\n",
    "df['Promo2SinceYear'] = df.Promo2SinceYear.fillna(1900).astype(np.int32)\n",
    "df['Promo2SinceWeek'] = df.Promo2SinceWeek.fillna(1).astype(np.int32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "from isoweek import Week\n",
    "\n",
    "df[\"Promo2Since\"] = pd.to_datetime(df.apply(lambda x: Week(x.Promo2SinceYear, x.Promo2SinceWeek).monday(), axis=1))\n",
    "\n",
    "df[\"Promo2Days\"] = df.Date.subtract(df[\"Promo2Since\"]).dt.days\n",
    "df.loc[df.Promo2Days<0, \"Promo2Days\"] = 0\n",
    "df.loc[df.Promo2SinceYear<1990, \"Promo2Days\"] = 0\n",
    "\n",
    "df[\"Promo2Weeks\"] = df[\"Promo2Days\"] // 7\n",
    "df.loc[df.Promo2Weeks<0, \"Promo2Weeks\"] = 0\n",
    "df.loc[df.Promo2Weeks>25, \"Promo2Weeks\"] = 25 # limit the number of categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CompetitionOpenSinceYear'] = df.CompetitionOpenSinceYear.fillna(1900).astype(np.int32)\n",
    "df['CompetitionOpenSinceMonth'] = df.CompetitionOpenSinceMonth.fillna(1).astype(np.int32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Populate some temporal columns related to competition\n",
    "df[\"CompetitionOpenSince\"] = pd.to_datetime(dict(year=df.CompetitionOpenSinceYear, \n",
    "                                                 month=df.CompetitionOpenSinceMonth, day=15))\n",
    "\n",
    "df[\"CompetitionDaysOpen\"] = df.Date.subtract(df.CompetitionOpenSince).dt.days\n",
    "df.loc[df.CompetitionOpenSinceYear<1990, \"CompetitionDaysOpen\"] = 0\n",
    "df.loc[df.CompetitionDaysOpen<0, \"CompetitionDaysOpen\"] = 0\n",
    "\n",
    "df[\"CompetitionMonthsOpen\"] = df[\"CompetitionDaysOpen\"] // 30\n",
    "df.loc[df.CompetitionMonthsOpen<0, \"CompetitionMonthsOpen\"] = 0\n",
    "df.loc[df.CompetitionMonthsOpen>24, \"CompetitionMonthsOpen\"] = 24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CompetitionDaysOpen                     2510\n",
       "CompetitionMonthsOpen                     24\n",
       "CompetitionOpenSince     2008-09-15 00:00:00\n",
       "Promo2Days                                 0\n",
       "Promo2Since              1900-01-01 00:00:00\n",
       "Promo2Weeks                                0\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.columns.difference(base_columns)].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_columns = df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Duration between events"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "def set_elapsed(df, fld, pre):\n",
    "    # Given a particular boolean field fld to monitor, \n",
    "    # this function will start tracking time since the last occurrence of that field\n",
    "    \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(abs((d-last_date).astype('timedelta64[D]') / day1))\n",
    "    df[pre+fld] = res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a temporary dataframe with relevant data\n",
    "on_columns = [\"Date\", \"Store\", \"Open\", \"Promo\", \"StateHoliday\", \"SchoolHoliday\"]\n",
    "\n",
    "elapsed_df = df[on_columns].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get durations of events\n",
    "elapsed_df['Closed'] = ~elapsed_df['Open']\n",
    "event_flds = ['Closed', 'SchoolHoliday', 'StateHoliday', 'Promo']\n",
    "\n",
    "for fld in event_flds:\n",
    "    elapsed_df.sort_values(['Store', 'Date'], inplace=True)\n",
    "    set_elapsed(elapsed_df, fld, 'After')\n",
    "    elapsed_df.sort_values(['Store', 'Date'], ascending=[True, False], inplace=True)\n",
    "    set_elapsed(elapsed_df, fld, 'Before')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "elapsed_df.set_index(\"Date\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace NaNs with 0\n",
    "for suffix in event_flds:\n",
    "    for prefix in ['Before', 'After']:\n",
    "        column = prefix + suffix\n",
    "        elapsed_df[column] = elapsed_df[column].fillna(0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the number of events in the previous 7 days\n",
    "bwd_df = elapsed_df[['Store']+event_flds].sort_index().groupby(\"Store\").rolling(7, min_periods=1).sum()\n",
    "# _ next 7 days\n",
    "fwd_df = elapsed_df[['Store']+event_flds].sort_index(ascending=False).groupby(\"Store\").rolling(7, min_periods=1).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop index\n",
    "bwd_df.drop('Store', axis=1, inplace=True)\n",
    "bwd_df.reset_index(inplace=True)\n",
    "fwd_df.drop('Store', axis=1, inplace=True)\n",
    "fwd_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Store', 'Date', 'Closed', 'SchoolHoliday', 'StateHoliday', 'Promo'], dtype='object')"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bwd_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Store', 'Date', 'Closed', 'SchoolHoliday', 'StateHoliday', 'Promo'], dtype='object')"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fwd_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Store', 'Open', 'Promo', 'StateHoliday', 'SchoolHoliday', 'Closed',\n",
       "       'AfterClosed', 'BeforeClosed', 'AfterSchoolHoliday',\n",
       "       'BeforeSchoolHoliday', 'AfterStateHoliday', 'BeforeStateHoliday',\n",
       "       'AfterPromo', 'BeforePromo'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elapsed_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "elapsed_df = left_outer_join(elapsed_df, bwd_df, ['Date', 'Store'], suffixes=['', '_bw'])\n",
    "elapsed_df = left_outer_join(elapsed_df, fwd_df, ['Date', 'Store'], suffixes=['', '_fw'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>989</th>\n",
       "      <th>988</th>\n",
       "      <th>987</th>\n",
       "      <th>986</th>\n",
       "      <th>985</th>\n",
       "      <th>984</th>\n",
       "      <th>983</th>\n",
       "      <th>982</th>\n",
       "      <th>981</th>\n",
       "      <th>980</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>2013-01-01 00:00:00</td>\n",
       "      <td>2013-01-02 00:00:00</td>\n",
       "      <td>2013-01-03 00:00:00</td>\n",
       "      <td>2013-01-04 00:00:00</td>\n",
       "      <td>2013-01-05 00:00:00</td>\n",
       "      <td>2013-01-06 00:00:00</td>\n",
       "      <td>2013-01-07 00:00:00</td>\n",
       "      <td>2013-01-08 00:00:00</td>\n",
       "      <td>2013-01-09 00:00:00</td>\n",
       "      <td>2013-01-10 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Store</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\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>Open</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo</th>\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>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday</th>\n",
       "      <td>True</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>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\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>Closed</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterClosed</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\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>BeforeClosed</th>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterSchoolHoliday</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforeSchoolHoliday</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterStateHoliday</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforeStateHoliday</th>\n",
       "      <td>0</td>\n",
       "      <td>86</td>\n",
       "      <td>85</td>\n",
       "      <td>84</td>\n",
       "      <td>83</td>\n",
       "      <td>82</td>\n",
       "      <td>81</td>\n",
       "      <td>80</td>\n",
       "      <td>79</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterPromo</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforePromo</th>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Closed_bw</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SchoolHoliday_bw</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday_bw</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo_bw</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\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>Closed_fw</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\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>SchoolHoliday_fw</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday_fw</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo_fw</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     989                  988  \\\n",
       "Date                 2013-01-01 00:00:00  2013-01-02 00:00:00   \n",
       "Store                                  1                    1   \n",
       "Open                               False                 True   \n",
       "Promo                              False                False   \n",
       "StateHoliday                        True                False   \n",
       "SchoolHoliday                       True                 True   \n",
       "Closed                              True                False   \n",
       "AfterClosed                            0                    1   \n",
       "BeforeClosed                           0                    4   \n",
       "AfterSchoolHoliday                     0                    0   \n",
       "BeforeSchoolHoliday                    0                    0   \n",
       "AfterStateHoliday                      0                    1   \n",
       "BeforeStateHoliday                     0                   86   \n",
       "AfterPromo                             0                    0   \n",
       "BeforePromo                            6                    5   \n",
       "Closed_bw                              1                    1   \n",
       "SchoolHoliday_bw                       1                    2   \n",
       "StateHoliday_bw                        1                    1   \n",
       "Promo_bw                               0                    0   \n",
       "Closed_fw                              2                    1   \n",
       "SchoolHoliday_fw                       7                    7   \n",
       "StateHoliday_fw                        1                    0   \n",
       "Promo_fw                               1                    2   \n",
       "\n",
       "                                     987                  986  \\\n",
       "Date                 2013-01-03 00:00:00  2013-01-04 00:00:00   \n",
       "Store                                  1                    1   \n",
       "Open                                True                 True   \n",
       "Promo                              False                False   \n",
       "StateHoliday                       False                False   \n",
       "SchoolHoliday                       True                 True   \n",
       "Closed                             False                False   \n",
       "AfterClosed                            2                    3   \n",
       "BeforeClosed                           3                    2   \n",
       "AfterSchoolHoliday                     0                    0   \n",
       "BeforeSchoolHoliday                    0                    0   \n",
       "AfterStateHoliday                      2                    3   \n",
       "BeforeStateHoliday                    85                   84   \n",
       "AfterPromo                             0                    0   \n",
       "BeforePromo                            4                    3   \n",
       "Closed_bw                              1                    1   \n",
       "SchoolHoliday_bw                       3                    4   \n",
       "StateHoliday_bw                        1                    1   \n",
       "Promo_bw                               0                    0   \n",
       "Closed_fw                              1                    1   \n",
       "SchoolHoliday_fw                       7                    7   \n",
       "StateHoliday_fw                        0                    0   \n",
       "Promo_fw                               3                    4   \n",
       "\n",
       "                                     985                  984  \\\n",
       "Date                 2013-01-05 00:00:00  2013-01-06 00:00:00   \n",
       "Store                                  1                    1   \n",
       "Open                                True                False   \n",
       "Promo                              False                False   \n",
       "StateHoliday                       False                False   \n",
       "SchoolHoliday                       True                 True   \n",
       "Closed                             False                 True   \n",
       "AfterClosed                            4                    0   \n",
       "BeforeClosed                           1                    0   \n",
       "AfterSchoolHoliday                     0                    0   \n",
       "BeforeSchoolHoliday                    0                    0   \n",
       "AfterStateHoliday                      4                    5   \n",
       "BeforeStateHoliday                    83                   82   \n",
       "AfterPromo                             0                    0   \n",
       "BeforePromo                            2                    1   \n",
       "Closed_bw                              1                    2   \n",
       "SchoolHoliday_bw                       5                    6   \n",
       "StateHoliday_bw                        1                    1   \n",
       "Promo_bw                               0                    0   \n",
       "Closed_fw                              1                    1   \n",
       "SchoolHoliday_fw                       7                    6   \n",
       "StateHoliday_fw                        0                    0   \n",
       "Promo_fw                               5                    5   \n",
       "\n",
       "                                     983                  982  \\\n",
       "Date                 2013-01-07 00:00:00  2013-01-08 00:00:00   \n",
       "Store                                  1                    1   \n",
       "Open                                True                 True   \n",
       "Promo                               True                 True   \n",
       "StateHoliday                       False                False   \n",
       "SchoolHoliday                       True                 True   \n",
       "Closed                             False                False   \n",
       "AfterClosed                            1                    2   \n",
       "BeforeClosed                           6                    5   \n",
       "AfterSchoolHoliday                     0                    0   \n",
       "BeforeSchoolHoliday                    0                    0   \n",
       "AfterStateHoliday                      6                    7   \n",
       "BeforeStateHoliday                    81                   80   \n",
       "AfterPromo                             0                    0   \n",
       "BeforePromo                            0                    0   \n",
       "Closed_bw                              2                    1   \n",
       "SchoolHoliday_bw                       7                    7   \n",
       "StateHoliday_bw                        1                    0   \n",
       "Promo_bw                               1                    2   \n",
       "Closed_fw                              1                    1   \n",
       "SchoolHoliday_fw                       5                    4   \n",
       "StateHoliday_fw                        0                    0   \n",
       "Promo_fw                               5                    4   \n",
       "\n",
       "                                     981                  980  \n",
       "Date                 2013-01-09 00:00:00  2013-01-10 00:00:00  \n",
       "Store                                  1                    1  \n",
       "Open                                True                 True  \n",
       "Promo                               True                 True  \n",
       "StateHoliday                       False                False  \n",
       "SchoolHoliday                       True                 True  \n",
       "Closed                             False                False  \n",
       "AfterClosed                            3                    4  \n",
       "BeforeClosed                           4                    3  \n",
       "AfterSchoolHoliday                     0                    0  \n",
       "BeforeSchoolHoliday                    0                    0  \n",
       "AfterStateHoliday                      8                    9  \n",
       "BeforeStateHoliday                    79                   78  \n",
       "AfterPromo                             0                    0  \n",
       "BeforePromo                            0                    0  \n",
       "Closed_bw                              1                    1  \n",
       "SchoolHoliday_bw                       7                    7  \n",
       "StateHoliday_bw                        0                    0  \n",
       "Promo_bw                               3                    4  \n",
       "Closed_fw                              1                    1  \n",
       "SchoolHoliday_fw                       3                    2  \n",
       "StateHoliday_fw                        0                    0  \n",
       "Promo_fw                               3                    2  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elapsed_df.sort_values(by=['Store', 'Date']).head(10).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop not needed fields before join\n",
    "elapsed_df.drop(['Open'] + event_flds, axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = left_outer_join(df, elapsed_df, ['Store', 'Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "AfterClosed             5.0\n",
       "AfterPromo              0.0\n",
       "AfterSchoolHoliday      0.0\n",
       "AfterStateHoliday      57.0\n",
       "BeforeClosed            2.0\n",
       "BeforePromo             0.0\n",
       "BeforeSchoolHoliday     0.0\n",
       "BeforeStateHoliday      0.0\n",
       "Closed_bw               1.0\n",
       "Closed_fw               1.0\n",
       "Promo_bw                5.0\n",
       "Promo_fw                5.0\n",
       "SchoolHoliday_bw        5.0\n",
       "SchoolHoliday_fw        7.0\n",
       "StateHoliday_bw         0.0\n",
       "StateHoliday_fw         0.0\n",
       "Name: 0, dtype: float64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.columns.difference(base_columns)].iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Closed stores and zero sales stores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "178801"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(~df.Open).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "172871"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df['Sales'] == 0).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Closed stores won't be counted into the forecasts\n",
    "df = df[~((~df.Open) | (df['Sales'] == 0))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('Open', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Save data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Timestamp('2013-01-01 00:00:00'), Timestamp('2015-09-17 00:00:00'))"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by=['Date', 'Store'], inplace=True)\n",
    "\n",
    "df.Date.iloc[0], df.Date.iloc[-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=879442, step=1)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.reset_index(inplace=True, drop=True)\n",
    "\n",
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sales                   35104\n",
       "Customers               35104\n",
       "CompetitionDistance      2262\n",
       "PromoInterval          438106\n",
       "Max_VisibilityKm        22172\n",
       "Mean_VisibilityKm       22172\n",
       "Min_VisibilitykM        22172\n",
       "Max_Gust_SpeedKm_h     677088\n",
       "CloudCover              73011\n",
       "Events                 211902\n",
       "dtype: int64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get columns with missing values\n",
    "df[df.columns[df.isna().any()]].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[['SchoolHoliday_bw', 'StateHoliday_bw', 'Promo_bw', 'Closed_bw',\n",
    "    'SchoolHoliday_fw', 'StateHoliday_fw', 'Promo_fw', 'Closed_fw']] = \\\n",
    "df[['SchoolHoliday_bw', 'StateHoliday_bw', 'Promo_bw', 'Closed_bw',\n",
    "    'SchoolHoliday_fw', 'StateHoliday_fw', 'Promo_fw', 'Closed_fw']].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(844338, 76)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df = df.loc[~df.Sales.isnull()]\n",
    "\n",
    "train_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py:3391: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self[k1] = value[k2]\n"
     ]
    }
   ],
   "source": [
    "train_df[['Sales', 'Customers']] = train_df[['Sales', 'Customers']].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>count</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Store</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>1115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales</th>\n",
       "      <td>844338</td>\n",
       "      <td>46</td>\n",
       "      <td>41551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Customers</th>\n",
       "      <td>844338</td>\n",
       "      <td>8</td>\n",
       "      <td>7388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <td>844338</td>\n",
       "      <td>2013</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Month</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Week</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Day</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dayofweek</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dayofyear</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Elapsed</th>\n",
       "      <td>844338</td>\n",
       "      <td>1356998400</td>\n",
       "      <td>1438300800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionDistance</th>\n",
       "      <td>842152</td>\n",
       "      <td>20</td>\n",
       "      <td>75860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionOpenSinceMonth</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionOpenSinceYear</th>\n",
       "      <td>844338</td>\n",
       "      <td>1900</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2SinceWeek</th>\n",
       "      <td>844338</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2SinceYear</th>\n",
       "      <td>844338</td>\n",
       "      <td>1900</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trend</th>\n",
       "      <td>844338</td>\n",
       "      <td>28</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trend_DE</th>\n",
       "      <td>844338</td>\n",
       "      <td>50</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_TemperatureC</th>\n",
       "      <td>844338</td>\n",
       "      <td>-11</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_TemperatureC</th>\n",
       "      <td>844338</td>\n",
       "      <td>-13</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_TemperatureC</th>\n",
       "      <td>844338</td>\n",
       "      <td>-15</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dew_PointC</th>\n",
       "      <td>844338</td>\n",
       "      <td>-14</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MeanDew_PointC</th>\n",
       "      <td>844338</td>\n",
       "      <td>-15</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_DewpointC</th>\n",
       "      <td>844338</td>\n",
       "      <td>-73</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Humidity</th>\n",
       "      <td>844338</td>\n",
       "      <td>44</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Humidity</th>\n",
       "      <td>844338</td>\n",
       "      <td>30</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_Humidity</th>\n",
       "      <td>844338</td>\n",
       "      <td>4</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Sea_Level_PressurehPa</th>\n",
       "      <td>844338</td>\n",
       "      <td>976</td>\n",
       "      <td>1043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Sea_Level_PressurehPa</th>\n",
       "      <td>844338</td>\n",
       "      <td>974</td>\n",
       "      <td>1040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_Sea_Level_PressurehPa</th>\n",
       "      <td>844338</td>\n",
       "      <td>970</td>\n",
       "      <td>1038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_VisibilityKm</th>\n",
       "      <td>823710</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_VisibilityKm</th>\n",
       "      <td>823710</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Min_VisibilitykM</th>\n",
       "      <td>823710</td>\n",
       "      <td>0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Wind_SpeedKm_h</th>\n",
       "      <td>844338</td>\n",
       "      <td>3</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mean_Wind_SpeedKm_h</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max_Gust_SpeedKm_h</th>\n",
       "      <td>194051</td>\n",
       "      <td>21</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Precipitationmm</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CloudCover</th>\n",
       "      <td>776282</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WindDirDegrees</th>\n",
       "      <td>844338</td>\n",
       "      <td>-1</td>\n",
       "      <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2Days</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>2195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2Weeks</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionDaysOpen</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>9177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionMonthsOpen</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterClosed</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforeClosed</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterSchoolHoliday</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforeSchoolHoliday</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterStateHoliday</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforeStateHoliday</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AfterPromo</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BeforePromo</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Closed_bw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SchoolHoliday_bw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday_bw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo_bw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Closed_fw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SchoolHoliday_fw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday_fw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo_fw</th>\n",
       "      <td>844338</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             count         min         max\n",
       "Store                       844338           1        1115\n",
       "Sales                       844338          46       41551\n",
       "Customers                   844338           8        7388\n",
       "Year                        844338        2013        2015\n",
       "Month                       844338           1          12\n",
       "Week                        844338           1          52\n",
       "Day                         844338           1          31\n",
       "Dayofweek                   844338           0           6\n",
       "Dayofyear                   844338           1         365\n",
       "Elapsed                     844338  1356998400  1438300800\n",
       "CompetitionDistance         842152          20       75860\n",
       "CompetitionOpenSinceMonth   844338           1          12\n",
       "CompetitionOpenSinceYear    844338        1900        2015\n",
       "Promo2SinceWeek             844338           1          50\n",
       "Promo2SinceYear             844338        1900        2015\n",
       "trend                       844338          28         100\n",
       "trend_DE                    844338          50         100\n",
       "Max_TemperatureC            844338         -11          38\n",
       "Mean_TemperatureC           844338         -13          31\n",
       "Min_TemperatureC            844338         -15          24\n",
       "Dew_PointC                  844338         -14          25\n",
       "MeanDew_PointC              844338         -15          20\n",
       "Min_DewpointC               844338         -73          18\n",
       "Max_Humidity                844338          44         100\n",
       "Mean_Humidity               844338          30         100\n",
       "Min_Humidity                844338           4         100\n",
       "Max_Sea_Level_PressurehPa   844338         976        1043\n",
       "Mean_Sea_Level_PressurehPa  844338         974        1040\n",
       "Min_Sea_Level_PressurehPa   844338         970        1038\n",
       "Max_VisibilityKm            823710           0          31\n",
       "Mean_VisibilityKm           823710           0          31\n",
       "Min_VisibilitykM            823710           0          31\n",
       "Max_Wind_SpeedKm_h          844338           3         101\n",
       "Mean_Wind_SpeedKm_h         844338           2          53\n",
       "Max_Gust_SpeedKm_h          194051          21         111\n",
       "Precipitationmm             844338           0          58\n",
       "CloudCover                  776282           0           8\n",
       "WindDirDegrees              844338          -1         360\n",
       "Promo2Days                  844338           0        2195\n",
       "Promo2Weeks                 844338           0          25\n",
       "CompetitionDaysOpen         844338           0        9177\n",
       "CompetitionMonthsOpen       844338           0          24\n",
       "AfterClosed                 844338           0         926\n",
       "BeforeClosed                844338           0         986\n",
       "AfterSchoolHoliday          844338           0         107\n",
       "BeforeSchoolHoliday         844338           0         194\n",
       "AfterStateHoliday           844338           0         135\n",
       "BeforeStateHoliday          844338           0         195\n",
       "AfterPromo                  844338           0         188\n",
       "BeforePromo                 844338           0          16\n",
       "Closed_bw                   844338           0           6\n",
       "SchoolHoliday_bw            844338           0           7\n",
       "StateHoliday_bw             844338           0           2\n",
       "Promo_bw                    844338           0           5\n",
       "Closed_fw                   844338           0           6\n",
       "SchoolHoliday_fw            844338           0           7\n",
       "StateHoliday_fw             844338           0           2\n",
       "Promo_fw                    844338           0           5"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.describe(include=[np.number]).loc[['count', 'min', 'max']].T.astype(np.int64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>unique</th>\n",
       "      <th>top</th>\n",
       "      <th>freq</th>\n",
       "      <th>first</th>\n",
       "      <th>last</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>844338</td>\n",
       "      <td>942</td>\n",
       "      <td>2015-06-09 00:00:00</td>\n",
       "      <td>1115</td>\n",
       "      <td>2013-01-01 00:00:00</td>\n",
       "      <td>2015-07-31 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>467463</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StateHoliday</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>843428</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SchoolHoliday</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>680893</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_month_end</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>816299</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_month_start</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>824972</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_quarter_end</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>835723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_quarter_start</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>836819</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_year_end</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>842288</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Is_year_start</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>844285</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>StoreType</th>\n",
       "      <td>844338</td>\n",
       "      <td>4</td>\n",
       "      <td>a</td>\n",
       "      <td>457042</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assortment</th>\n",
       "      <td>844338</td>\n",
       "      <td>3</td>\n",
       "      <td>a</td>\n",
       "      <td>444875</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2</th>\n",
       "      <td>844338</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "      <td>423292</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PromoInterval</th>\n",
       "      <td>421046</td>\n",
       "      <td>3</td>\n",
       "      <td>Jan,Apr,Jul,Oct</td>\n",
       "      <td>242397</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>State</th>\n",
       "      <td>844338</td>\n",
       "      <td>12</td>\n",
       "      <td>NW</td>\n",
       "      <td>222930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Events</th>\n",
       "      <td>641281</td>\n",
       "      <td>21</td>\n",
       "      <td>Rain</td>\n",
       "      <td>324320</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Promo2Since</th>\n",
       "      <td>844338</td>\n",
       "      <td>56</td>\n",
       "      <td>1900-01-01 00:00:00</td>\n",
       "      <td>423292</td>\n",
       "      <td>1900-01-01 00:00:00</td>\n",
       "      <td>2015-06-01 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CompetitionOpenSince</th>\n",
       "      <td>844338</td>\n",
       "      <td>171</td>\n",
       "      <td>1900-01-15 00:00:00</td>\n",
       "      <td>269222</td>\n",
       "      <td>1900-01-15 00:00:00</td>\n",
       "      <td>2015-08-15 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       count unique                  top    freq  \\\n",
       "Date                  844338    942  2015-06-09 00:00:00    1115   \n",
       "Promo                 844338      2                False  467463   \n",
       "StateHoliday          844338      2                False  843428   \n",
       "SchoolHoliday         844338      2                False  680893   \n",
       "Is_month_end          844338      2                False  816299   \n",
       "Is_month_start        844338      2                False  824972   \n",
       "Is_quarter_end        844338      2                False  835723   \n",
       "Is_quarter_start      844338      2                False  836819   \n",
       "Is_year_end           844338      2                False  842288   \n",
       "Is_year_start         844338      2                False  844285   \n",
       "StoreType             844338      4                    a  457042   \n",
       "Assortment            844338      3                    a  444875   \n",
       "Promo2                844338      2                False  423292   \n",
       "PromoInterval         421046      3      Jan,Apr,Jul,Oct  242397   \n",
       "State                 844338     12                   NW  222930   \n",
       "Events                641281     21                 Rain  324320   \n",
       "Promo2Since           844338     56  1900-01-01 00:00:00  423292   \n",
       "CompetitionOpenSince  844338    171  1900-01-15 00:00:00  269222   \n",
       "\n",
       "                                    first                 last  \n",
       "Date                  2013-01-01 00:00:00  2015-07-31 00:00:00  \n",
       "Promo                                 NaN                  NaN  \n",
       "StateHoliday                          NaN                  NaN  \n",
       "SchoolHoliday                         NaN                  NaN  \n",
       "Is_month_end                          NaN                  NaN  \n",
       "Is_month_start                        NaN                  NaN  \n",
       "Is_quarter_end                        NaN                  NaN  \n",
       "Is_quarter_start                      NaN                  NaN  \n",
       "Is_year_end                           NaN                  NaN  \n",
       "Is_year_start                         NaN                  NaN  \n",
       "StoreType                             NaN                  NaN  \n",
       "Assortment                            NaN                  NaN  \n",
       "Promo2                                NaN                  NaN  \n",
       "PromoInterval                         NaN                  NaN  \n",
       "State                                 NaN                  NaN  \n",
       "Events                                NaN                  NaN  \n",
       "Promo2Since           1900-01-01 00:00:00  2015-06-01 00:00:00  \n",
       "CompetitionOpenSince  1900-01-15 00:00:00  2015-08-15 00:00:00  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.describe(exclude=[np.number]).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py:3940: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  errors=errors)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(35104, 74)"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_df = df.loc[df.Sales.isnull()]\n",
    "test_df.drop('Sales', axis=1, inplace=True)\n",
    "test_df.drop('Customers', axis=1, inplace=True)\n",
    "\n",
    "test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save final tables\n",
    "train_df.to_pickle(path/'train_df')\n",
    "test_df.to_pickle(path/'test_df')"
   ]
  },
  {
   "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.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}