<jupyter_start><jupyter_text>MARATONA BEHIND THE CODE 2020 DESAFIO 7 - TNT   Installing Libs<jupyter_code>!pip install scikit-learn --upgrade
!pip install xgboost --upgrade
!pip install imblearn --upgrade
!pip install cloudant
from cloudant import Cloudant
u = '<username>'
p = '<<PASSWORD>>'
a = '<username>'
client = Cloudant(u, p, account = a, connect=True, auto_renew=True)
db = client['<your db>']
response = db.all_docs(limit=15000, include_docs = True)

docs = []

for r in response['rows']:
    docs.append(r['doc']['payload'])
type(docs)<jupyter_output><empty_output><jupyter_text>Download dos conjuntos de dados em formato .csv<jupyter_code>import pandas as pd
df_training_dataset = pd.DataFrame(data=docs)
df_training_dataset.tail()<jupyter_output><empty_output><jupyter_text>Atenção!A coluna-alvo neste desafio é a coluna ``TARGET``<jupyter_code>#IMPORTS

import six
import sys
sys.modules['sklearn.externals.six'] = six
from sklearn.impute import SimpleImputer
import numpy as np
from sklearn.preprocessing import StandardScaler
from sklearn.model_selection import train_test_split
from imblearn.over_sampling import SMOTE
from sklearn.model_selection import GridSearchCV
from sklearn.multiclass import OneVsRestClassifier
from xgboost import XGBClassifier
from sklearn.pipeline import Pipeline
from sklearn.metrics import accuracy_score
from sklearn.metrics import classification_report
from sklearn import metrics
from IPython.display import Image  
from imblearn.under_sampling import RandomUnderSampler
from imblearn.over_sampling import RandomOverSampler
from sklearn.model_selection import train_test_split
from sklearn.model_selection import GridSearchCV
from sklearn.model_selection import StratifiedKFold<jupyter_output><empty_output><jupyter_text>Fazendo as transformações e Classifier<jupyter_code>id_var_name = 'id'
target_name = 'TARGET'
def is_imbalanced(df, target_name, tolerance):
    """
    

    Parameters
    ----------
    df : pandas.Dataframe
        df.
    target_name : string
        target_name.
    tolerance : int
        Tolerance percentage.

    Returns
    -------
    boolean
        True if is imbalanced, else False.

    """
    # Print the unique Churn values
    print(set(df[target_name]))
    # Calculate the ratio size of each churn group
    ratio_size = df.groupby([target_name]).size() / df.shape[0] * 100
    ratio_size_min = ratio_size[ratio_size.idxmin()]
    print(ratio_size)
    print(ratio_size_min)
    return (ratio_size_min <= tolerance)


imbalanced = is_imbalanced(df_training_dataset, target_name = target_name, tolerance = 30)
def split_cat_num_vars(df, id_var_name, target_name, prepare_for_ml=True, get_num=True, bf_proc_cols = []):
    if bf_proc_cols:
        df_bf_proc = df[bf_proc_cols]
        dict_rename_cols = {}
        for col in bf_proc_cols:
            dict_rename_cols[col] = col + "_bf_proc"
        df_bf_proc = df_bf_proc.rename(columns=dict_rename_cols)

    custid = [id_var_name]
    target = [target_name]
    categorical = df.nunique()[df.nunique() < 10].keys().to_list()
    if target[0] in categorical:
        categorical.remove(target[0])

    numerical = [col for col in df.columns
                 if col not in categorical + custid + target]
    
    if not prepare_for_ml:
        if get_num:
            return df[numerical]
        else:
            return df[categorical]
    else:
    
        ## One-hot encode categorical features
        # drop_first : we remove the first encoded column, as it is redundant and can be infered from the others
        df = pd.get_dummies(data = df, columns = categorical, drop_first = True)
            
        ## Scale the numerical features
        # Some ML models requires this step, because otherwise variables with larger mean and standard deviation would
        # have more influence as predictors
        
        scaler = StandardScaler()
        
        # Note: TotalCharges type = Object
        # You can use the to_numeric method in order to convert the values under the Price column into a float:
        # By setting errors=’coerce’, you’ll transform the non-numeric values into NaN.
        
        for col in df[numerical].loc[:,df[numerical].dtypes == 'object']:
            df[col] = pd.to_numeric(df[col], errors='coerce')
            
        scaled_numerical = scaler.fit_transform(df[numerical])
        # Remove Nan and infinite values
        scaled_numerical = np.nan_to_num(scaled_numerical)
        
        # Build a DataFrame from scaled_numerical
        scaled_numerical = pd.DataFrame(scaled_numerical, columns=numerical)
        # scaled_numerical.fillna(scaled_numerical.mean())
        
        ## Bringing all together:
        
        # Drop non-scaled numerical columns
        df = df.drop(columns=numerical, axis=1)
        
        # Merge the non-numerical with the scaled numerical data
        df_final = df.merge(right = scaled_numerical,
                                how = 'left',
                                left_index=True,
                                right_index = True)
        if bf_proc_cols:
            df_final = df_final.merge(right = df_bf_proc,
                                    how = 'left',
                                    left_index=True,
                                    right_index = True)

    return(df_final)
df_training_dataset_rmcolumns = df_training_dataset.drop(columns=['Tempo', 'Estação', 'LAT', 'LONG', 'Movimentação', 'row'], inplace=False)
train_df_prepared = split_cat_num_vars(df_training_dataset_rmcolumns, id_var_name, target_name, prepare_for_ml=True, get_num=True, bf_proc_cols = [])
#### Split the data
def split_train_test(df, target_name, id_var_name, test_size):

    # Split X and Y into training and testing datasets
    train, test = train_test_split(df, test_size = test_size, random_state=49)
    
    # Store column names from `telcom` excluding target variable and customer ID
    cols = [col for col in df.columns if col not in id_var_name + target_name]
    
    # Extract training features
    train_X = train[cols]
    
    # Extract training target
    train_Y = train[target_name]
    
    # Extract testing features
    test_X = test[cols]
    
    # Extract testing target
    test_Y = test[target_name]
    return(train_X, train_Y, test_X, test_Y)
train_X, train_Y, test_X, test_Y = split_train_test(train_df_prepared, target_name=target_name, id_var_name=id_var_name, test_size=0.1)
train_X_res, train_Y_res= SMOTE(random_state=49, sampling_strategy=0.6).fit_resample(train_X, train_Y)
si = SimpleImputer(
    missing_values=np.nan,  # os valores faltantes são do tipo ``np.nan`` (padrão Pandas)
    strategy='most_frequent'
)
from sklearn.model_selection import StratifiedKFold

grid = Pipeline(steps=[
    ('imputer', si),
    #('Rdf', RandomForestClassifier(n_jobs=-1, max_depth=20, random_state=0))
    
    ('Rdf', OneVsRestClassifier(XGBClassifier(n_jobs=-1, max_depth=8, learning_rate=0.24, gamma=0.41, colsample_bytree=0.89)))
])

grid.fit(train_X_res, train_Y_res.values.ravel())

y_pred = grid.predict(test_X)

# Compute metrics
print(classification_report(test_Y, y_pred))   
print(accuracy_score(test_Y, y_pred))<jupyter_output>precision    recall  f1-score   support

      NORMAL       0.92      0.87      0.90       815
 REABASTECER       0.76      0.84      0.80       390

    accuracy                           0.86      1205
   macro avg       0.84      0.86      0.85      1205
weighted avg       0.87      0.86      0.86      1205

0.862240663900415<jupyter_text>Analisando a qualidade do modelo através da matriz de confusão   Scoring dos dados necessários para entregar a solução Como entrega da sua solução, esperamos os resultados classificados no seguinte dataset chamado "to_be_scored.csv":  Download da "folha de respostas"<jupyter_code>!wget --no-check-certificate --content-disposition https://gitlab.com/JoaoPedroPP/datasets/-/raw/master/ntn/to_be_scored.csv
df_to_be_scored = pd.read_csv(r'to_be_scored.csv')<jupyter_output>--2020-09-10 21:01:49--  https://gitlab.com/JoaoPedroPP/datasets/-/raw/master/ntn/to_be_scored.csv
Resolving gitlab.com (gitlab.com)... 192.168.127.12, fd00:c2b6:b24b:be67:2827:688d:e6a1:6a3b
Connecting to gitlab.com (gitlab.com)|192.168.127.12|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/plain]
Saving to: ‘to_be_scored.csv.2’

    [ <=>                                   ] 70,291      --.-K/s   in 0.001s  

2020-09-10 21:01:49 (51.6 MB/s) - ‘to_be_scored.csv.2’ saved [70291]<jupyter_text>Atenção! Para poder aplicar seu modelo e classificar a folha de respostas, você precisa primeiro aplicar as mesmas transformações com colunas que você aplicou no dataset de treino. Não remova ou adicione linhas na folha de respostas.  Não altere a ordem das linhas na folha de respostas. Ao final, as 1000 entradas devem estar classificadas, com os valores previstos em uma coluna chamada "target" Na célula abaixo, repetimos rapidamente os mesmos passos de pré-processamento usados no exemplo dado com árvore de decisão<jupyter_code>df_to_be_scored_rmcolumns = df_to_be_scored.drop(columns=['Tempo', 'Estação', 'LAT', 'LONG', 'Movimentação'], inplace=False)

df_to_be_scored_prepared = split_cat_num_vars(df_to_be_scored_rmcolumns, id_var_name, target_name, prepare_for_ml=True, get_num=True, bf_proc_cols = [])<jupyter_output><empty_output><jupyter_text>Pode ser verificado abaixo que as colunas da folha de resposta agora são idênticas às que foram usadas para treinar o modelo:  AtençãoPara todas colunas que não existirem no "df_to_be_scored", você pode usar a técnica abaixo para adicioná-las:<jupyter_code>y_pred = grid.predict(df_to_be_scored_prepared)
df_to_be_scored["TARGET"] = y_pred<jupyter_output><empty_output><jupyter_text>Salvando a folha de respostas como um arquivo .csv para ser submetido<jupyter_code>project.save_data(file_name="results.csv", data=df_to_be_scored.to_csv(index=False))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08872901678657075, 'fraction_numerical': 0.02158273381294964, 'mean_word_length': 3.494387067804221, 'pattern_counts': {'":': 1, '<': 39, '<?xml version=': 0, '>': 37, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '185475', 'license': 'Unlicense', 'n_tokens_mistral': 3506, 'n_tokens_neox': 3298, 'n_words': 815, 'path': 'Desafio 7/notebook.ipynb', 'repo_name': 'manbbo/maratona-behind-the-code', 'size': '9995'}