<jupyter_start><jupyter_text>O Desafio <img alt="Colaboratory logo" height="99px" 
src='https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTpw-4GbEpC5PiordgozbjH7ILPI-X6NU_d7Q&usqp=CAU'
align="left" hspace="10px" vspace="0px" >
Prever Vendas Futuras 
Projeto final para o curso Coursera "Como ganhar um concurso de ciência de dados"


Kaggle
  Descrição
Este desafio serve como projeto final para o curso Coursera "Como ganhar um concurso de ciência de dados" .

Nesta competição, você trabalhará com um conjunto de dados de série temporal desafiador que consiste em dados diários de vendas, gentilmente cedidos por uma das **maiores empresas de software da russa** - a **1C Company** . 

Pedimos que você preveja as vendas totais de cada produto e loja no próximo mês. Ao resolver esta competição, você poderá aplicar e aprimorar suas habilidades em ciência de dados. 
<img alt="Colaboratory logo" height="49px" 
src='https://static.1c.ru/images/logo.png'
align="left" hspace="10px" vspace="0px" >
 1C COMPANY

Fundada em 1991, a 1C Company é especializada em desenvolvimento, distribuição, publicação e suporte de software para o mercado de massa. 
 site daa empresa  Dados Você recebe dados históricos de vendas diários. A tarefa é prever a quantidade total de produtos vendidos em cada loja para o conjunto de teste. Observe que a lista de lojas e produtos muda ligeiramente a cada mês. Criar um modelo robusto que possa lidar com tais situações é parte do desafio.

**Descrições de arquivo**
> * **sales_train.csv** - o conjunto de treinamento. Dados históricos diários de janeiro de 2013 a outubro de 2015.
> * **test.csv** - o conjunto de teste. Você precisa prever as vendas dessas lojas e produtos para novembro de 2015.
> * **sample_submission.csv** - um arquivo de envio de amostra no formato correto.
> * **items.csv** - informações complementares sobre os itens / produtos.
> * **item_categories.csv**  - informações complementares sobre as categorias de itens.
> * **shops.csv** - informações complementares sobre as lojas.

**Campos de dados**
> * **ID  - um Id que representa uma tupla (Loja, Item) dentro do conjunto de teste
> * **shop_id** - identificador único de uma loja
> * **item_id** - identificador único de um produto
> * **item_category_id** - identificador único da categoria do item
> * **item_cnt_day** - número de produtos vendidos. **Você está prevendo um valor mensal desta medida**  💥
> * **item_price** - preço atual de um item
> * **data**  - data no formato dd / mm / aaaa
> * **date_block_num** - um número de mês consecutivo, usado por conveniência. Janeiro de 2013 é 0, fevereiro de 2013 é 1, ..., outubro de 2015 é 33
> * **item_name**  - nome do item
> * **shop_name** - nome da loja
> * **item_category_name** - nome da categoria do item  Regras >* **Limites de envio**: No máximo 5 inscrições por dia.

[Submissions](https://www.kaggle.com/c/competitive-data-science-predict-future-sales/submissions)  Analises  Setup  (fazer upload do kaggle.json aqui)<jupyter_code>from google.colab import files
from google.colab import output
import warnings
warnings.filterwarnings('ignore')
run_setup = True
if run_setup:
  files.upload()
  !pip install -q kaggle
  !mkdir -p ~/.kaggle
  !cp kaggle.json ~/.kaggle/
  !ls ~/.kaggle
  !chmod 600 /root/.kaggle/kaggle.json
  !kaggle competitions download -c competitive-data-science-predict-future-sales
output.clear()

def transpose(d,l=100):
  x = pd.DataFrame(d)
  return x.head(l).transpose()
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import gc
if run_setup:
  !ls -l *.zip
  !unzip items.csv.zip
  !unzip sales_train.csv.zip
  !unzip test.csv.zip
  !unzip sample_submission.csv.zip
  output.clear()
!ls -l *.csv
!rm *.zip
item_categories =  pd.read_csv('./item_categories.csv')
items =  pd.read_csv('./items.csv')
sales_train =  pd.read_csv('./sales_train.csv')
submission =  pd.read_csv('./sample_submission.csv')
shops =  pd.read_csv('./shops.csv')
test =  pd.read_csv('./test.csv')<jupyter_output><empty_output><jupyter_text>Traduzir de RU > PT<jupyter_code>!pip install -U textblob
from textblob import TextBlob
for i in range(len(shops)):
  text = shops.loc[i,"shop_name"]
  hi_blob = TextBlob(text)
  text_pt = hi_blob.translate(to='pt',from_lang='ru')
  shops.loc[i,"shop_name"] =str(text_pt)
output.clear()
#item_categories =  pd.read_csv('./items.csv')
for i in range(len(item_categories)):
  text = item_categories.loc[i,"item_category_name"]
  hi_blob = TextBlob(u''+text)
  text_pt = hi_blob.translate(to='pt')
  item_categories.loc[i,"item_category_name"] =str(text_pt)
output.clear()
d = items.drop_duplicates()
print("duplicate rows removed\t",len(items)-len(d))<jupyter_output><empty_output><jupyter_text>Overview > **shops**<jupyter_code>shops.describe().transpose()
shops.info()
import urllib.request
import codecs
from bs4 import BeautifulSoup
wiki = 'https://pt.wikipedia.org/wiki/Lista_de_cidades_da_R%C3%BAssia_por_popula%C3%A7%C3%A3o'
page = urllib.request.urlopen(wiki)
soup = BeautifulSoup(page, 'html5lib')
table = soup.find('table', attrs={'class': 'wikitable'})
cities = []
tds = [row for row in table.findAll('tr')]
for td in tds:
  if len(td.findAll("td"))==4:
    cities.append([td.findAll("td")[1].find("a").text.strip().upper(),
                 td.findAll("td")[2].find("a").text.strip().upper(),
                 td.findAll("td")[3].find("i").text.replace('\xa0','').strip()])
#print(rows)

import re
from unicodedata import normalize
def fn_central(n):
  n if n.find('"')==-1 else n[0:n.find('"')].strip()
  n = re.sub('[^A-Za-z]+', ' ', n).strip()
  n = n.upper()
  if n.find('CENTRO') >=0  or n.find('CENTRAL')>=0:
    return 1
  else:
    return 0
shops["shop_comercial_central"] = 0
shops["shop_comercial_central"] = shops.shop_name.transform(lambda n:fn_central(n))
pd.DataFrame(shops["shop_comercial_central"].value_counts()).head(100)
shops.head(1)

!pip install fuzzywuzzy
!pip install python-Levenshtein
output.clear()
from fuzzywuzzy import fuzz 
from fuzzywuzzy import process 

def fn_city(n):
  n if n.find('"')==-1 else n[0:n.find('"')].strip()
  n = re.sub('[^A-Za-z]+', ' ', n).strip().upper()
  n = n.replace("CENTRO","")
  n = n.replace("CENTRAL","")
  n = n.replace("COMERCIAL","")
  n = n.replace("COMERCIO","")
  n = n.replace("CENTRAL","")
  n = n.replace("N ","")
  n = n.replace("DE ","")
  n = n.replace("MEGA ","")
  n = n.replace("COMPLEXO E ","")
  if n.find("SPB")>=0:
    n = "Distrito Federal do Noroeste"
  else:
    if n.find("UFA")>=0:
      n = "Bascortostão"
    else:
      if n.find("MOSC")>=0:
        n = "distrito federal central"
      else:
        n = n.split(' ')[0]
        c = [m[1] for m in cities]
        n, s= process.extractBests(n, c)[0]  
  return n
shops["shop_shortname"] = shops.shop_name.transform(lambda n:fn_city(n))

shops["shop_city"] = ""
shops["shop_district"]=""
shops["shop_population"]=0

query = list(shops.shop_shortname.value_counts().index)
output.clear()
for q in query:
  print(q.lower())
  r, i = process.extractBests(q, cities)[0]
  shops.loc[(shops.shop_shortname==q),"shop_city"] = r[0]
  shops.loc[(shops.shop_shortname==q),"shop_district"]= r[1]
  shops.loc[(shops.shop_shortname==q),"shop_population"]= int(r[2])
(shops.shop_city.sort_values())
shops["category"] = shops.shop_name.str.split(" ").map( lambda x: x[1].replace("(","").replace(",","").replace(".","").replace('\"','').upper()[0:1] )
cats = list(shops.category.value_counts().index)
shops.loc[(shops.category.isin(cats[3:])),"category"]='O'
transpose(shops.category.value_counts())
cols = [ 'shop_id', 'shop_comercial_central', 'shop_city', 'shop_district',  'category']
shops = shops[cols]
shops.shop_comercial_central = shops.shop_comercial_central.astype('category')
shops.shop_comercial_central = shops.shop_comercial_central.cat.codes
shops.shop_district = shops.shop_district.astype('category')
shops.shop_district = shops.shop_district.cat.codes
shops.category = shops.category.astype('category')
shops.category = shops.category.cat.codes
shops.shop_city = shops.shop_city.astype('category')
shops.shop_city = shops.shop_city.cat.codes
shops.head()<jupyter_output><empty_output><jupyter_text>**item_categories**<jupyter_code>item_categories.head(1)
item_categories.describe().transpose()
item_categories["item_category_type"] =  item_categories.item_category_name+'- '
item_categories["item_category_type"] = item_categories.item_category_type.transform(lambda c: c.split("-")[0].strip().upper() if c.find(" ")==-1 else c.split(" ")[0].strip().upper()  )
item_categories["item_category_type"] = item_categories.item_category_type.transform(lambda c: c if c.find("-")==-1 else c.split("-")[0].strip().upper()  )
categories = (item_categories["item_category_type"].value_counts().index)
item_categories.loc[(item_categories.item_category_type.isin(categories[8:])),"item_category_type"]='OUTROS'
transpose(item_categories.item_category_type.value_counts())
item_categories = item_categories[['item_category_id','item_category_type']]
item_categories.item_category_type = item_categories.item_category_type.astype('category')
item_categories.item_category_type = item_categories.item_category_type.cat.codes
item_categories.head(1)<jupyter_output><empty_output><jupyter_text>**items**<jupyter_code>items.head(1)
items.describe().transpose()<jupyter_output><empty_output><jupyter_text>**items_categories**<jupyter_code>items_categories = items.merge(item_categories,how='left',left_on='item_category_id', right_on='item_category_id')
items_categories.describe().transpose()
items_categories = items_categories[["item_id","item_category_id","item_category_type"]]
items_categories.reset_index(drop=True, inplace=True)
items_categories.head(1)<jupyter_output><empty_output><jupyter_text>**sales_train**<jupyter_code>sales_train.head(1)
sales_train.describe().transpose()<jupyter_output><empty_output><jupyter_text>**sales**<jupyter_code>sales = sales_train.copy()
sales = items.merge(sales,how='right',left_on='item_id', right_on='item_id')
len(sales),len(sales_train),len(items)
sales.head(1)
sales = items_categories.merge(sales,how='right',on=["item_id","item_category_id"])
len(sales),len(sales_train),len(items_categories)
#sales.drop(columns=['item_name_x','item_name_y'],inplace=True)
sales.reset_index(drop=True,inplace=True)
sales.head(1)
import time
ts = time.time()
sales.date  = pd.to_datetime(sales.date,errors='coerce')
years = (sales.date.dt.year.value_counts().index)
months= (sales.date.dt.month.value_counts().index)
years
sequence = 0
for i,y in enumerate(years.sort_values()):
  for m in months.sort_values():
    if y==2013 and m ==1:
      sales.loc[(sales.date.dt.year==y)&(sales.date.dt.month==m),"date_block_num"]=0
    else:
      sequence+=1
      sales.loc[(sales.date.dt.year==y)&(sales.date.dt.month==m),"date_block_num"]=sequence
gc.collect()
time.time() - ts<jupyter_output><empty_output><jupyter_text>Pivot Table<jupyter_code>sales.pivot_table(index=["item_cnt_day"])
sales.pivot_table(index=["shop_id"],columns=["date_block_num"], aggfunc=["mean"])
pd.pivot_table(sales,index=["date_block_num","shop_id","item_category_id"],values=["item_cnt_day"],aggfunc=["sum","mean"])
pd.pivot_table(sales,index=["date_block_num","shop_id","item_category_id","item_id"],values=["item_cnt_day"],aggfunc=["sum","mean"])
pd.pivot_table(sales,index=["shop_id"],values=["item_price"],aggfunc=['mean','max','min',len])
#sales = sales[sales.item_price >=1]
pd.pivot_table(sales,index=["shop_id"],values=["item_cnt_day"],aggfunc=['mean','max','min','count'])
sales_pivot = sales.pivot_table(
    index=[ 'shop_id','item_id'],
    values=['item_cnt_day'],
    columns=['date_block_num'],
    fill_value=0,
    aggfunc='mean'
).reset_index()

sales_pivot
sales_pivot.head(1)
sales_pivot.describe()
data = test.copy()
data = data.merge(sales_pivot, how='left', on=['shop_id', 'item_id']).fillna(0).drop(
    ['ID', 'shop_id', 'item_id'], axis=1)
data
data
len(data.values[:,:-2]),len(data.values[:, -2:-1]),len(data.values[:,1:-1])
xtrain, ytrain = data.values[:,:-2], data.values[:, -2:-1].ravel()
xvalid, yvalid = data.values[:,1:-1], data.values[:, -1:].ravel()

xtest = data.values[:, 2:]<jupyter_output><empty_output><jupyter_text>Models<jupyter_code>import tensorflow as tf
from tensorflow import keras
from tensorflow.keras.callbacks import ReduceLROnPlateau,LearningRateScheduler
import os
import math
from sklearn.preprocessing import StandardScaler
from sklearn.model_selection import train_test_split
keras.backend.clear_session()

xtrain = np.reshape(xtrain, (xtrain.shape[0],xtrain.shape[1], 1))
xvalid = np.reshape(xvalid, (xvalid.shape[0],xvalid.shape[1], 1))


def learning_rate_scheduler(self,epoch=0):
  lr = 0.9
  print(' Learning rate: ', lr,' - epoch: ',epoch)
  return lr
batch_size = 512
unit = 5#int(xtrain.shape[1])/3)
model = keras.Sequential()
model.add(keras.layers.LSTM(unit, return_sequences=True, input_shape=(xtrain.shape[1], 1)))
model.add(keras.layers.Dropout(0.1))
model.add(keras.layers.LSTM(unit, return_sequences=True))
model.add(keras.layers.Dropout(0.1))
model.add(keras.layers.LSTM(unit))
model.add(keras.layers.Dense(1))
train_size = xtrain.shape[0]
model.compile(optimizer='RMSprop', loss='mse', metrics=[tf.keras.metrics.RootMeanSquaredError(name='root_mean_squared_error', dtype=None)])

lr_scheduler = LearningRateScheduler(learning_rate_scheduler)
lr_reducer = ReduceLROnPlateau(factor=np.sqrt(0.1),
                            cooldown=1,
                            patience=2,
                            min_lr=0.5e-2)
callbacks = [ lr_scheduler, lr_reducer]

gc.collect()

steps_per_epoch =  math.ceil(train_size / batch_size )
history = model.fit(xtrain, ytrain,  
                            epochs=1, verbose=1, 
                            steps_per_epoch=steps_per_epoch, 
                            batch_size=batch_size, 
                            validation_data=(xvalid, yvalid),
                            callbacks=callbacks)
name="IGTI"
epochs = list(range(1,(np.array(history.history['root_mean_squared_error']).shape[0])+1))
plt.clf()
fig = plt.figure(figsize=(15, 2))
ax = fig.add_subplot(121)
ax2 = fig.add_subplot(122)

ax.set_title(name+' - RMSE')
ax.plot(epochs,history.history['root_mean_squared_error'], "r.-")
ax.plot(epochs,history.history['val_root_mean_squared_error'], "c.-")
ax.legend(('RMSE','VAL RMSE'),fontsize=15)
ax.set_xlabel("Epochs")
ax.grid(True)

ax2.set_title(name+' - LOSS')
ax2.plot(epochs,history.history['loss'], "r.-")
ax2.plot(epochs,history.history['val_loss'], "c.-")
ax2.legend(('LOSS','VAL LOSS'),fontsize=15)
ax2.set_xlabel("Epochs")
ax2.grid(True)
plt.show()

from sklearn.metrics import mean_squared_error
ypred = model.predict(xvalid)
print('MSE =', mean_squared_error(yvalid, ypred, squared=False))
print('RMSE =', mean_squared_error(yvalid, ypred, squared=True))
x = np.reshape(xtest, (xtest.shape[0],xtest.shape[1], 1))
preds = model.predict(x)
!rm igti.csv
ids = pd.read_csv('./test.csv')
submission = pd.DataFrame({
    "ID": ids.index, 
    "item_cnt_month": preds.reshape(-1)
})
submission.head()
submission.to_csv('igti.csv', index=False)
!kaggle competitions submit -c competitive-data-science-predict-future-sales -f igti.csv -m "<NAME> "<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16155774050510893, 'fraction_numerical': 0.012659854765117924, 'mean_word_length': 5.0552529182879375, 'pattern_counts': {'":': 2, '<': 44, '<?xml version=': 0, '>': 70, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '135271', 'license': 'Apache-2.0', 'n_tokens_mistral': 5927, 'n_tokens_neox': 5826, 'n_words': 1033, 'path': 'IGTI_Prever_Vendas_Futuras_(Pandas_Pivot_Table)_[Francke].ipynb', 'repo_name': 'franckepeixoto/igti-ml', 'size': '15575'}