# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/brunotagoncalves/MeLi_2021_demand_prediction/blob/main/meli_EDA_3rd_sub.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + [markdown] id="-ZbsoPU-AJ4N"
# # Read Data 

# + colab={"base_uri": "https://localhost:8080/"} id="37hIzI0UANWB" outputId="ebde8135-8e36-477a-f733-1f9e0d249f17"
# !wget https://meli-data-challenge.s3.amazonaws.com/2021/test_data.csv
# !wget https://meli-data-challenge.s3.amazonaws.com/2021/train_data.parquet
# !wget https://meli-data-challenge.s3.amazonaws.com/2021/items_static_metadata_full.jl
# !wget https://meli-data-challenge.s3.amazonaws.com/2021/sample_submission.csv.gz

# + colab={"base_uri": "https://localhost:8080/"} id="GL6kFn3IcDH1" outputId="4f00bf58-9efe-49aa-c2e5-48b08cde8380"
# !apt-get install openjdk-8-jdk-headless -qq > /dev/null
# #!wget http://www-eu.apache.org/dist/spark/spark-2.4.8/spark-2.4.8-bin-hadoop2.7.tgz 
# !wget -P spark_tgz/ http://www-eu.apache.org/dist/spark/spark-2.4.8/spark-2.4.8-bin-hadoop2.7.tgz

# + colab={"base_uri": "https://localhost:8080/"} id="MXldW4KjcF8h" outputId="a18cd1f5-c638-4d58-8ead-18d70a228515"
# !tar -xf spark_tgz/spark-2.4.8-bin-hadoop2.7.tgz -C spark_tgz/
# !pip install -q findspark
# !pip install -q pyspark

# + id="DwlQZAD5cJrK"
import os
os.environ["JAVA_HOME"] = "/usr/lib/jvm/java-8-openjdk-amd64"
os.environ["SPARK_HOME"] = "/content/spark_tgz/spark-2.4.8-bin-hadoop2.7"

# + id="XjwlnWfvcNyT"
import findspark
findspark.init()
from pyspark.sql import SparkSession
spark = SparkSession.builder.master("local[*]").getOrCreate()

# + id="NcLjJFcUAVdR"
import pandas as pd 
import numpy as np


# + id="4nuN_PXPcPcT"
df_spark = spark.read.format('parquet').load("train_data.parquet")

# + colab={"base_uri": "https://localhost:8080/"} id="f3Kkho_vcdt-" outputId="7624d968-4db9-41d7-afee-548ec779bcf6"
df_spark.count()

# + id="15mQ-Xwwchv7"
df_spark.createOrReplaceTempView('df')

# + id="6ff7dxebga47"
spark.sql("""
SELECT df.*
  ,minutes_active - lag(minutes_active,1) over (partition by sku order by date asc) diff_minutes_active
  ,(CASE WHEN minutes_active - lag(minutes_active,1) over (partition by sku order by date asc) < 0 THEN 'Ruptura'END) Ruptura
  ,(CASE WHEN minutes_active - lag(minutes_active,1) over (partition by sku order by date asc) > 0 THEN 'Estoque'END) Estoque
  
FROM df
--where sku = 645793
--order by sku, date
""").createOrReplaceTempView('df_re')

# + id="nHyIniUBglfx"
spark.sql("""
SELECT df_re.*
  ,CASE WHEN Ruptura is null and Estoque is null and minutes_active>0 and diff_minutes_active is null then 'Estoque'
        --WHEN Estoque is not null then 'Estoque'
        WHEN minutes_active = diff_minutes_active	and Estoque is not null then 'Estoque'
        --WHEN Ruptura is not null and Estoque is null then 'Ruptura'
        WHEN Ruptura is not null and Estoque is null and minutes_active=0  then 'Ruptura'
        END STATUS_RUPTURA
FROM df_re
--where sku = 645793
--order by sku, date
""").createOrReplaceTempView('df_re')#.toPandas()

# + id="J7jz7MUM9QCy"
spark.sql("""
SELECT df_re.*
  ,COUNT(CASE WHEN STATUS_RUPTURA IS NOT NULL THEN 1 END) over (partition by sku order by date ) ciclo_ruptura
FROM df_re
--where sku = 645793
--order by sku, date
""").createOrReplaceTempView('df_re')#.toPandas()

# + id="fS4JxQefo97e"
spark.sql("""
SELECT df_re.*
  ,SUM(sold_quantity) over (partition by sku,ciclo_ruptura order by date desc) sum_sold_quantity 
  ,sum(case when sold_quantity >0 then current_price*sold_quantity end) over (partition by sku,ciclo_ruptura order by date desc)/SUM(sold_quantity) over (partition by sku,ciclo_ruptura order by date desc) avg_current_price 
  ,SUM(minutes_active) over (partition by sku,ciclo_ruptura order by date desc) days_active_nround
  ,round(SUM(minutes_active) over (partition by sku,ciclo_ruptura order by date desc) /60/24 + 0.5,0) days_active 
FROM df_re
--where sku = 645793
--order by sku, date
""").createOrReplaceTempView('df_re')#.toPandas()

# + id="Bro3AOIrkDQc"
spark.sql("""
SELECT df_re.*  
  ,min(days_active_nround) over (partition by sku,ciclo_ruptura order by date desc)>=1 FLG_ruptura 
FROM df_re
--where sku = 0
--order by sku, date
""").filter('FLG_ruptura=True').drop(*['date','sold_quantity','minutes_active','diff_minutes_active',	'Ruptura',	'Estoque',	'STATUS_RUPTURA',	'ciclo_ruptura']).createOrReplaceTempView('df_final')#.toPandas()

# + id="OOVZxzIfr9D6" colab={"base_uri": "https://localhost:8080/"} outputId="d27935e2-5514-4505-dce7-ebf39afb83a8"
# %%time
spark.sql("""
SELECT *
FROM df_final
""").write.mode('overwrite').format('parquet').save('data/tabelao/')

# + id="lWELtLngxNJJ"
df = spark.read.format('parquet').load('data/tabelao/')

# + colab={"base_uri": "https://localhost:8080/"} id="zre2tnJkzomq" outputId="edc5dcde-5040-41fa-e012-e62078a0c13e"
df.count()

# + id="kbejrXlAxuze"
df.repartition(1).write.mode('overwrite').format('parquet').save('data/tabela_final/')

# + id="4pNRaVNnCNuP"
from glob import glob 
loc_parquet = glob('data/tabela_final/*.parquet')
df = pd.read_parquet(loc_parquet[0])
#pd_df = df.toPandas()

# + colab={"base_uri": "https://localhost:8080/", "height": 206} id="q7At4HCRCWIW" outputId="a965380e-1857-447b-80a9-494961f14449"
df.head()

# + colab={"base_uri": "https://localhost:8080/"} id="T6awArEvGMdY" outputId="39e9d46f-b407-4374-f0eb-d7755dcbbbf1"
df.shape

# + colab={"base_uri": "https://localhost:8080/", "height": 1000} id="EX0D4oSxYi0m" outputId="bd1e9ed6-8b8d-41a9-9911-51a9311dec0c"
df.loc[df.sku==264,:]

# + id="rzeqUexTU7FS"
df_test = pd.read_csv('test_data.csv')

# + colab={"base_uri": "https://localhost:8080/"} id="XGsVMA6wzcyK" outputId="40339fb5-c5bf-47ec-f02c-029b6cf88cde"
# !pip install catboost

# + id="-b0XxqtOzUT_"
from catboost import Pool, CatBoostClassifier

# + id="PczvuvFFVBiw"
vars = ['sku',
 'sum_sold_quantity'
 ]
cat_vars =  ['sku'
 ]

# + id="x-sLOQ1TYpC-"
from sklearn.model_selection import train_test_split

# + id="DRR9yMtG6qBx"
train_data,test_data,train_label,test_label = train_test_split(df,df.days_active,train_size=.02,test_size=.01)

# + colab={"base_uri": "https://localhost:8080/"} id="EoM4ZSXgD569" outputId="6b3b4eda-57b0-4d5f-8c12-5446bbb0d5cd"
# initialize data
#train_data = g_df.loc[:,vars].sample(frac=.01,random_state=123)
#train_label = g_df.loc[:,'days_active'].apply(lambda x : x if x<30. else 30.).round().astype(int).sample(frac=.01,random_state=123)
#test_data = g_df.loc[~g_df.sku.isnull(),'current_price_count':'shipping_payment_shipping_payment']#.sample(frac=.1,random_state=1234)
# initialize Pool
train_pool = Pool(train_data.loc[:,vars], 
                  train_label, 
                  cat_features=cat_vars)
test_pool = Pool(test_data.loc[:,vars], 
                  test_label, 
                  cat_features=cat_vars)

# specify the training parameters 
model = CatBoostClassifier(task_type='GPU')#n_estimators=100,learning_rate=0.1)
#train the model
model.fit(train_pool)
# make the prediction using the resulting model

# + id="ltOIjuO-EJ6c" outputId="26f451db-67f1-4bfd-e79b-f627d8fc4900" colab={"base_uri": "https://localhost:8080/"}
model.score(test_pool),model.score(train_pool)


# + id="iXmZqPnaHqEU"

def rps(probs, outcome):
    cum_probs = np.cumsum(probs)
    cum_outcomes = np.cumsum(outcome)
    
    print(cum_outcomes)
    print(cum_probs)
    sum_rps = 0
    for i in range(len(outcome)):         
        sum_rps+= (cum_probs[i] - cum_outcomes[i])**2
    
    return sum_rps/(len(outcome)-1)


# + id="XgbeOOMEeFe5"
df_test.loc[:,'sum_sold_quantity'] = df_test.target_stock
test_data = df_test.loc[:,vars]
test_pool = Pool(test_data,
                 cat_features=cat_vars) 


# + colab={"base_uri": "https://localhost:8080/", "height": 300} id="WK8KYe1846YC" outputId="e9fa470a-1dc5-4eee-b2ec-3e8df5619df8"
pd.DataFrame(model.predict(test_data)).describe()#.hist()

# + colab={"base_uri": "https://localhost:8080/", "height": 316} id="C44mz6xFF01G" outputId="8b8e9b9d-e0cd-452e-abb2-46f1477e9a41"
pd.DataFrame(model.predict(test_data)).hist()

# + id="XDZsKkBM_DeR" outputId="88c6c9b5-b61a-4c17-bb14-fb3b0f0e26e4" colab={"base_uri": "https://localhost:8080/", "height": 282}
pd.DataFrame(model.predict(test_data)).apply(lambda x : x[0] if x[0]<30 else 30,axis=1).hist()

# + id="wJdV89FQ-spU"
result = pd.DataFrame(model.predict_proba(test_data)).round(4)#.to_csv("test_inner.csv.gz",compression='gzip',index=False,header=False)

# + id="RqxIen69-AuD"
result.loc[:,29] = result.loc[:,29:].sum(axis=1)

# + id="WgrVe4Zy-hDX"
result.loc[:,:29].to_csv("test_inner.csv.gz",compression='gzip',index=False,header=False)

# + id="GTaVIl3p-5o2"


StarcoderdataJupyterScriptsDedupFiltered