project 1021.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in all those csv files, take a look at what they contain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2717: DtypeWarning: Columns (5) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "holidays_events = pd.read_csv(\"holidays_events.csv\")\n",
    "items = pd.read_csv(\"items.csv\")\n",
    "oil = pd.read_csv(\"oil.csv\")\n",
    "stores = pd.read_csv(\"stores.csv\")\n",
    "test = pd.read_csv(\"test.csv\")\n",
    "train = pd.read_csv(\"train.csv\")\n",
    "transactions = pd.read_csv(\"transactions.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>type</th>\n",
       "      <th>locale</th>\n",
       "      <th>locale_name</th>\n",
       "      <th>description</th>\n",
       "      <th>transferred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-03-02</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Manta</td>\n",
       "      <td>Fundacion de Manta</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-04-01</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Regional</td>\n",
       "      <td>Cotopaxi</td>\n",
       "      <td>Provincializacion de Cotopaxi</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012-04-12</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Cuenca</td>\n",
       "      <td>Fundacion de Cuenca</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012-04-14</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Libertad</td>\n",
       "      <td>Cantonizacion de Libertad</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012-04-21</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Riobamba</td>\n",
       "      <td>Cantonizacion de Riobamba</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     type    locale locale_name                    description  \\\n",
       "0  2012-03-02  Holiday     Local       Manta             Fundacion de Manta   \n",
       "1  2012-04-01  Holiday  Regional    Cotopaxi  Provincializacion de Cotopaxi   \n",
       "2  2012-04-12  Holiday     Local      Cuenca            Fundacion de Cuenca   \n",
       "3  2012-04-14  Holiday     Local    Libertad      Cantonizacion de Libertad   \n",
       "4  2012-04-21  Holiday     Local    Riobamba      Cantonizacion de Riobamba   \n",
       "\n",
       "   transferred  \n",
       "0        False  \n",
       "1        False  \n",
       "2        False  \n",
       "3        False  \n",
       "4        False  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Ecuador                           174\n",
       "Quito                              13\n",
       "Guaranda                           12\n",
       "Latacunga                          12\n",
       "Riobamba                           12\n",
       "Ambato                             12\n",
       "Guayaquil                          11\n",
       "Cuenca                              7\n",
       "Ibarra                              7\n",
       "Imbabura                            6\n",
       "Puyo                                6\n",
       "Loja                                6\n",
       "Salinas                             6\n",
       "Libertad                            6\n",
       "Santo Domingo                       6\n",
       "Manta                               6\n",
       "Cotopaxi                            6\n",
       "Esmeraldas                          6\n",
       "Cayambe                             6\n",
       "Santa Elena                         6\n",
       "Santo Domingo de los Tsachilas      6\n",
       "Quevedo                             6\n",
       "Machala                             6\n",
       "El Carmen                           6\n",
       "Name: locale_name, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays_events['locale_name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>type</th>\n",
       "      <th>locale</th>\n",
       "      <th>locale_name</th>\n",
       "      <th>description</th>\n",
       "      <th>transferred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2012-10-09</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Independencia de Guayaquil</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>2013-10-09</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Independencia de Guayaquil</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>2014-10-09</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Independencia de Guayaquil</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>2016-05-24</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Batalla de Pichincha</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>2016-07-25</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Guayaquil</td>\n",
       "      <td>Fundacion de Guayaquil</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>268</th>\n",
       "      <td>2016-08-10</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Primer Grito de Independencia</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Primer dia del ano</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>2017-04-12</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Cuenca</td>\n",
       "      <td>Fundacion de Cuenca</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>312</th>\n",
       "      <td>2017-05-24</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Batalla de Pichincha</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>324</th>\n",
       "      <td>2017-08-10</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Primer Grito de Independencia</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>328</th>\n",
       "      <td>2017-09-28</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Ibarra</td>\n",
       "      <td>Fundacion de Ibarra</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340</th>\n",
       "      <td>2017-12-06</td>\n",
       "      <td>Holiday</td>\n",
       "      <td>Local</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Fundacion de Quito</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           date     type    locale locale_name                    description  \\\n",
       "19   2012-10-09  Holiday  National     Ecuador     Independencia de Guayaquil   \n",
       "72   2013-10-09  Holiday  National     Ecuador     Independencia de Guayaquil   \n",
       "135  2014-10-09  Holiday  National     Ecuador     Independencia de Guayaquil   \n",
       "255  2016-05-24  Holiday  National     Ecuador           Batalla de Pichincha   \n",
       "266  2016-07-25  Holiday     Local   Guayaquil         Fundacion de Guayaquil   \n",
       "268  2016-08-10  Holiday  National     Ecuador  Primer Grito de Independencia   \n",
       "297  2017-01-01  Holiday  National     Ecuador             Primer dia del ano   \n",
       "303  2017-04-12  Holiday     Local      Cuenca            Fundacion de Cuenca   \n",
       "312  2017-05-24  Holiday  National     Ecuador           Batalla de Pichincha   \n",
       "324  2017-08-10  Holiday  National     Ecuador  Primer Grito de Independencia   \n",
       "328  2017-09-28  Holiday     Local      Ibarra            Fundacion de Ibarra   \n",
       "340  2017-12-06  Holiday     Local       Quito             Fundacion de Quito   \n",
       "\n",
       "     transferred  \n",
       "19          True  \n",
       "72          True  \n",
       "135         True  \n",
       "255         True  \n",
       "266         True  \n",
       "268         True  \n",
       "297         True  \n",
       "303         True  \n",
       "312         True  \n",
       "324         True  \n",
       "328         True  \n",
       "340         True  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays_events[holidays_events['transferred'] == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>type</th>\n",
       "      <th>locale</th>\n",
       "      <th>locale_name</th>\n",
       "      <th>description</th>\n",
       "      <th>transferred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2012-10-12</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Independencia de Guayaquil</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>2013-10-11</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Independencia de Guayaquil</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>2014-10-10</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Independencia de Guayaquil</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>2016-05-27</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Batalla de Pichincha</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>265</th>\n",
       "      <td>2016-07-24</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>Local</td>\n",
       "      <td>Guayaquil</td>\n",
       "      <td>Traslado Fundacion de Guayaquil</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>2016-08-12</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Primer Grito de Independencia</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>2017-01-02</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Primer dia del ano</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>2017-04-13</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>Local</td>\n",
       "      <td>Cuenca</td>\n",
       "      <td>Fundacion de Cuenca</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>2017-05-26</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Batalla de Pichincha</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>325</th>\n",
       "      <td>2017-08-11</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>National</td>\n",
       "      <td>Ecuador</td>\n",
       "      <td>Traslado Primer Grito de Independencia</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>Local</td>\n",
       "      <td>Ibarra</td>\n",
       "      <td>Fundacion de Ibarra</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>2017-12-08</td>\n",
       "      <td>Transfer</td>\n",
       "      <td>Local</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Traslado Fundacion de Quito</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           date      type    locale locale_name  \\\n",
       "20   2012-10-12  Transfer  National     Ecuador   \n",
       "73   2013-10-11  Transfer  National     Ecuador   \n",
       "136  2014-10-10  Transfer  National     Ecuador   \n",
       "256  2016-05-27  Transfer  National     Ecuador   \n",
       "265  2016-07-24  Transfer     Local   Guayaquil   \n",
       "269  2016-08-12  Transfer  National     Ecuador   \n",
       "298  2017-01-02  Transfer  National     Ecuador   \n",
       "304  2017-04-13  Transfer     Local      Cuenca   \n",
       "313  2017-05-26  Transfer  National     Ecuador   \n",
       "325  2017-08-11  Transfer  National     Ecuador   \n",
       "329  2017-09-29  Transfer     Local      Ibarra   \n",
       "342  2017-12-08  Transfer     Local       Quito   \n",
       "\n",
       "                                description  transferred  \n",
       "20      Traslado Independencia de Guayaquil        False  \n",
       "73      Traslado Independencia de Guayaquil        False  \n",
       "136     Traslado Independencia de Guayaquil        False  \n",
       "256           Traslado Batalla de Pichincha        False  \n",
       "265         Traslado Fundacion de Guayaquil        False  \n",
       "269  Traslado Primer Grito de Independencia        False  \n",
       "298             Traslado Primer dia del ano        False  \n",
       "304                     Fundacion de Cuenca        False  \n",
       "313           Traslado Batalla de Pichincha        False  \n",
       "325  Traslado Primer Grito de Independencia        False  \n",
       "329                     Fundacion de Ibarra        False  \n",
       "342             Traslado Fundacion de Quito        False  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays_events[holidays_events['type'] == 'Transfer']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 350 entries, 0 to 349\n",
      "Data columns (total 6 columns):\n",
      "date           350 non-null object\n",
      "type           350 non-null object\n",
      "locale         350 non-null object\n",
      "locale_name    350 non-null object\n",
      "description    350 non-null object\n",
      "transferred    350 non-null bool\n",
      "dtypes: bool(1), object(5)\n",
      "memory usage: 14.1+ KB\n"
     ]
    }
   ],
   "source": [
    "holidays_events.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>family</th>\n",
       "      <th>class</th>\n",
       "      <th>perishable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>96995</td>\n",
       "      <td>GROCERY I</td>\n",
       "      <td>1093</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>99197</td>\n",
       "      <td>GROCERY I</td>\n",
       "      <td>1067</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103501</td>\n",
       "      <td>CLEANING</td>\n",
       "      <td>3008</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103520</td>\n",
       "      <td>GROCERY I</td>\n",
       "      <td>1028</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103665</td>\n",
       "      <td>BREAD/BAKERY</td>\n",
       "      <td>2712</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item_nbr        family  class  perishable\n",
       "0     96995     GROCERY I   1093           0\n",
       "1     99197     GROCERY I   1067           0\n",
       "2    103501      CLEANING   3008           0\n",
       "3    103520     GROCERY I   1028           0\n",
       "4    103665  BREAD/BAKERY   2712           1"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4100 entries, 0 to 4099\n",
      "Data columns (total 4 columns):\n",
      "item_nbr      4100 non-null int64\n",
      "family        4100 non-null object\n",
      "class         4100 non-null int64\n",
      "perishable    4100 non-null int64\n",
      "dtypes: int64(3), object(1)\n",
      "memory usage: 128.2+ KB\n"
     ]
    }
   ],
   "source": [
    "items.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GROCERY I                     1334\n",
       "BEVERAGES                      613\n",
       "CLEANING                       446\n",
       "PRODUCE                        306\n",
       "DAIRY                          242\n",
       "PERSONAL CARE                  153\n",
       "BREAD/BAKERY                   134\n",
       "HOME CARE                      108\n",
       "DELI                            91\n",
       "MEATS                           84\n",
       "HOME AND KITCHEN I              77\n",
       "LIQUOR,WINE,BEER                73\n",
       "FROZEN FOODS                    55\n",
       "POULTRY                         54\n",
       "HOME AND KITCHEN II             45\n",
       "EGGS                            41\n",
       "CELEBRATION                     31\n",
       "LAWN AND GARDEN                 26\n",
       "PREPARED FOODS                  26\n",
       "LADIESWEAR                      21\n",
       "LINGERIE                        20\n",
       "AUTOMOTIVE                      20\n",
       "BEAUTY                          19\n",
       "PLAYERS AND ELECTRONICS         17\n",
       "SCHOOL AND OFFICE SUPPLIES      15\n",
       "GROCERY II                      14\n",
       "PET SUPPLIES                    14\n",
       "SEAFOOD                          8\n",
       "MAGAZINES                        6\n",
       "HARDWARE                         4\n",
       "HOME APPLIANCES                  1\n",
       "BABY CARE                        1\n",
       "BOOKS                            1\n",
       "Name: family, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items['family'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "item_nbr      4100\n",
       "family          33\n",
       "class          337\n",
       "perishable       2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>dcoilwtico</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>93.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-03</td>\n",
       "      <td>92.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-04</td>\n",
       "      <td>93.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-07</td>\n",
       "      <td>93.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  dcoilwtico\n",
       "0  2013-01-01         NaN\n",
       "1  2013-01-02       93.14\n",
       "2  2013-01-03       92.97\n",
       "3  2013-01-04       93.12\n",
       "4  2013-01-07       93.20"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oil.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1218 entries, 0 to 1217\n",
      "Data columns (total 2 columns):\n",
      "date          1218 non-null object\n",
      "dcoilwtico    1175 non-null float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 19.1+ KB\n"
     ]
    }
   ],
   "source": [
    "oil.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>type</th>\n",
       "      <th>cluster</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Quito</td>\n",
       "      <td>Pichincha</td>\n",
       "      <td>D</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Santo Domingo</td>\n",
       "      <td>Santo Domingo de los Tsachilas</td>\n",
       "      <td>D</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   store_nbr           city                           state type  cluster\n",
       "0          1          Quito                       Pichincha    D       13\n",
       "1          2          Quito                       Pichincha    D       13\n",
       "2          3          Quito                       Pichincha    D        8\n",
       "3          4          Quito                       Pichincha    D        9\n",
       "4          5  Santo Domingo  Santo Domingo de los Tsachilas    D        4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stores.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "store_nbr    54\n",
       "city         22\n",
       "state        16\n",
       "type          5\n",
       "cluster      17\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stores.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>onpromotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>125497040</td>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>1</td>\n",
       "      <td>96995</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>125497041</td>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>1</td>\n",
       "      <td>99197</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>125497042</td>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>1</td>\n",
       "      <td>103501</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>125497043</td>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>1</td>\n",
       "      <td>103520</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>125497044</td>\n",
       "      <td>2017-08-16</td>\n",
       "      <td>1</td>\n",
       "      <td>103665</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          id        date  store_nbr  item_nbr  onpromotion\n",
       "0  125497040  2017-08-16          1     96995        False\n",
       "1  125497041  2017-08-16          1     99197        False\n",
       "2  125497042  2017-08-16          1    103501        False\n",
       "3  125497043  2017-08-16          1    103520        False\n",
       "4  125497044  2017-08-16          1    103665        False"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3370464 entries, 0 to 3370463\n",
      "Data columns (total 5 columns):\n",
      "id             int64\n",
      "date           object\n",
      "store_nbr      int64\n",
      "item_nbr       int64\n",
      "onpromotion    bool\n",
      "dtypes: bool(1), int64(3), object(1)\n",
      "memory usage: 106.1+ MB\n"
     ]
    }
   ],
   "source": [
    "test.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    3171867\n",
       "True      198597\n",
       "Name: onpromotion, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['onpromotion'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>unit_sales</th>\n",
       "      <th>onpromotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>103665</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>105574</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>105575</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>108079</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>108701</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id        date  store_nbr  item_nbr  unit_sales onpromotion\n",
       "0   0  2013-01-01         25    103665         7.0         NaN\n",
       "1   1  2013-01-01         25    105574         1.0         NaN\n",
       "2   2  2013-01-01         25    105575         2.0         NaN\n",
       "3   3  2013-01-01         25    108079         1.0         NaN\n",
       "4   4  2013-01-01         25    108701         1.0         NaN"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    96028767\n",
       "True      7810622\n",
       "Name: onpromotion, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['onpromotion'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 125497040 entries, 0 to 125497039\n",
      "Data columns (total 6 columns):\n",
      "id             int64\n",
      "date           object\n",
      "store_nbr      int64\n",
      "item_nbr       int64\n",
      "unit_sales     float64\n",
      "onpromotion    object\n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 5.6+ GB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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_nbr</th>\n",
       "      <th>transactions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>1</td>\n",
       "      <td>2111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>2</td>\n",
       "      <td>2358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>3487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-02</td>\n",
       "      <td>4</td>\n",
       "      <td>1922</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  store_nbr  transactions\n",
       "0  2013-01-01         25           770\n",
       "1  2013-01-02          1          2111\n",
       "2  2013-01-02          2          2358\n",
       "3  2013-01-02          3          3487\n",
       "4  2013-01-02          4          1922"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 83488 entries, 0 to 83487\n",
      "Data columns (total 3 columns):\n",
      "date            83488 non-null object\n",
      "store_nbr       83488 non-null int64\n",
      "transactions    83488 non-null int64\n",
      "dtypes: int64(2), object(1)\n",
      "memory usage: 1.9+ MB\n"
     ]
    }
   ],
   "source": [
    "transactions.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date            1682\n",
       "store_nbr         54\n",
       "transactions    4993\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Take a look at their NaN values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date            0\n",
       "store_nbr       0\n",
       "transactions    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                    0\n",
       "date                  0\n",
       "store_nbr             0\n",
       "item_nbr              0\n",
       "unit_sales            0\n",
       "onpromotion    21657651\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    96028767\n",
       "True      7810622\n",
       "Name: onpromotion, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#take a look at the onpromotion column, see if it only has \"NaN\" and \"True\" value\n",
    "#so we can fill all NaN value with False\n",
    "#But seems that it has \"true\", \"false\" and NaN values\n",
    "train['onpromotion'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>unit_sales</th>\n",
       "      <th>onpromotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21668391</th>\n",
       "      <td>21668391</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>9</td>\n",
       "      <td>414354</td>\n",
       "      <td>11.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21668820</th>\n",
       "      <td>21668820</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>9</td>\n",
       "      <td>874593</td>\n",
       "      <td>33.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21668931</th>\n",
       "      <td>21668931</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>9</td>\n",
       "      <td>999545</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21669011</th>\n",
       "      <td>21669011</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>9</td>\n",
       "      <td>1057515</td>\n",
       "      <td>1.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21669094</th>\n",
       "      <td>21669094</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>9</td>\n",
       "      <td>1119217</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                id        date  store_nbr  item_nbr  unit_sales onpromotion\n",
       "21668391  21668391  2014-04-01          9    414354        11.0        True\n",
       "21668820  21668820  2014-04-01          9    874593        33.0        True\n",
       "21668931  21668931  2014-04-01          9    999545         2.0        True\n",
       "21669011  21669011  2014-04-01          9   1057515         1.0        True\n",
       "21669094  21669094  2014-04-01          9   1119217         2.0        True"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#take a look at those \"onpromotion\", \"not on promotion\", and NaN, find their difference\n",
    "train[train['onpromotion'] == True].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>unit_sales</th>\n",
       "      <th>onpromotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21657651</th>\n",
       "      <td>21657651</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>1</td>\n",
       "      <td>103520</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21657652</th>\n",
       "      <td>21657652</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>1</td>\n",
       "      <td>103665</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21657653</th>\n",
       "      <td>21657653</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>1</td>\n",
       "      <td>105574</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21657654</th>\n",
       "      <td>21657654</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>1</td>\n",
       "      <td>105575</td>\n",
       "      <td>11.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21657655</th>\n",
       "      <td>21657655</td>\n",
       "      <td>2014-04-01</td>\n",
       "      <td>1</td>\n",
       "      <td>105577</td>\n",
       "      <td>4.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                id        date  store_nbr  item_nbr  unit_sales onpromotion\n",
       "21657651  21657651  2014-04-01          1    103520         1.0       False\n",
       "21657652  21657652  2014-04-01          1    103665         3.0       False\n",
       "21657653  21657653  2014-04-01          1    105574         1.0       False\n",
       "21657654  21657654  2014-04-01          1    105575        11.0       False\n",
       "21657655  21657655  2014-04-01          1    105577         4.0       False"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#take a look at those \"onpromotion\", \"not on promotion\", and NaN, find their difference\n",
    "train[train['onpromotion'] == False].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>date</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>unit_sales</th>\n",
       "      <th>onpromotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>103665</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>105574</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>105575</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>108079</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>25</td>\n",
       "      <td>108701</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id        date  store_nbr  item_nbr  unit_sales onpromotion\n",
       "0   0  2013-01-01         25    103665         7.0         NaN\n",
       "1   1  2013-01-01         25    105574         1.0         NaN\n",
       "2   2  2013-01-01         25    105575         2.0         NaN\n",
       "3   3  2013-01-01         25    108079         1.0         NaN\n",
       "4   4  2013-01-01         25    108701         1.0         NaN"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#take a look at those \"onpromotion\", \"not on promotion\", and NaN, find their difference\n",
    "train[train['onpromotion'].isnull()].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id             0\n",
       "date           0\n",
       "store_nbr      0\n",
       "item_nbr       0\n",
       "onpromotion    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "store_nbr    0\n",
       "city         0\n",
       "state        0\n",
       "type         0\n",
       "cluster      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stores.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date           0\n",
       "dcoilwtico    43\n",
       "dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oil.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "item_nbr      0\n",
       "family        0\n",
       "class         0\n",
       "perishable    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date           0\n",
       "type           0\n",
       "locale         0\n",
       "locale_name    0\n",
       "description    0\n",
       "transferred    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holidays_events.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Join tables together"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## concatenate train and test together first"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>id</th>\n",
       "      <th>item_nbr</th>\n",
       "      <th>onpromotion</th>\n",
       "      <th>source</th>\n",
       "      <th>store_nbr</th>\n",
       "      <th>unit_sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>0</td>\n",
       "      <td>103665</td>\n",
       "      <td>NaN</td>\n",
       "      <td>train</td>\n",
       "      <td>25</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>1</td>\n",
       "      <td>105574</td>\n",
       "      <td>NaN</td>\n",
       "      <td>train</td>\n",
       "      <td>25</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>2</td>\n",
       "      <td>105575</td>\n",
       "      <td>NaN</td>\n",
       "      <td>train</td>\n",
       "      <td>25</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>3</td>\n",
       "      <td>108079</td>\n",
       "      <td>NaN</td>\n",
       "      <td>train</td>\n",
       "      <td>25</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013-01-01</td>\n",
       "      <td>4</td>\n",
       "      <td>108701</td>\n",
       "      <td>NaN</td>\n",
       "      <td>train</td>\n",
       "      <td>25</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  id  item_nbr onpromotion source  store_nbr  unit_sales\n",
       "0  2013-01-01   0    103665         NaN  train         25         7.0\n",
       "1  2013-01-01   1    105574         NaN  train         25         1.0\n",
       "2  2013-01-01   2    105575         NaN  train         25         2.0\n",
       "3  2013-01-01   3    108079         NaN  train         25         1.0\n",
       "4  2013-01-01   4    108701         NaN  train         25         1.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train['source'] = 'train'\n",
    "test['source'] = 'test'\n",
    "df_001 = pd.concat([train, test], axis = 0)\n",
    "df_001.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                  0\n",
       "id                    0\n",
       "item_nbr              0\n",
       "onpromotion    21657651\n",
       "source                0\n",
       "store_nbr             0\n",
       "unit_sales      3370464\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_001.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join holiday table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_002 = df_001.merge(holidays_events)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join oil table and df_001 table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_002 = df_001.merge(oil, on='date', how = 'left')\n",
    "#df_002.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#df_002[df_002['date'] == '2013-01-02']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                  0\n",
       "id                    0\n",
       "item_nbr              0\n",
       "onpromotion    21657651\n",
       "source                0\n",
       "store_nbr             0\n",
       "unit_sales      3370464\n",
       "dcoilwtico     41365546\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_002.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def sum_dig_pow(a, b): # range(a, b + 1) will be studied by the function\n",
    "    # your code here\n",
    "    final = []\n",
    "    for i in range(a, b+1):\n",
    "        str_i = str(i)\n",
    "        len_i = len(str_i)\n",
    "        result = 0\n",
    "        for s in str_i:\n",
    "            result = result + int(s) ** (str_i.index(s) + 1)\n",
    "        if result == i:\n",
    "            final.append(i)\n",
    "    return final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 89, 135, 175, 518, 598, 1306]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum_dig_pow(0, 1677)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1\n",
      "1\n",
      "37\n",
      "2\n",
      "380\n",
      "1\n",
      "416\n"
     ]
    }
   ],
   "source": [
    "result = 0\n",
    "str_i = '1676'\n",
    "for s in str_i:\n",
    "    result = result + int(s) ** (str_i.index(s) + 1)\n",
    "    print(str_i.index(s))\n",
    "    print(result)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1\n",
      "2\n",
      "0\n"
     ]
    }
   ],
   "source": [
    "for c in 'test':\n",
    "    print ('test'.index(c))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}