Modelo_Submissao_Parte_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Objetivo: modelo de previsão de vendas\n",
    "##### Autor: Yan Sym"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.model_selection import train_test_split, cross_val_score, KFold, StratifiedKFold, TimeSeriesSplit\n",
    "from sklearn.metrics import mean_absolute_error, mean_squared_error, make_scorer\n",
    "from hyperopt import fmin, hp, tpe\n",
    "from math import sqrt\n",
    "import lightgbm as lgb\n",
    "import warnings\n",
    "import random\n",
    "import gc\n",
    "\n",
    "pd.set_option('display.expand_frame_repr', False)\n",
    "pd.set_option('display.max_columns', None)  \n",
    "pd.set_option('display.expand_frame_repr', False)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Variáveis globais e parâmetros"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "random_seed = 42\n",
    "n_fold = 5\n",
    "\n",
    "params = {'num_leaves': 8,\n",
    "          'learning_rate': 0.05,\n",
    "          'subsample': 0.85,\n",
    "          'feature_fraction': 0.85,\n",
    "          'boosting_type': 'gbdt',\n",
    "          'n_jobs': 3,\n",
    "          'max_depth': -1,\n",
    "          \"metric\": 'rmse',\n",
    "          'random_state': 42,\n",
    "          'reg_lambda': 10}\n",
    "\n",
    "lista_vars_explicativas = ['cod_municipio',\n",
    "'mes',\n",
    "'qtde_media',\n",
    "'qtde_soma',\n",
    "'qtde_max',\n",
    "'qtde_min',\n",
    "'feature_04',\n",
    "'feature_08',\n",
    "'feature_09',\n",
    "'feature_14',\n",
    "'feature_18',\n",
    "'feature_04_vezes_06',\n",
    "'receita_target_lag_12_meses',\n",
    "'receita_loja_std_3_meses',\n",
    "'receita_target_media_movel_3_meses',\n",
    "'receita_total_loja_anomes',\n",
    "'receita_loja_media_movel_3_meses',\n",
    "'soma_quantidade_items_loja_media_movel_3_meses',\n",
    "'receita_loja_lag_1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Métodos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# métricas de erro\n",
    "def mae_score(true, pred):\n",
    "    return mean_absolute_error(true, pred)\n",
    "\n",
    "def mse_score(true, pred):\n",
    "    return mean_squared_error(true, pred)\n",
    "\n",
    "def rmse_score(true, pred):\n",
    "    return (sqrt(mean_squared_error(true, pred)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Leitura de bases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3130, 21)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod_loja</th>\n",
       "      <th>cod_ap</th>\n",
       "      <th>cod_municipio</th>\n",
       "      <th>feature_01</th>\n",
       "      <th>feature_02</th>\n",
       "      <th>feature_03</th>\n",
       "      <th>feature_04</th>\n",
       "      <th>feature_05</th>\n",
       "      <th>feature_06</th>\n",
       "      <th>feature_07</th>\n",
       "      <th>feature_08</th>\n",
       "      <th>feature_09</th>\n",
       "      <th>feature_10</th>\n",
       "      <th>feature_11</th>\n",
       "      <th>feature_12</th>\n",
       "      <th>feature_13</th>\n",
       "      <th>feature_14</th>\n",
       "      <th>feature_15</th>\n",
       "      <th>feature_16</th>\n",
       "      <th>feature_17</th>\n",
       "      <th>feature_18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>4125506005002</td>\n",
       "      <td>4125506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>True</td>\n",
       "      <td>21.76</td>\n",
       "      <td>FEATURE_05_VALUE_04</td>\n",
       "      <td>31</td>\n",
       "      <td>0.168922</td>\n",
       "      <td>0.167659</td>\n",
       "      <td>115.958848</td>\n",
       "      <td>2.349821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5300108005004</td>\n",
       "      <td>5300108</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_03</td>\n",
       "      <td>True</td>\n",
       "      <td>22.00</td>\n",
       "      <td>FEATURE_05_VALUE_03</td>\n",
       "      <td>27</td>\n",
       "      <td>0.215967</td>\n",
       "      <td>0.197287</td>\n",
       "      <td>1562.674817</td>\n",
       "      <td>3.670883</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>3550308005052</td>\n",
       "      <td>3550308</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_03</td>\n",
       "      <td>True</td>\n",
       "      <td>45.66</td>\n",
       "      <td>FEATURE_05_VALUE_03</td>\n",
       "      <td>6</td>\n",
       "      <td>0.190102</td>\n",
       "      <td>0.179140</td>\n",
       "      <td>5593.905463</td>\n",
       "      <td>2.815883</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>3145604003002</td>\n",
       "      <td>3145604</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>False</td>\n",
       "      <td>30.00</td>\n",
       "      <td>FEATURE_05_VALUE_03</td>\n",
       "      <td>3</td>\n",
       "      <td>0.141988</td>\n",
       "      <td>0.143677</td>\n",
       "      <td>12.824022</td>\n",
       "      <td>7.218132</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1600303005010</td>\n",
       "      <td>1600303</td>\n",
       "      <td>FEATURE_01_VALUE_10</td>\n",
       "      <td>FEATURE_02_VALUE_02</td>\n",
       "      <td>False</td>\n",
       "      <td>62.49</td>\n",
       "      <td>FEATURE_05_VALUE_03</td>\n",
       "      <td>30</td>\n",
       "      <td>0.107619</td>\n",
       "      <td>0.103083</td>\n",
       "      <td>325.531120</td>\n",
       "      <td>1.972944</td>\n",
       "      <td>FEATURE_11_VALUE_02</td>\n",
       "      <td>FEATURE_12_VALUE_01</td>\n",
       "      <td>100.0</td>\n",
       "      <td>88.1</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>88.5</td>\n",
       "      <td>93.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod_loja         cod_ap  cod_municipio           feature_01           feature_02  feature_03  feature_04           feature_05  feature_06  feature_07  feature_08   feature_09  feature_10           feature_11           feature_12  feature_13  feature_14  feature_15  feature_16  feature_17  feature_18\n",
       "0         1  4125506005002        4125506                  NaN  FEATURE_02_VALUE_04        True       21.76  FEATURE_05_VALUE_04          31    0.168922    0.167659   115.958848    2.349821                  NaN                  NaN         NaN         NaN         NaN         NaN         NaN         NaN\n",
       "1         2  5300108005004        5300108                  NaN  FEATURE_02_VALUE_03        True       22.00  FEATURE_05_VALUE_03          27    0.215967    0.197287  1562.674817    3.670883                  NaN                  NaN         NaN         NaN         NaN         NaN         NaN         NaN\n",
       "2         3  3550308005052        3550308                  NaN  FEATURE_02_VALUE_03        True       45.66  FEATURE_05_VALUE_03           6    0.190102    0.179140  5593.905463    2.815883                  NaN                  NaN         NaN         NaN         NaN         NaN         NaN         NaN\n",
       "3         4  3145604003002        3145604                  NaN  FEATURE_02_VALUE_04       False       30.00  FEATURE_05_VALUE_03           3    0.141988    0.143677    12.824022    7.218132                  NaN                  NaN         NaN         NaN         NaN         NaN         NaN         NaN\n",
       "4         5  1600303005010        1600303  FEATURE_01_VALUE_10  FEATURE_02_VALUE_02       False       62.49  FEATURE_05_VALUE_03          30    0.107619    0.103083   325.531120    1.972944  FEATURE_11_VALUE_02  FEATURE_12_VALUE_01       100.0        88.1       100.0       100.0        88.5        93.8"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_lojas = pd.read_csv('lojas_atuais.csv')\n",
    "print (df_lojas.shape)\n",
    "df_lojas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1003094, 6)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod_loja</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>categoria</th>\n",
       "      <th>qtde</th>\n",
       "      <th>receita</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>CATEG_01</td>\n",
       "      <td>301.0</td>\n",
       "      <td>9164.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>CATEG_01</td>\n",
       "      <td>187.0</td>\n",
       "      <td>8175.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>CATEG_01</td>\n",
       "      <td>242.0</td>\n",
       "      <td>10521.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>CATEG_01</td>\n",
       "      <td>104.0</td>\n",
       "      <td>4560.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>CATEG_01</td>\n",
       "      <td>100.0</td>\n",
       "      <td>4263.30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod_loja   ano  mes categoria   qtde   receita\n",
       "0         1  2016    1  CATEG_01  301.0   9164.86\n",
       "1         1  2016    2  CATEG_01  187.0   8175.55\n",
       "2         1  2016    3  CATEG_01  242.0  10521.67\n",
       "3         1  2016    4  CATEG_01  104.0   4560.91\n",
       "4         1  2016    5  CATEG_01  100.0   4263.30"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento = pd.read_csv('faturamento_lojas_atuais.csv')\n",
    "print (df_faturamento.shape)\n",
    "df_faturamento.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### engenharia de variáveis explicativas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lojas['feature_04_vezes_06'] = df_lojas['feature_04'] * df_lojas['feature_06']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Agrupamento de produtos por loja"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_faturamento['ano_mes'] = df_faturamento['ano'].map(str) + df_faturamento['mes'].map(str).str.zfill(2)\n",
    "df_faturamento['ano_mes'] = pd.to_datetime(df_faturamento['ano_mes'], format='%Y%m')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_quantidade_anomes = pd.DataFrame(df_faturamento.groupby(['cod_loja', 'ano', 'mes']).\\\n",
    "                                    agg({'qtde': ['sum', 'mean', 'max', 'min']})).reset_index()\n",
    "\n",
    "df_quantidade_anomes.columns = ['cod_loja', 'ano', 'mes', 'qtde_soma', 'qtde_media', 'qtde_min', 'qtde_max']\n",
    "\n",
    "df_faturamento_medio_loja_anomes = pd.DataFrame(df_faturamento.groupby(['cod_loja', 'ano', 'mes'])['receita'].sum()).reset_index()\n",
    "df_faturamento_medio_loja_anomes.columns = ['cod_loja', 'ano', 'mes', 'receita_total_loja_anomes']\n",
    "\n",
    "df_faturamento = pd.merge(df_faturamento_medio_loja_anomes,\n",
    "                          df_quantidade_anomes,\n",
    "                          on=['cod_loja', 'ano', 'mes'],\n",
    "                          how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017-12-01    3123\n",
       "2017-11-01    3108\n",
       "2017-10-01    3100\n",
       "2017-08-01    3093\n",
       "2017-09-01    3092\n",
       "2017-07-01    3091\n",
       "2017-06-01    3088\n",
       "2017-01-01    3087\n",
       "2017-05-01    3087\n",
       "2017-03-01    3084\n",
       "2016-12-01    3083\n",
       "2017-02-01    3083\n",
       "2017-04-01    3078\n",
       "2016-11-01    3064\n",
       "2016-10-01    3049\n",
       "2016-08-01    3048\n",
       "2016-09-01    3048\n",
       "2016-07-01    3046\n",
       "2016-06-01    3041\n",
       "2016-05-01    3036\n",
       "2016-04-01    3031\n",
       "2016-01-01    3029\n",
       "2016-03-01    3028\n",
       "2016-02-01    3027\n",
       "Name: ano_mes, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento['ano_mes'] = df_faturamento['ano'].map(str) + df_faturamento['mes'].map(str).str.zfill(2)\n",
    "df_faturamento['ano_mes'] = pd.to_datetime(df_faturamento['ano_mes'], format='%Y%m')\n",
    "df_faturamento['ano_mes'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod_loja</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>receita_total_loja_anomes</th>\n",
       "      <th>qtde_soma</th>\n",
       "      <th>qtde_media</th>\n",
       "      <th>qtde_min</th>\n",
       "      <th>qtde_max</th>\n",
       "      <th>ano_mes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>441833.37</td>\n",
       "      <td>13034.0</td>\n",
       "      <td>931.000000</td>\n",
       "      <td>3820.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>11411.0</td>\n",
       "      <td>815.071429</td>\n",
       "      <td>2637.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>15030.0</td>\n",
       "      <td>1073.571429</td>\n",
       "      <td>4251.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2016-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>12726.0</td>\n",
       "      <td>909.000000</td>\n",
       "      <td>3688.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>13978.0</td>\n",
       "      <td>998.428571</td>\n",
       "      <td>4345.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2016-05-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod_loja   ano  mes  receita_total_loja_anomes  qtde_soma   qtde_media  qtde_min  qtde_max    ano_mes\n",
       "0         1  2016    1                  441833.37    13034.0   931.000000    3820.0      24.0 2016-01-01\n",
       "1         1  2016    2                  395340.22    11411.0   815.071429    2637.0      24.0 2016-02-01\n",
       "2         1  2016    3                  587810.18    15030.0  1073.571429    4251.0      71.0 2016-03-01\n",
       "3         1  2016    4                  534618.84    12726.0   909.000000    3688.0       7.0 2016-04-01\n",
       "4         1  2016    5                  595477.92    13978.0   998.428571    4345.0      84.0 2016-05-01"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_faturamento['receita_m1'] = df_faturamento.groupby('cod_loja')['receita_total_loja_anomes'].transform(lambda x: x.shift(-1))\n",
    "df_faturamento['receita_m2'] = df_faturamento.groupby('cod_loja')['receita_total_loja_anomes'].transform(lambda x: x.shift(-2))\n",
    "df_faturamento['receita_m3'] = df_faturamento.groupby('cod_loja')['receita_total_loja_anomes'].transform(lambda x: x.shift(-3))\n",
    "df_faturamento['receita_target'] = (df_faturamento['receita_m1'] + df_faturamento['receita_m2'] + df_faturamento['receita_m3'])/3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>cod_loja</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>receita_total_loja_anomes</th>\n",
       "      <th>qtde_soma</th>\n",
       "      <th>qtde_media</th>\n",
       "      <th>qtde_min</th>\n",
       "      <th>qtde_max</th>\n",
       "      <th>ano_mes</th>\n",
       "      <th>receita_m1</th>\n",
       "      <th>receita_m2</th>\n",
       "      <th>receita_m3</th>\n",
       "      <th>receita_target</th>\n",
       "      <th>soma_quantidade_items_loja_media_movel_3_meses</th>\n",
       "      <th>receita_loja_std_3_meses</th>\n",
       "      <th>receita_loja_media_movel_3_meses</th>\n",
       "      <th>receita_loja_lag_6_meses</th>\n",
       "      <th>receita_loja_lag_12_meses</th>\n",
       "      <th>receita_target_media_movel_3_meses</th>\n",
       "      <th>receita_target_media_movel_6_meses</th>\n",
       "      <th>receita_target_lag_12_meses</th>\n",
       "      <th>receita_loja_lag_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>441833.37</td>\n",
       "      <td>13034.0</td>\n",
       "      <td>931.000000</td>\n",
       "      <td>3820.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>11411.0</td>\n",
       "      <td>815.071429</td>\n",
       "      <td>2637.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>505923.080000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>15030.0</td>\n",
       "      <td>1073.571429</td>\n",
       "      <td>4251.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>573347.936667</td>\n",
       "      <td>13158.333333</td>\n",
       "      <td>100428.687433</td>\n",
       "      <td>474994.590000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>550635.554444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>572635.646667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>12726.0</td>\n",
       "      <td>909.000000</td>\n",
       "      <td>3688.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>583079.666667</td>\n",
       "      <td>13055.666667</td>\n",
       "      <td>99391.933058</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>576354.416667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>573347.936667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>13978.0</td>\n",
       "      <td>998.428571</td>\n",
       "      <td>4345.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>625181.36</td>\n",
       "      <td>592980.813333</td>\n",
       "      <td>13911.333333</td>\n",
       "      <td>33145.991477</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583136.138889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583079.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>6</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>14693.0</td>\n",
       "      <td>1049.500000</td>\n",
       "      <td>3894.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2016-06-01</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>625181.36</td>\n",
       "      <td>583509.12</td>\n",
       "      <td>590834.836667</td>\n",
       "      <td>13799.000000</td>\n",
       "      <td>33654.194794</td>\n",
       "      <td>573347.936667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>588965.105556</td>\n",
       "      <td>569800.330000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>592980.813333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>7</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>13570.0</td>\n",
       "      <td>969.285714</td>\n",
       "      <td>3437.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>625181.36</td>\n",
       "      <td>583509.12</td>\n",
       "      <td>611797.30</td>\n",
       "      <td>606829.260000</td>\n",
       "      <td>14080.333333</td>\n",
       "      <td>16912.161242</td>\n",
       "      <td>583079.666667</td>\n",
       "      <td>441833.37</td>\n",
       "      <td>NaN</td>\n",
       "      <td>596881.636667</td>\n",
       "      <td>586618.026667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>590834.836667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>625181.36</td>\n",
       "      <td>13889.0</td>\n",
       "      <td>992.071429</td>\n",
       "      <td>4159.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>2016-08-01</td>\n",
       "      <td>583509.12</td>\n",
       "      <td>611797.30</td>\n",
       "      <td>876921.63</td>\n",
       "      <td>690742.683333</td>\n",
       "      <td>14050.666667</td>\n",
       "      <td>30795.942717</td>\n",
       "      <td>592980.813333</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>629468.926667</td>\n",
       "      <td>606302.532778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>606829.260000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>9</td>\n",
       "      <td>583509.12</td>\n",
       "      <td>13309.0</td>\n",
       "      <td>950.642857</td>\n",
       "      <td>3924.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>2016-09-01</td>\n",
       "      <td>611797.30</td>\n",
       "      <td>876921.63</td>\n",
       "      <td>1257613.00</td>\n",
       "      <td>915443.976667</td>\n",
       "      <td>13589.333333</td>\n",
       "      <td>31332.680883</td>\n",
       "      <td>590834.836667</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>737671.973333</td>\n",
       "      <td>663318.539444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>690742.683333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>10</td>\n",
       "      <td>611797.30</td>\n",
       "      <td>14709.0</td>\n",
       "      <td>1131.461538</td>\n",
       "      <td>3907.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>2016-10-01</td>\n",
       "      <td>876921.63</td>\n",
       "      <td>1257613.00</td>\n",
       "      <td>404108.77</td>\n",
       "      <td>846214.466667</td>\n",
       "      <td>13969.000000</td>\n",
       "      <td>21275.689477</td>\n",
       "      <td>606829.260000</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>817467.042222</td>\n",
       "      <td>707174.339444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>915443.976667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod_loja   ano  mes  receita_total_loja_anomes  qtde_soma   qtde_media  qtde_min  qtde_max    ano_mes  receita_m1  receita_m2  receita_m3  receita_target  soma_quantidade_items_loja_media_movel_3_meses  receita_loja_std_3_meses  receita_loja_media_movel_3_meses  receita_loja_lag_6_meses  receita_loja_lag_12_meses  receita_target_media_movel_3_meses  receita_target_media_movel_6_meses  receita_target_lag_12_meses  receita_loja_lag_1\n",
       "0         1  2016    1                  441833.37    13034.0   931.000000    3820.0      24.0 2016-01-01   395340.22   587810.18   534618.84   505923.080000                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN                 NaN\n",
       "1         1  2016    2                  395340.22    11411.0   815.071429    2637.0      24.0 2016-02-01   587810.18   534618.84   595477.92   572635.646667                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN       505923.080000\n",
       "2         1  2016    3                  587810.18    15030.0  1073.571429    4251.0      71.0 2016-03-01   534618.84   595477.92   589947.05   573347.936667                                    13158.333333             100428.687433                     474994.590000                       NaN                        NaN                       550635.554444                                 NaN                          NaN       572635.646667\n",
       "3         1  2016    4                  534618.84    12726.0   909.000000    3688.0       7.0 2016-04-01   595477.92   589947.05   563814.03   583079.666667                                    13055.666667              99391.933058                     505923.080000                       NaN                        NaN                       576354.416667                                 NaN                          NaN       573347.936667\n",
       "4         1  2016    5                  595477.92    13978.0   998.428571    4345.0      84.0 2016-05-01   589947.05   563814.03   625181.36   592980.813333                                    13911.333333              33145.991477                     572635.646667                       NaN                        NaN                       583136.138889                                 NaN                          NaN       583079.666667\n",
       "5         1  2016    6                  589947.05    14693.0  1049.500000    3894.0      95.0 2016-06-01   563814.03   625181.36   583509.12   590834.836667                                    13799.000000              33654.194794                     573347.936667                       NaN                        NaN                       588965.105556                       569800.330000                          NaN       592980.813333\n",
       "6         1  2016    7                  563814.03    13570.0   969.285714    3437.0      90.0 2016-07-01   625181.36   583509.12   611797.30   606829.260000                                    14080.333333              16912.161242                     583079.666667                 441833.37                        NaN                       596881.636667                       586618.026667                          NaN       590834.836667\n",
       "7         1  2016    8                  625181.36    13889.0   992.071429    4159.0      81.0 2016-08-01   583509.12   611797.30   876921.63   690742.683333                                    14050.666667              30795.942717                     592980.813333                 395340.22                        NaN                       629468.926667                       606302.532778                          NaN       606829.260000\n",
       "8         1  2016    9                  583509.12    13309.0   950.642857    3924.0      90.0 2016-09-01   611797.30   876921.63  1257613.00   915443.976667                                    13589.333333              31332.680883                     590834.836667                 587810.18                        NaN                       737671.973333                       663318.539444                          NaN       690742.683333\n",
       "9         1  2016   10                  611797.30    14709.0  1131.461538    3907.0      79.0 2016-10-01   876921.63  1257613.00   404108.77   846214.466667                                    13969.000000              21275.689477                     606829.260000                 534618.84                        NaN                       817467.042222                       707174.339444                          NaN       915443.976667"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_media_movel1 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['qtde_soma'].transform(lambda x: x.rolling(3).mean()))\n",
    "\n",
    "df_media_movel2 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_total_loja_anomes'].transform(lambda x: x.rolling(3).std()))\n",
    "\n",
    "df_media_movel3 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_total_loja_anomes'].transform(lambda x: x.rolling(3).mean()))\n",
    "\n",
    "df_media_movel4 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_total_loja_anomes'].transform(lambda x: x.shift(6)))\n",
    "\n",
    "df_media_movel5 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_total_loja_anomes'].transform(lambda x: x.shift(12)))\n",
    "\n",
    "df_media_movel6 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_target'].transform(lambda x: x.rolling(3).mean()))\n",
    "\n",
    "df_media_movel7 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_target'].transform(lambda x: x.rolling(6).mean()))\n",
    "\n",
    "df_media_movel8 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_target'].transform(lambda x: x.shift(12)))\n",
    "\n",
    "df_media_movel9 = pd.DataFrame(df_faturamento.groupby(['cod_loja'])\\\n",
    "                              ['receita_target'].transform(lambda x: x.shift(1)))\n",
    "\n",
    "df_faturamento['soma_quantidade_items_loja_media_movel_3_meses'] = df_media_movel1['qtde_soma'].values.tolist()\n",
    "df_faturamento['receita_loja_std_3_meses'] = df_media_movel2['receita_total_loja_anomes'].values.tolist()\n",
    "df_faturamento['receita_loja_media_movel_3_meses'] = df_media_movel3['receita_total_loja_anomes'].values.tolist()\n",
    "df_faturamento['receita_loja_lag_6_meses'] = df_media_movel4['receita_total_loja_anomes'].values.tolist()\n",
    "df_faturamento['receita_loja_lag_12_meses'] = df_media_movel5['receita_total_loja_anomes'].values.tolist()\n",
    "df_faturamento['receita_target_media_movel_3_meses'] = df_media_movel6['receita_target'].values.tolist()\n",
    "df_faturamento['receita_target_media_movel_6_meses'] = df_media_movel7['receita_target'].values.tolist()\n",
    "df_faturamento['receita_target_lag_12_meses'] = df_media_movel8['receita_target'].values.tolist()\n",
    "df_faturamento['receita_loja_lag_1'] = df_media_movel9['receita_target'].values.tolist()\n",
    "\n",
    "df_faturamento.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73644, 22)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>cod_loja</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>receita_total_loja_anomes</th>\n",
       "      <th>qtde_soma</th>\n",
       "      <th>qtde_media</th>\n",
       "      <th>qtde_min</th>\n",
       "      <th>qtde_max</th>\n",
       "      <th>ano_mes</th>\n",
       "      <th>receita_m1</th>\n",
       "      <th>receita_m2</th>\n",
       "      <th>receita_m3</th>\n",
       "      <th>receita_target</th>\n",
       "      <th>soma_quantidade_items_loja_media_movel_3_meses</th>\n",
       "      <th>receita_loja_std_3_meses</th>\n",
       "      <th>receita_loja_media_movel_3_meses</th>\n",
       "      <th>receita_loja_lag_6_meses</th>\n",
       "      <th>receita_loja_lag_12_meses</th>\n",
       "      <th>receita_target_media_movel_3_meses</th>\n",
       "      <th>receita_target_media_movel_6_meses</th>\n",
       "      <th>receita_target_lag_12_meses</th>\n",
       "      <th>receita_loja_lag_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>441833.37</td>\n",
       "      <td>13034.0</td>\n",
       "      <td>931.000000</td>\n",
       "      <td>3820.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>11411.0</td>\n",
       "      <td>815.071429</td>\n",
       "      <td>2637.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>505923.080000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>15030.0</td>\n",
       "      <td>1073.571429</td>\n",
       "      <td>4251.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>573347.936667</td>\n",
       "      <td>13158.333333</td>\n",
       "      <td>100428.687433</td>\n",
       "      <td>474994.590000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>550635.554444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>572635.646667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>12726.0</td>\n",
       "      <td>909.000000</td>\n",
       "      <td>3688.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>583079.666667</td>\n",
       "      <td>13055.666667</td>\n",
       "      <td>99391.933058</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>576354.416667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>573347.936667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>13978.0</td>\n",
       "      <td>998.428571</td>\n",
       "      <td>4345.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>625181.36</td>\n",
       "      <td>592980.813333</td>\n",
       "      <td>13911.333333</td>\n",
       "      <td>33145.991477</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583136.138889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583079.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>4</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>173683.70</td>\n",
       "      <td>4148.0</td>\n",
       "      <td>319.076923</td>\n",
       "      <td>947.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2380.333333</td>\n",
       "      <td>68932.752823</td>\n",
       "      <td>94818.060000</td>\n",
       "      <td>59034.85</td>\n",
       "      <td>168548.71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47384.846667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>5</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>248834.01</td>\n",
       "      <td>5800.0</td>\n",
       "      <td>414.285714</td>\n",
       "      <td>2040.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>182297.02</td>\n",
       "      <td>210579.83</td>\n",
       "      <td>202545.65</td>\n",
       "      <td>198474.166667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>5</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>182297.02</td>\n",
       "      <td>3896.0</td>\n",
       "      <td>278.285714</td>\n",
       "      <td>1059.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>210579.83</td>\n",
       "      <td>202545.65</td>\n",
       "      <td>343893.54</td>\n",
       "      <td>252339.673333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>198474.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>5</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>210579.83</td>\n",
       "      <td>3920.0</td>\n",
       "      <td>280.000000</td>\n",
       "      <td>1287.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>202545.65</td>\n",
       "      <td>343893.54</td>\n",
       "      <td>256717.01</td>\n",
       "      <td>267718.733333</td>\n",
       "      <td>4538.666667</td>\n",
       "      <td>33392.790308</td>\n",
       "      <td>213903.620000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>239510.857778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252339.673333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>5</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>202545.65</td>\n",
       "      <td>3870.0</td>\n",
       "      <td>276.428571</td>\n",
       "      <td>1215.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>343893.54</td>\n",
       "      <td>256717.01</td>\n",
       "      <td>223261.58</td>\n",
       "      <td>274624.043333</td>\n",
       "      <td>3895.333333</td>\n",
       "      <td>14574.363375</td>\n",
       "      <td>198474.166667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>264894.150000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>267718.733333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    cod_loja   ano  mes  receita_total_loja_anomes  qtde_soma   qtde_media  qtde_min  qtde_max    ano_mes  receita_m1  receita_m2  receita_m3  receita_target  soma_quantidade_items_loja_media_movel_3_meses  receita_loja_std_3_meses  receita_loja_media_movel_3_meses  receita_loja_lag_6_meses  receita_loja_lag_12_meses  receita_target_media_movel_3_meses  receita_target_media_movel_6_meses  receita_target_lag_12_meses  receita_loja_lag_1\n",
       "0          1  2016    1                  441833.37    13034.0   931.000000    3820.0      24.0 2016-01-01   395340.22   587810.18   534618.84   505923.080000                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN                 NaN\n",
       "1          1  2016    2                  395340.22    11411.0   815.071429    2637.0      24.0 2016-02-01   587810.18   534618.84   595477.92   572635.646667                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN       505923.080000\n",
       "2          1  2016    3                  587810.18    15030.0  1073.571429    4251.0      71.0 2016-03-01   534618.84   595477.92   589947.05   573347.936667                                    13158.333333             100428.687433                     474994.590000                       NaN                        NaN                       550635.554444                                 NaN                          NaN       572635.646667\n",
       "3          1  2016    4                  534618.84    12726.0   909.000000    3688.0       7.0 2016-04-01   595477.92   589947.05   563814.03   583079.666667                                    13055.666667              99391.933058                     505923.080000                       NaN                        NaN                       576354.416667                                 NaN                          NaN       573347.936667\n",
       "4          1  2016    5                  595477.92    13978.0   998.428571    4345.0      84.0 2016-05-01   589947.05   563814.03   625181.36   592980.813333                                    13911.333333              33145.991477                     572635.646667                       NaN                        NaN                       583136.138889                                 NaN                          NaN       583079.666667\n",
       "..       ...   ...  ...                        ...        ...          ...       ...       ...        ...         ...         ...         ...             ...                                             ...                       ...                               ...                       ...                        ...                                 ...                                 ...                          ...                 ...\n",
       "95         4  2017   12                  173683.70     4148.0   319.076923     947.0      13.0 2017-12-01         NaN         NaN         NaN             NaN                                     2380.333333              68932.752823                      94818.060000                  59034.85                  168548.71                                 NaN                                 NaN                 47384.846667                 NaN\n",
       "96         5  2016    1                  248834.01     5800.0   414.285714    2040.0       7.0 2016-01-01   182297.02   210579.83   202545.65   198474.166667                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN                 NaN\n",
       "97         5  2016    2                  182297.02     3896.0   278.285714    1059.0      13.0 2016-02-01   210579.83   202545.65   343893.54   252339.673333                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN       198474.166667\n",
       "98         5  2016    3                  210579.83     3920.0   280.000000    1287.0       4.0 2016-03-01   202545.65   343893.54   256717.01   267718.733333                                     4538.666667              33392.790308                     213903.620000                       NaN                        NaN                       239510.857778                                 NaN                          NaN       252339.673333\n",
       "99         5  2016    4                  202545.65     3870.0   276.428571    1215.0       7.0 2016-04-01   343893.54   256717.01   223261.58   274624.043333                                     3895.333333              14574.363375                     198474.166667                       NaN                        NaN                       264894.150000                                 NaN                          NaN       267718.733333\n",
       "\n",
       "[100 rows x 22 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73644, 22)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2016-01-01T00:00:00.000000000', '2016-02-01T00:00:00.000000000',\n",
       "       '2016-03-01T00:00:00.000000000', '2016-04-01T00:00:00.000000000',\n",
       "       '2016-05-01T00:00:00.000000000', '2016-06-01T00:00:00.000000000',\n",
       "       '2016-07-01T00:00:00.000000000', '2016-08-01T00:00:00.000000000',\n",
       "       '2016-09-01T00:00:00.000000000', '2016-10-01T00:00:00.000000000',\n",
       "       '2016-11-01T00:00:00.000000000', '2016-12-01T00:00:00.000000000',\n",
       "       '2017-01-01T00:00:00.000000000', '2017-02-01T00:00:00.000000000',\n",
       "       '2017-03-01T00:00:00.000000000', '2017-04-01T00:00:00.000000000',\n",
       "       '2017-05-01T00:00:00.000000000', '2017-06-01T00:00:00.000000000',\n",
       "       '2017-07-01T00:00:00.000000000', '2017-08-01T00:00:00.000000000',\n",
       "       '2017-09-01T00:00:00.000000000', '2017-10-01T00:00:00.000000000',\n",
       "       '2017-11-01T00:00:00.000000000', '2017-12-01T00:00:00.000000000'],\n",
       "      dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento['ano_mes'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### junta variáveis explicativas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_faturamento = pd.merge(df_faturamento,\n",
    "                          df_lojas,\n",
    "                          on='cod_loja',\n",
    "                          how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73644, 43)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>cod_loja</th>\n",
       "      <th>ano</th>\n",
       "      <th>mes</th>\n",
       "      <th>receita_total_loja_anomes</th>\n",
       "      <th>qtde_soma</th>\n",
       "      <th>qtde_media</th>\n",
       "      <th>qtde_min</th>\n",
       "      <th>qtde_max</th>\n",
       "      <th>ano_mes</th>\n",
       "      <th>receita_m1</th>\n",
       "      <th>receita_m2</th>\n",
       "      <th>receita_m3</th>\n",
       "      <th>receita_target</th>\n",
       "      <th>soma_quantidade_items_loja_media_movel_3_meses</th>\n",
       "      <th>receita_loja_std_3_meses</th>\n",
       "      <th>receita_loja_media_movel_3_meses</th>\n",
       "      <th>receita_loja_lag_6_meses</th>\n",
       "      <th>receita_loja_lag_12_meses</th>\n",
       "      <th>receita_target_media_movel_3_meses</th>\n",
       "      <th>receita_target_media_movel_6_meses</th>\n",
       "      <th>receita_target_lag_12_meses</th>\n",
       "      <th>receita_loja_lag_1</th>\n",
       "      <th>cod_ap</th>\n",
       "      <th>cod_municipio</th>\n",
       "      <th>feature_01</th>\n",
       "      <th>feature_02</th>\n",
       "      <th>feature_03</th>\n",
       "      <th>feature_04</th>\n",
       "      <th>feature_05</th>\n",
       "      <th>feature_06</th>\n",
       "      <th>feature_07</th>\n",
       "      <th>feature_08</th>\n",
       "      <th>feature_09</th>\n",
       "      <th>feature_10</th>\n",
       "      <th>feature_11</th>\n",
       "      <th>feature_12</th>\n",
       "      <th>feature_13</th>\n",
       "      <th>feature_14</th>\n",
       "      <th>feature_15</th>\n",
       "      <th>feature_16</th>\n",
       "      <th>feature_17</th>\n",
       "      <th>feature_18</th>\n",
       "      <th>feature_04_vezes_06</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>441833.37</td>\n",
       "      <td>13034.0</td>\n",
       "      <td>931.000000</td>\n",
       "      <td>3820.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4125506005002</td>\n",
       "      <td>4125506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>True</td>\n",
       "      <td>21.76</td>\n",
       "      <td>FEATURE_05_VALUE_04</td>\n",
       "      <td>31</td>\n",
       "      <td>0.168922</td>\n",
       "      <td>0.167659</td>\n",
       "      <td>115.958848</td>\n",
       "      <td>2.349821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>674.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>395340.22</td>\n",
       "      <td>11411.0</td>\n",
       "      <td>815.071429</td>\n",
       "      <td>2637.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2016-02-01</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>4125506005002</td>\n",
       "      <td>4125506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>True</td>\n",
       "      <td>21.76</td>\n",
       "      <td>FEATURE_05_VALUE_04</td>\n",
       "      <td>31</td>\n",
       "      <td>0.168922</td>\n",
       "      <td>0.167659</td>\n",
       "      <td>115.958848</td>\n",
       "      <td>2.349821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>674.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>587810.18</td>\n",
       "      <td>15030.0</td>\n",
       "      <td>1073.571429</td>\n",
       "      <td>4251.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2016-03-01</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>573347.936667</td>\n",
       "      <td>13158.333333</td>\n",
       "      <td>100428.687433</td>\n",
       "      <td>474994.590000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>550635.554444</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>4125506005002</td>\n",
       "      <td>4125506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>True</td>\n",
       "      <td>21.76</td>\n",
       "      <td>FEATURE_05_VALUE_04</td>\n",
       "      <td>31</td>\n",
       "      <td>0.168922</td>\n",
       "      <td>0.167659</td>\n",
       "      <td>115.958848</td>\n",
       "      <td>2.349821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>674.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>534618.84</td>\n",
       "      <td>12726.0</td>\n",
       "      <td>909.000000</td>\n",
       "      <td>3688.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>583079.666667</td>\n",
       "      <td>13055.666667</td>\n",
       "      <td>99391.933058</td>\n",
       "      <td>505923.080000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>576354.416667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>573347.936667</td>\n",
       "      <td>4125506005002</td>\n",
       "      <td>4125506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>True</td>\n",
       "      <td>21.76</td>\n",
       "      <td>FEATURE_05_VALUE_04</td>\n",
       "      <td>31</td>\n",
       "      <td>0.168922</td>\n",
       "      <td>0.167659</td>\n",
       "      <td>115.958848</td>\n",
       "      <td>2.349821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>674.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>595477.92</td>\n",
       "      <td>13978.0</td>\n",
       "      <td>998.428571</td>\n",
       "      <td>4345.0</td>\n",
       "      <td>84.0</td>\n",
       "      <td>2016-05-01</td>\n",
       "      <td>589947.05</td>\n",
       "      <td>563814.03</td>\n",
       "      <td>625181.36</td>\n",
       "      <td>592980.813333</td>\n",
       "      <td>13911.333333</td>\n",
       "      <td>33145.991477</td>\n",
       "      <td>572635.646667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583136.138889</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>583079.666667</td>\n",
       "      <td>4125506005002</td>\n",
       "      <td>4125506</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FEATURE_02_VALUE_04</td>\n",
       "      <td>True</td>\n",
       "      <td>21.76</td>\n",
       "      <td>FEATURE_05_VALUE_04</td>\n",
       "      <td>31</td>\n",
       "      <td>0.168922</td>\n",
       "      <td>0.167659</td>\n",
       "      <td>115.958848</td>\n",
       "      <td>2.349821</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>674.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod_loja   ano  mes  receita_total_loja_anomes  qtde_soma   qtde_media  qtde_min  qtde_max    ano_mes  receita_m1  receita_m2  receita_m3  receita_target  soma_quantidade_items_loja_media_movel_3_meses  receita_loja_std_3_meses  receita_loja_media_movel_3_meses  receita_loja_lag_6_meses  receita_loja_lag_12_meses  receita_target_media_movel_3_meses  receita_target_media_movel_6_meses  receita_target_lag_12_meses  receita_loja_lag_1         cod_ap  cod_municipio feature_01           feature_02  feature_03  feature_04           feature_05  feature_06  feature_07  feature_08  feature_09  feature_10 feature_11 feature_12  feature_13  feature_14  feature_15  feature_16  feature_17  feature_18  feature_04_vezes_06\n",
       "0         1  2016    1                  441833.37    13034.0   931.000000    3820.0      24.0 2016-01-01   395340.22   587810.18   534618.84   505923.080000                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN                 NaN  4125506005002        4125506        NaN  FEATURE_02_VALUE_04        True       21.76  FEATURE_05_VALUE_04          31    0.168922    0.167659  115.958848    2.349821        NaN        NaN         NaN         NaN         NaN         NaN         NaN         NaN               674.56\n",
       "1         1  2016    2                  395340.22    11411.0   815.071429    2637.0      24.0 2016-02-01   587810.18   534618.84   595477.92   572635.646667                                             NaN                       NaN                               NaN                       NaN                        NaN                                 NaN                                 NaN                          NaN       505923.080000  4125506005002        4125506        NaN  FEATURE_02_VALUE_04        True       21.76  FEATURE_05_VALUE_04          31    0.168922    0.167659  115.958848    2.349821        NaN        NaN         NaN         NaN         NaN         NaN         NaN         NaN               674.56\n",
       "2         1  2016    3                  587810.18    15030.0  1073.571429    4251.0      71.0 2016-03-01   534618.84   595477.92   589947.05   573347.936667                                    13158.333333             100428.687433                     474994.590000                       NaN                        NaN                       550635.554444                                 NaN                          NaN       572635.646667  4125506005002        4125506        NaN  FEATURE_02_VALUE_04        True       21.76  FEATURE_05_VALUE_04          31    0.168922    0.167659  115.958848    2.349821        NaN        NaN         NaN         NaN         NaN         NaN         NaN         NaN               674.56\n",
       "3         1  2016    4                  534618.84    12726.0   909.000000    3688.0       7.0 2016-04-01   595477.92   589947.05   563814.03   583079.666667                                    13055.666667              99391.933058                     505923.080000                       NaN                        NaN                       576354.416667                                 NaN                          NaN       573347.936667  4125506005002        4125506        NaN  FEATURE_02_VALUE_04        True       21.76  FEATURE_05_VALUE_04          31    0.168922    0.167659  115.958848    2.349821        NaN        NaN         NaN         NaN         NaN         NaN         NaN         NaN               674.56\n",
       "4         1  2016    5                  595477.92    13978.0   998.428571    4345.0      84.0 2016-05-01   589947.05   563814.03   625181.36   592980.813333                                    13911.333333              33145.991477                     572635.646667                       NaN                        NaN                       583136.138889                                 NaN                          NaN       583079.666667  4125506005002        4125506        NaN  FEATURE_02_VALUE_04        True       21.76  FEATURE_05_VALUE_04          31    0.168922    0.167659  115.958848    2.349821        NaN        NaN         NaN         NaN         NaN         NaN         NaN         NaN               674.56"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faturamento.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### separa base em treino e teste"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = df_faturamento.loc[df_faturamento['ano_mes'] <= '2017-09-01']\n",
    "df_test = df_faturamento.loc[df_faturamento['ano_mes'] == '2017-12-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(64313, 43)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_train = df_train.loc[(~df_train['receita_target'].isnull())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(64283, 43)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3123, 43)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2016-01-01T00:00:00.000000000', '2016-02-01T00:00:00.000000000',\n",
       "       '2016-03-01T00:00:00.000000000', '2016-04-01T00:00:00.000000000',\n",
       "       '2016-05-01T00:00:00.000000000', '2016-06-01T00:00:00.000000000',\n",
       "       '2016-07-01T00:00:00.000000000', '2016-08-01T00:00:00.000000000',\n",
       "       '2016-09-01T00:00:00.000000000', '2016-10-01T00:00:00.000000000',\n",
       "       '2016-11-01T00:00:00.000000000', '2016-12-01T00:00:00.000000000',\n",
       "       '2017-01-01T00:00:00.000000000', '2017-02-01T00:00:00.000000000',\n",
       "       '2017-03-01T00:00:00.000000000', '2017-04-01T00:00:00.000000000',\n",
       "       '2017-05-01T00:00:00.000000000', '2017-06-01T00:00:00.000000000',\n",
       "       '2017-07-01T00:00:00.000000000', '2017-08-01T00:00:00.000000000',\n",
       "       '2017-09-01T00:00:00.000000000'], dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train['ano_mes'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2017-12-01T00:00:00.000000000'], dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test['ano_mes'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Divide dados em variáveis explicativas e target"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "X_train: (64283, 19)\n",
      "y_train: (64283, 1)\n",
      "X_test: (3123, 19)\n",
      "y_test: (3123, 1)\n"
     ]
    }
   ],
   "source": [
    "X_train = df_train[lista_vars_explicativas].copy()\n",
    "y_train = df_train[['receita_target']].copy()\n",
    "\n",
    "X_test = df_test[lista_vars_explicativas].copy()\n",
    "y_test = df_test[['receita_target']].copy()\n",
    "\n",
    "print (f'X_train: {X_train.shape}')\n",
    "print (f'y_train: {y_train.shape}')\n",
    "print (f'X_test: {X_test.shape}')\n",
    "print (f'y_test: {y_test.shape}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Out of time Cross Validation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fold: 1\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttraining's rmse: 14136.6\tvalid_1's rmse: 15784.1\n",
      "[200]\ttraining's rmse: 10162\tvalid_1's rmse: 11659.4\n",
      "[300]\ttraining's rmse: 9123.75\tvalid_1's rmse: 10978.4\n",
      "[400]\ttraining's rmse: 8499.23\tvalid_1's rmse: 10530.3\n",
      "[500]\ttraining's rmse: 8053.37\tvalid_1's rmse: 10319.5\n",
      "[600]\ttraining's rmse: 7687.09\tvalid_1's rmse: 10169.5\n",
      "[700]\ttraining's rmse: 7386.92\tvalid_1's rmse: 10062.8\n",
      "[800]\ttraining's rmse: 7124.3\tvalid_1's rmse: 9941.86\n",
      "[900]\ttraining's rmse: 6867.06\tvalid_1's rmse: 9806.57\n",
      "[1000]\ttraining's rmse: 6640.9\tvalid_1's rmse: 9720.92\n",
      "[1100]\ttraining's rmse: 6431.33\tvalid_1's rmse: 9641.34\n",
      "[1200]\ttraining's rmse: 6232.53\tvalid_1's rmse: 9558.56\n",
      "[1300]\ttraining's rmse: 6074.24\tvalid_1's rmse: 9515.04\n",
      "[1400]\ttraining's rmse: 5909.95\tvalid_1's rmse: 9474.14\n",
      "[1500]\ttraining's rmse: 5763.42\tvalid_1's rmse: 9425.81\n",
      "[1600]\ttraining's rmse: 5631.35\tvalid_1's rmse: 9387.76\n",
      "[1700]\ttraining's rmse: 5502.99\tvalid_1's rmse: 9356.77\n",
      "[1800]\ttraining's rmse: 5382.93\tvalid_1's rmse: 9319.76\n",
      "[1900]\ttraining's rmse: 5271.52\tvalid_1's rmse: 9282.63\n",
      "[2000]\ttraining's rmse: 5171.71\tvalid_1's rmse: 9256.56\n",
      "[2100]\ttraining's rmse: 5077.41\tvalid_1's rmse: 9231.59\n",
      "[2200]\ttraining's rmse: 4985.19\tvalid_1's rmse: 9204.17\n",
      "[2300]\ttraining's rmse: 4900.84\tvalid_1's rmse: 9182.08\n",
      "[2400]\ttraining's rmse: 4815.44\tvalid_1's rmse: 9150.79\n",
      "[2500]\ttraining's rmse: 4741.66\tvalid_1's rmse: 9135.08\n",
      "[2600]\ttraining's rmse: 4670.9\tvalid_1's rmse: 9112.18\n",
      "[2700]\ttraining's rmse: 4598.58\tvalid_1's rmse: 9096.46\n",
      "[2800]\ttraining's rmse: 4524.99\tvalid_1's rmse: 9079.81\n",
      "[2900]\ttraining's rmse: 4455.72\tvalid_1's rmse: 9073.27\n",
      "[3000]\ttraining's rmse: 4386.38\tvalid_1's rmse: 9062.36\n",
      "[3100]\ttraining's rmse: 4319.95\tvalid_1's rmse: 9048.77\n",
      "[3200]\ttraining's rmse: 4252.89\tvalid_1's rmse: 9031.21\n",
      "[3300]\ttraining's rmse: 4196.44\tvalid_1's rmse: 9017.4\n",
      "[3400]\ttraining's rmse: 4140.63\tvalid_1's rmse: 9003.62\n",
      "[3500]\ttraining's rmse: 4086.4\tvalid_1's rmse: 8989.95\n",
      "[3600]\ttraining's rmse: 4025.93\tvalid_1's rmse: 8979.88\n",
      "[3700]\ttraining's rmse: 3975.7\tvalid_1's rmse: 8967.36\n",
      "[3800]\ttraining's rmse: 3924.37\tvalid_1's rmse: 8960.12\n",
      "[3900]\ttraining's rmse: 3871.77\tvalid_1's rmse: 8948.56\n",
      "[4000]\ttraining's rmse: 3821.8\tvalid_1's rmse: 8940.03\n",
      "[4100]\ttraining's rmse: 3769.91\tvalid_1's rmse: 8925.77\n",
      "[4200]\ttraining's rmse: 3725.2\tvalid_1's rmse: 8919.72\n",
      "[4300]\ttraining's rmse: 3677.77\tvalid_1's rmse: 8910.1\n",
      "[4400]\ttraining's rmse: 3633.76\tvalid_1's rmse: 8903.17\n",
      "[4500]\ttraining's rmse: 3590.8\tvalid_1's rmse: 8898.87\n",
      "[4600]\ttraining's rmse: 3551.26\tvalid_1's rmse: 8891.47\n",
      "[4700]\ttraining's rmse: 3510.43\tvalid_1's rmse: 8884.28\n",
      "[4800]\ttraining's rmse: 3471.15\tvalid_1's rmse: 8871.92\n",
      "[4900]\ttraining's rmse: 3433.53\tvalid_1's rmse: 8861.95\n",
      "[5000]\ttraining's rmse: 3399.43\tvalid_1's rmse: 8853.7\n",
      "[5100]\ttraining's rmse: 3361.98\tvalid_1's rmse: 8845.78\n",
      "[5200]\ttraining's rmse: 3324.32\tvalid_1's rmse: 8841.75\n",
      "[5300]\ttraining's rmse: 3285.46\tvalid_1's rmse: 8835.31\n",
      "Early stopping, best iteration is:\n",
      "[5294]\ttraining's rmse: 3287.65\tvalid_1's rmse: 8835.14\n",
      "val rmse score is 8835.140434426095\n",
      "Fold: 2\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttraining's rmse: 13665.9\tvalid_1's rmse: 13418.8\n",
      "[200]\ttraining's rmse: 9795.19\tvalid_1's rmse: 10141.8\n",
      "[300]\ttraining's rmse: 8859.66\tvalid_1's rmse: 9475.17\n",
      "[400]\ttraining's rmse: 8320.64\tvalid_1's rmse: 9114.25\n",
      "[500]\ttraining's rmse: 7905.74\tvalid_1's rmse: 8860.65\n",
      "[600]\ttraining's rmse: 7585.74\tvalid_1's rmse: 8679.44\n",
      "[700]\ttraining's rmse: 7316.04\tvalid_1's rmse: 8538.89\n",
      "[800]\ttraining's rmse: 7068.44\tvalid_1's rmse: 8392.13\n",
      "[900]\ttraining's rmse: 6851.68\tvalid_1's rmse: 8276.2\n",
      "[1000]\ttraining's rmse: 6674.93\tvalid_1's rmse: 8176.47\n",
      "[1100]\ttraining's rmse: 6507.43\tvalid_1's rmse: 8080.6\n",
      "[1200]\ttraining's rmse: 6362.05\tvalid_1's rmse: 8001.44\n",
      "[1300]\ttraining's rmse: 6227.01\tvalid_1's rmse: 7925.43\n",
      "[1400]\ttraining's rmse: 6102.18\tvalid_1's rmse: 7869.23\n",
      "[1500]\ttraining's rmse: 5983.5\tvalid_1's rmse: 7807.79\n",
      "[1600]\ttraining's rmse: 5879.78\tvalid_1's rmse: 7754.69\n",
      "[1700]\ttraining's rmse: 5776.29\tvalid_1's rmse: 7716.02\n",
      "[1800]\ttraining's rmse: 5681\tvalid_1's rmse: 7669.06\n",
      "[1900]\ttraining's rmse: 5591.03\tvalid_1's rmse: 7621.76\n",
      "[2000]\ttraining's rmse: 5504.86\tvalid_1's rmse: 7584.63\n",
      "[2100]\ttraining's rmse: 5427.88\tvalid_1's rmse: 7552.57\n",
      "[2200]\ttraining's rmse: 5351.31\tvalid_1's rmse: 7524.97\n",
      "[2300]\ttraining's rmse: 5273.18\tvalid_1's rmse: 7492.66\n",
      "[2400]\ttraining's rmse: 5203.34\tvalid_1's rmse: 7464.18\n",
      "[2500]\ttraining's rmse: 5133.55\tvalid_1's rmse: 7438.07\n",
      "[2600]\ttraining's rmse: 5064.29\tvalid_1's rmse: 7415.54\n",
      "[2700]\ttraining's rmse: 5005.26\tvalid_1's rmse: 7389.81\n",
      "[2800]\ttraining's rmse: 4947.14\tvalid_1's rmse: 7375.13\n",
      "[2900]\ttraining's rmse: 4891.79\tvalid_1's rmse: 7357.06\n",
      "[3000]\ttraining's rmse: 4835.09\tvalid_1's rmse: 7341.48\n",
      "[3100]\ttraining's rmse: 4779.13\tvalid_1's rmse: 7320.46\n",
      "[3200]\ttraining's rmse: 4725.19\tvalid_1's rmse: 7305.05\n",
      "[3300]\ttraining's rmse: 4673.79\tvalid_1's rmse: 7290.29\n",
      "[3400]\ttraining's rmse: 4624.58\tvalid_1's rmse: 7277.88\n",
      "[3500]\ttraining's rmse: 4578.28\tvalid_1's rmse: 7265.41\n",
      "[3600]\ttraining's rmse: 4532.1\tvalid_1's rmse: 7248.72\n",
      "[3700]\ttraining's rmse: 4487.57\tvalid_1's rmse: 7234.12\n",
      "[3800]\ttraining's rmse: 4443.16\tvalid_1's rmse: 7220.32\n",
      "[3900]\ttraining's rmse: 4395.91\tvalid_1's rmse: 7204.71\n",
      "[4000]\ttraining's rmse: 4355.02\tvalid_1's rmse: 7190.3\n",
      "[4100]\ttraining's rmse: 4317.51\tvalid_1's rmse: 7178.15\n",
      "[4200]\ttraining's rmse: 4279.09\tvalid_1's rmse: 7168.08\n",
      "[4300]\ttraining's rmse: 4239.77\tvalid_1's rmse: 7156.02\n",
      "[4400]\ttraining's rmse: 4202.23\tvalid_1's rmse: 7147.06\n",
      "[4500]\ttraining's rmse: 4165.06\tvalid_1's rmse: 7137.97\n",
      "[4600]\ttraining's rmse: 4127.77\tvalid_1's rmse: 7129.59\n",
      "[4700]\ttraining's rmse: 4093.99\tvalid_1's rmse: 7124.43\n",
      "[4800]\ttraining's rmse: 4060.37\tvalid_1's rmse: 7114.92\n",
      "[4900]\ttraining's rmse: 4029.8\tvalid_1's rmse: 7108.62\n",
      "[5000]\ttraining's rmse: 3997.21\tvalid_1's rmse: 7100.88\n",
      "[5100]\ttraining's rmse: 3964\tvalid_1's rmse: 7096.99\n",
      "[5200]\ttraining's rmse: 3928.39\tvalid_1's rmse: 7093.96\n",
      "[5300]\ttraining's rmse: 3896.67\tvalid_1's rmse: 7089.65\n",
      "Early stopping, best iteration is:\n",
      "[5343]\ttraining's rmse: 3884.47\tvalid_1's rmse: 7086.72\n",
      "val rmse score is 7086.720341413141\n",
      "Fold: 3\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttraining's rmse: 12947.9\tvalid_1's rmse: 15538.2\n",
      "[200]\ttraining's rmse: 9249.23\tvalid_1's rmse: 10866.6\n",
      "[300]\ttraining's rmse: 8394.44\tvalid_1's rmse: 10026.8\n",
      "[400]\ttraining's rmse: 7900.01\tvalid_1's rmse: 9620.66\n",
      "[500]\ttraining's rmse: 7553.87\tvalid_1's rmse: 9378.79\n",
      "[600]\ttraining's rmse: 7289.97\tvalid_1's rmse: 9196.41\n",
      "[700]\ttraining's rmse: 7068.53\tvalid_1's rmse: 9060.19\n",
      "[800]\ttraining's rmse: 6862.65\tvalid_1's rmse: 8916.67\n",
      "[900]\ttraining's rmse: 6680.42\tvalid_1's rmse: 8802.33\n",
      "[1000]\ttraining's rmse: 6519.25\tvalid_1's rmse: 8710.53\n",
      "[1100]\ttraining's rmse: 6368.28\tvalid_1's rmse: 8614.71\n",
      "[1200]\ttraining's rmse: 6233.58\tvalid_1's rmse: 8532.66\n",
      "[1300]\ttraining's rmse: 6115.04\tvalid_1's rmse: 8484.02\n",
      "[1400]\ttraining's rmse: 6005.09\tvalid_1's rmse: 8423.81\n",
      "[1500]\ttraining's rmse: 5913.45\tvalid_1's rmse: 8378.55\n",
      "[1600]\ttraining's rmse: 5814.47\tvalid_1's rmse: 8342.21\n",
      "[1700]\ttraining's rmse: 5729.78\tvalid_1's rmse: 8307.94\n",
      "[1800]\ttraining's rmse: 5641.35\tvalid_1's rmse: 8273.08\n",
      "[1900]\ttraining's rmse: 5566.68\tvalid_1's rmse: 8253.82\n",
      "[2000]\ttraining's rmse: 5493.05\tvalid_1's rmse: 8218.59\n",
      "[2100]\ttraining's rmse: 5424.27\tvalid_1's rmse: 8186.57\n",
      "[2200]\ttraining's rmse: 5353.27\tvalid_1's rmse: 8156.36\n",
      "[2300]\ttraining's rmse: 5290.09\tvalid_1's rmse: 8132.29\n",
      "[2400]\ttraining's rmse: 5231.51\tvalid_1's rmse: 8109.03\n",
      "[2500]\ttraining's rmse: 5169.87\tvalid_1's rmse: 8086.54\n",
      "[2600]\ttraining's rmse: 5113.2\tvalid_1's rmse: 8058.77\n",
      "[2700]\ttraining's rmse: 5058.88\tvalid_1's rmse: 8035.78\n",
      "[2800]\ttraining's rmse: 5005.95\tvalid_1's rmse: 8021.01\n",
      "[2900]\ttraining's rmse: 4955.82\tvalid_1's rmse: 7999.47\n",
      "[3000]\ttraining's rmse: 4905.66\tvalid_1's rmse: 7990.89\n",
      "[3100]\ttraining's rmse: 4858.38\tvalid_1's rmse: 7972.75\n",
      "[3200]\ttraining's rmse: 4809.85\tvalid_1's rmse: 7956.41\n",
      "[3300]\ttraining's rmse: 4763.3\tvalid_1's rmse: 7941.08\n",
      "[3400]\ttraining's rmse: 4719.37\tvalid_1's rmse: 7932.02\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[3500]\ttraining's rmse: 4678.84\tvalid_1's rmse: 7918.2\n",
      "[3600]\ttraining's rmse: 4637.59\tvalid_1's rmse: 7909.92\n",
      "[3700]\ttraining's rmse: 4599.51\tvalid_1's rmse: 7894.51\n",
      "[3800]\ttraining's rmse: 4559.32\tvalid_1's rmse: 7883.44\n",
      "[3900]\ttraining's rmse: 4522.18\tvalid_1's rmse: 7872.59\n",
      "[4000]\ttraining's rmse: 4486.26\tvalid_1's rmse: 7863.85\n",
      "[4100]\ttraining's rmse: 4453.3\tvalid_1's rmse: 7854.81\n",
      "[4200]\ttraining's rmse: 4418\tvalid_1's rmse: 7846.24\n",
      "[4300]\ttraining's rmse: 4387.01\tvalid_1's rmse: 7838.2\n",
      "Early stopping, best iteration is:\n",
      "[4338]\ttraining's rmse: 4374.25\tvalid_1's rmse: 7833.64\n",
      "val rmse score is 7833.640399740041\n",
      "Fold: 4\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttraining's rmse: 13133.2\tvalid_1's rmse: 12751.9\n",
      "[200]\ttraining's rmse: 9347.21\tvalid_1's rmse: 9384.68\n",
      "[300]\ttraining's rmse: 8434.19\tvalid_1's rmse: 8757.94\n",
      "[400]\ttraining's rmse: 7935.12\tvalid_1's rmse: 8439.67\n",
      "[500]\ttraining's rmse: 7592.34\tvalid_1's rmse: 8308.65\n",
      "[600]\ttraining's rmse: 7291.75\tvalid_1's rmse: 8151.14\n",
      "Early stopping, best iteration is:\n",
      "[601]\ttraining's rmse: 7288.08\tvalid_1's rmse: 8147.68\n",
      "val rmse score is 8147.679962266376\n",
      "Fold: 5\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[100]\ttraining's rmse: 12744\tvalid_1's rmse: 18559.4\n",
      "[200]\ttraining's rmse: 9083.48\tvalid_1's rmse: 15939\n",
      "[300]\ttraining's rmse: 8263.01\tvalid_1's rmse: 15427.2\n",
      "[400]\ttraining's rmse: 7809.52\tvalid_1's rmse: 15204\n",
      "[500]\ttraining's rmse: 7500.34\tvalid_1's rmse: 15042.4\n",
      "[600]\ttraining's rmse: 7234.36\tvalid_1's rmse: 15020\n",
      "[700]\ttraining's rmse: 7012.8\tvalid_1's rmse: 14941.5\n",
      "[800]\ttraining's rmse: 6816.55\tvalid_1's rmse: 14886.3\n",
      "[900]\ttraining's rmse: 6641.89\tvalid_1's rmse: 14838.2\n",
      "[1000]\ttraining's rmse: 6484.18\tvalid_1's rmse: 14784.8\n",
      "[1100]\ttraining's rmse: 6346.55\tvalid_1's rmse: 14736.6\n",
      "[1200]\ttraining's rmse: 6229.97\tvalid_1's rmse: 14704.3\n",
      "[1300]\ttraining's rmse: 6119.64\tvalid_1's rmse: 14664.9\n",
      "[1400]\ttraining's rmse: 6021.25\tvalid_1's rmse: 14649.5\n",
      "Early stopping, best iteration is:\n",
      "[1389]\ttraining's rmse: 6032.4\tvalid_1's rmse: 14649\n",
      "val rmse score is 14649.044985916064\n",
      "mean rmse score over folds is 9310.445224752342\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-28-06f5048668b2>:28: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_test['pred'] = y_preds\n"
     ]
    }
   ],
   "source": [
    "folds = TimeSeriesSplit(n_splits=n_fold)\n",
    "\n",
    "splits = folds.split(X_train, y_train)\n",
    "y_preds = np.zeros(X_test.shape[0])\n",
    "y_oof = np.zeros(X_train.shape[0])\n",
    "columns = X_train.columns.tolist()\n",
    "\n",
    "feature_importances = pd.DataFrame()\n",
    "feature_importances['feature'] = columns\n",
    "mean_score = []\n",
    "for fold_n, (train_index, valid_index) in enumerate(splits):\n",
    "    print('Fold:',fold_n+1)\n",
    "    X_train_tmp, X_valid_tmp = X_train[columns].iloc[train_index], X_train[columns].iloc[valid_index]\n",
    "    y_train_tmp, y_valid_tmp = y_train.iloc[train_index], y_train.iloc[valid_index]\n",
    "    dtrain = lgb.Dataset(X_train_tmp, label=y_train_tmp)\n",
    "    dvalid = lgb.Dataset(X_valid_tmp, label=y_valid_tmp)\n",
    "    clf = lgb.train(params, dtrain, 10000, valid_sets = [dtrain, dvalid], early_stopping_rounds = 50, verbose_eval=100)\n",
    "    feature_importances[f'fold_{fold_n + 1}'] = clf.feature_importance()\n",
    "    y_pred_valid = clf.predict(X_valid_tmp,num_iteration=clf.best_iteration)\n",
    "    y_oof[valid_index] = y_pred_valid\n",
    "    val_score = np.sqrt(mean_squared_error(y_pred_valid, y_valid_tmp))\n",
    "    print(f'val rmse score is {val_score}')\n",
    "    mean_score.append(val_score)\n",
    "    y_preds += clf.predict(X_test[columns], num_iteration=clf.best_iteration)/n_fold\n",
    "    del X_train_tmp, X_valid_tmp, y_train_tmp, y_valid_tmp\n",
    "    gc.collect()\n",
    "print('mean rmse score over folds is',np.mean(mean_score))\n",
    "df_test['pred'] = y_preds"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Prepara dados de teste para output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_resultados = df_test.copy()\n",
    "df_resultados['pred'] = y_preds\n",
    "df_resultados = df_resultados.reset_index()\n",
    "df_resultados = df_resultados[['cod_loja', 'pred']].copy()\n",
    "df_resultados.columns = ['cod_loja', 'faturamento']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>cod_loja</th>\n",
       "      <th>faturamento</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>282916.965038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>83572.813836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>230192.906579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>88639.154186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>207483.192548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>112</td>\n",
       "      <td>103023.708435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>113</td>\n",
       "      <td>235201.327003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>114</td>\n",
       "      <td>101710.280181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>115</td>\n",
       "      <td>88547.795238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>116</td>\n",
       "      <td>229676.237973</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    cod_loja    faturamento\n",
       "0          1  282916.965038\n",
       "1          2   83572.813836\n",
       "2          3  230192.906579\n",
       "3          4   88639.154186\n",
       "4          5  207483.192548\n",
       "..       ...            ...\n",
       "95       112  103023.708435\n",
       "96       113  235201.327003\n",
       "97       114  101710.280181\n",
       "98       115   88547.795238\n",
       "99       116  229676.237973\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultados.head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3123, 2)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultados.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_resultados['faturamento'] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3123, 2)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultados.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>cod_loja</th>\n",
       "      <th>faturamento</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>282916.965038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>83572.813836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>230192.906579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>88639.154186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>207483.192548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod_loja    faturamento\n",
       "0         1  282916.965038\n",
       "1         2   83572.813836\n",
       "2         3  230192.906579\n",
       "3         4   88639.154186\n",
       "4         5  207483.192548"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_resultados.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Salva resultado das predições em um csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_resultados.to_csv('desafio_1.csv', sep=';', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Importância das variáveis explicativas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>feature</th>\n",
       "      <th>fold_1</th>\n",
       "      <th>fold_2</th>\n",
       "      <th>fold_3</th>\n",
       "      <th>fold_4</th>\n",
       "      <th>fold_5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cod_municipio</td>\n",
       "      <td>1739</td>\n",
       "      <td>1792</td>\n",
       "      <td>1487</td>\n",
       "      <td>174</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mes</td>\n",
       "      <td>4211</td>\n",
       "      <td>4799</td>\n",
       "      <td>4082</td>\n",
       "      <td>881</td>\n",
       "      <td>1634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qtde_media</td>\n",
       "      <td>1189</td>\n",
       "      <td>1073</td>\n",
       "      <td>741</td>\n",
       "      <td>83</td>\n",
       "      <td>178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qtde_soma</td>\n",
       "      <td>1134</td>\n",
       "      <td>1070</td>\n",
       "      <td>693</td>\n",
       "      <td>145</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qtde_max</td>\n",
       "      <td>1154</td>\n",
       "      <td>1062</td>\n",
       "      <td>746</td>\n",
       "      <td>71</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>qtde_min</td>\n",
       "      <td>1982</td>\n",
       "      <td>1788</td>\n",
       "      <td>1386</td>\n",
       "      <td>167</td>\n",
       "      <td>457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>feature_04</td>\n",
       "      <td>1264</td>\n",
       "      <td>1097</td>\n",
       "      <td>871</td>\n",
       "      <td>142</td>\n",
       "      <td>323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>feature_08</td>\n",
       "      <td>957</td>\n",
       "      <td>805</td>\n",
       "      <td>668</td>\n",
       "      <td>39</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>feature_09</td>\n",
       "      <td>1294</td>\n",
       "      <td>1331</td>\n",
       "      <td>1052</td>\n",
       "      <td>79</td>\n",
       "      <td>242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>feature_14</td>\n",
       "      <td>969</td>\n",
       "      <td>946</td>\n",
       "      <td>724</td>\n",
       "      <td>63</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>feature_18</td>\n",
       "      <td>1044</td>\n",
       "      <td>1173</td>\n",
       "      <td>888</td>\n",
       "      <td>38</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>feature_04_vezes_06</td>\n",
       "      <td>1213</td>\n",
       "      <td>1328</td>\n",
       "      <td>1068</td>\n",
       "      <td>207</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>receita_target_lag_12_meses</td>\n",
       "      <td>1584</td>\n",
       "      <td>1396</td>\n",
       "      <td>1149</td>\n",
       "      <td>164</td>\n",
       "      <td>376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>receita_loja_std_3_meses</td>\n",
       "      <td>2391</td>\n",
       "      <td>2233</td>\n",
       "      <td>1838</td>\n",
       "      <td>249</td>\n",
       "      <td>535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>receita_target_media_movel_3_meses</td>\n",
       "      <td>5127</td>\n",
       "      <td>5386</td>\n",
       "      <td>4645</td>\n",
       "      <td>675</td>\n",
       "      <td>1603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>receita_total_loja_anomes</td>\n",
       "      <td>3133</td>\n",
       "      <td>3296</td>\n",
       "      <td>2540</td>\n",
       "      <td>348</td>\n",
       "      <td>961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>receita_loja_media_movel_3_meses</td>\n",
       "      <td>2140</td>\n",
       "      <td>2369</td>\n",
       "      <td>2062</td>\n",
       "      <td>133</td>\n",
       "      <td>431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>soma_quantidade_items_loja_media_movel_3_meses</td>\n",
       "      <td>1204</td>\n",
       "      <td>1222</td>\n",
       "      <td>735</td>\n",
       "      <td>68</td>\n",
       "      <td>229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>receita_loja_lag_1</td>\n",
       "      <td>3329</td>\n",
       "      <td>3235</td>\n",
       "      <td>2991</td>\n",
       "      <td>481</td>\n",
       "      <td>1027</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           feature  fold_1  fold_2  fold_3  fold_4  fold_5\n",
       "0                                    cod_municipio    1739    1792    1487     174     455\n",
       "1                                              mes    4211    4799    4082     881    1634\n",
       "2                                       qtde_media    1189    1073     741      83     178\n",
       "3                                        qtde_soma    1134    1070     693     145     308\n",
       "4                                         qtde_max    1154    1062     746      71     183\n",
       "5                                         qtde_min    1982    1788    1386     167     457\n",
       "6                                       feature_04    1264    1097     871     142     323\n",
       "7                                       feature_08     957     805     668      39     152\n",
       "8                                       feature_09    1294    1331    1052      79     242\n",
       "9                                       feature_14     969     946     724      63     104\n",
       "10                                      feature_18    1044    1173     888      38     177\n",
       "11                             feature_04_vezes_06    1213    1328    1068     207     348\n",
       "12                     receita_target_lag_12_meses    1584    1396    1149     164     376\n",
       "13                        receita_loja_std_3_meses    2391    2233    1838     249     535\n",
       "14              receita_target_media_movel_3_meses    5127    5386    4645     675    1603\n",
       "15                       receita_total_loja_anomes    3133    3296    2540     348     961\n",
       "16                receita_loja_media_movel_3_meses    2140    2369    2062     133     431\n",
       "17  soma_quantidade_items_loja_media_movel_3_meses    1204    1222     735      68     229\n",
       "18                              receita_loja_lag_1    3329    3235    2991     481    1027"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_importances"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}