<jupyter_start><jupyter_text>Loading Libraries<jupyter_code>import pandas as pd
import numpy as np
import gc
import seaborn as sns
import matplotlib.pyplot as plt
from utils import mygraph,analyze
from datetime import datetime
from scipy.stats import mode
from tqdm import tqdm<jupyter_output><empty_output><jupyter_text>Reading datasets<jupyter_code>y_train = pd.read_csv('../resources/data/y_train.csv')
y_train = analyze.reduce_mem_usage(y_train)
print("rows in table :",y_train.shape[0])
print("key values    :",y_train.key_value.nunique())
sample_submission = pd.read_csv('../resources/data/sample_submission.csv')
sample_submission = analyze.reduce_mem_usage(sample_submission)
print("rows in table :",sample_submission.shape[0])
print("key values    :",sample_submission.key_value.nunique())
rcc_train = pd.read_hdf('../resources/mydata/rcc_train.h5', 'results_table')
print(rcc_train.shape)
rcc_train['codmes']= pd.to_datetime(rcc_train['codmes'],format='%Y-%m-%d')#%d infer_datetime_format=True)
rcc_train = analyze.reduce_mem_usage(rcc_train)
rcc_train.head(3)
rcc_test = pd.read_hdf('../resources/mydata/rcc_test.h5', 'results_table')
print(rcc_test.shape)
rcc_test['codmes']= pd.to_datetime(rcc_test['codmes'],format='%Y-%m-%d')#%d infer_datetime_format=True)
rcc_test = analyze.reduce_mem_usage(rcc_test)
rcc_test.head(3)
productos = pd.read_hdf('../resources/mydata/productos.h5', 'results_table')
productos = analyze.reduce_mem_usage(productos)
productos.columns = ['description','PRODUCTO','producto_class','producto_credito']
productos.head(3)
se_train = pd.read_csv("../resources/data/se_train.csv")
se_train = analyze.reduce_mem_usage(se_train)
se_train.head(3)
se_test = pd.read_csv("../resources/data/se_test.csv")
se_test = analyze.reduce_mem_usage(se_test)
se_test.head(3)
rcc_train_prod_cliente = pd.merge(rcc_train,productos, how='left', on=['PRODUCTO'])
rcc_train_prod_cliente = pd.merge(rcc_train_prod_cliente,se_train, how='left', on=['key_value'])
del rcc_train
del se_train
gc.collect()
rcc_test_prod_cliente = pd.merge(rcc_test,productos, how='left', on=['PRODUCTO'])
rcc_test_prod_cliente = pd.merge(rcc_test_prod_cliente,se_test, how='left', on=['key_value'])
del rcc_test
del se_test
gc.collect()
rcc_train_prod_cliente.head(2)<jupyter_output><empty_output><jupyter_text>rcc_test_prod_cliente.head(2)<jupyter_code>n = len(rcc_train_prod_cliente)
nunique = rcc_train_prod_cliente.nunique()
names_column = nunique.index
bynary_columns = nunique[nunique==2].index
unique_columns = nunique[nunique==1].index
rcc_train_prod_cliente.drop(columns = list(unique_columns),inplace =True)
names_column  = (set(names_column) - set(bynary_columns))-set(unique_columns)
nunique
rcc_test_prod_cliente.drop(columns = list(unique_columns),inplace =True)
nunique[nunique.index.isin(names_column)]
datetime_object = datetime.strptime('2018-02-01', '%Y-%m-%d')
rcc_train_prod_cliente['diff_month']  = ((datetime_object - rcc_train_prod_cliente.codmes).dt.days/30).astype(int)
datetime_object = datetime.strptime('2019-02-01', '%Y-%m-%d')
rcc_test_prod_cliente['diff_month']   = ((datetime_object - rcc_test_prod_cliente.codmes).dt.days/30).astype(int)
rcc_train_prod_cliente.head(2)
rcc_train_prod_cliente_short = rcc_train_prod_cliente.iloc[:100000]
rcc_train_prod_cliente_short.head()
def float_to_float32(df):
    for name in df.columns:
        type_ = str(df[name].dtypes)
        if('float' in type_ or 'int' in type_):
            df[name] = df[name].astype(np.float32)
    return df
rcc_train_prod_cliente             = float_to_float32(rcc_train_prod_cliente)
rcc_train_prod_cliente.key_value   = rcc_train_prod_cliente.key_value.astype(int)
rcc_train_prod_cliente.diff_month  = rcc_train_prod_cliente.diff_month.astype(int)
rcc_test_prod_cliente             = float_to_float32(rcc_test_prod_cliente)
rcc_test_prod_cliente.key_value   = rcc_test_prod_cliente.key_value.astype(int)
rcc_test_prod_cliente.diff_month  = rcc_test_prod_cliente.diff_month.astype(int)
print(list(rcc_test_prod_cliente.columns))
ft={}
mode_agg = lambda x: x.mode().iloc[0]

mode_agg = lambda x: mode(x)[0][0]

lt = ['condicion','saldo','log_saldo']
for x in lt:
    ft[x]=['max','min','mean','sum']    

ft['ctd_hijos'] = ['count','max']
ft['ctd_veh'] = ['max']
'''
ft['ctd_hijos'] = ['count','max',mode_agg]
ft['ctd_veh'] = ['max',mode_agg]
ft['tipo_credito']=[mode_agg]
ft['cod_instit_financiera']=[mode_agg]
ft['PRODUCTO']=[mode_agg]
ft['RIESGO_DIRECTO']=[mode_agg]
ft['COD_CLASIFICACION_DEUDOR']=[mode_agg]
ft['in_range_default']=[mode_agg]
ft['condicion_id']=[mode_agg]
ft['PRODUCTO_id']=[mode_agg]
ft['PRODUCTO_id_notexist']=[mode_agg]
ft['RIESGO_DIRECTO_id']=[mode_agg]
ft['neg_saldo']=[mode_agg]
ft['saldo_1']=[mode_agg]
ft['description']=[mode_agg]
ft['producto_class']=[mode_agg]
ft['producto_credito']=[mode_agg]
ft['tip_lvledu']=[mode_agg]
#'''
list(ft.keys())
#del rcc_train_prod_cliente_agg
#gc.collect()
ids = list(rcc_train_prod_cliente.key_value.unique())<jupyter_output><empty_output><jupyter_text>rcc_train_prod_cliente_agg = rcc_train_prod_cliente[rcc_train_prod_cliente.key_value ==ids[0]]for id_ in tqdm(ids[1:]):    rcc_train_prod_cliente_agg = pd.concat([rcc_train_prod_cliente_agg,rcc_train_prod_cliente[rcc_train_prod_cliente.key_value ==id_]])rcc_train_prod_cliente_agg.head()<jupyter_code>def agg_periods(df, rangos,list_names):
    for rango in tqdm(rangos):
        for columna_ in list_names:
            lista_columnas = []
            for j in range(rango[0],rango[1]+1):
                lista_columnas.append(columna_+'_'+str(j))
            df[columna_+str(rango[0])+'_'+str(rango[1])+'_Mean'] = df[lista_columnas].mean(axis=1)
            df[columna_+str(rango[0])+'_'+str(rango[1])+'_Sum'] = df[lista_columnas].sum(axis=1)
            df[columna_+str(rango[0])+'_'+str(rango[1])+'_Min'] = df[lista_columnas].min(axis=1)
            df[columna_+str(rango[0])+'_'+str(rango[1])+'_Max'] = df[lista_columnas].max(axis=1)
            df[columna_+str(rango[0])+'_'+str(rango[1])+'_STD'] = df[lista_columnas].std(axis=1)
            df[columna_+str(rango[0])+'_'+str(rango[1])+'_STD'] = df[lista_columnas].std(axis=1)
            if rango[1]>=3:
                df[columna_+str(rango[0])+'_'+str(rango[1])+'_SKEW'] = df[lista_columnas].skew(axis=1)
            if rango[1]>=4:
                df[columna_+str(rango[0])+'_'+str(rango[1])+'_KURT'] = df[lista_columnas].kurt(axis=1)
    return df
rcc_train_prod_cliente_agg = pd.pivot_table(rcc_train_prod_cliente,index=['key_value'], columns=['diff_month'],
                                            values=list(ft.keys()),aggfunc=ft,fill_value=0).reset_index()
rcc_train_prod_cliente_agg.columns = ['cliente_'+x[0]+'_'+str(x[1])+'_'+str(x[2]) if x[0] not in ['key_value'] else x[0] for idx,x in enumerate(rcc_train_prod_cliente_agg.columns)]


rcc_test_prod_cliente_agg = pd.pivot_table(rcc_test_prod_cliente,index=['key_value'], columns=['diff_month'],
                                            values=list(ft.keys()),aggfunc=ft,fill_value=0).reset_index()
aux = rcc_test_prod_cliente_agg.columns
rcc_test_prod_cliente_agg.columns = ['cliente_'+x[0]+'_'+str(x[1])+'_'+str(x[2]) if x[0] not in ['key_value'] else x[0] for idx,x in enumerate(rcc_test_prod_cliente_agg.columns)]

list_names = {}
for i in aux:
    if not i[0] in ['key_value']:
        list_names['cliente_'+i[0]+'_'+i[1]]=None
list_names = list_names.keys()

rcc_train_prod_cliente_agg.to_hdf('../resources/mydata/rcc_train_cliente_agg.h5', 'results_table', mode='w',format='table')
rcc_test_prod_cliente_agg.to_hdf('../resources/mydata/rcc_test_cliente_agg.h5', 'results_table', mode='w',format='table')
rangos = [[0,2],[0,3],[0,4],[0,5],[0,6],[0,7],[0,8],[0,9],[0,11]]

rcc_train_prod_cliente_periods_agg = agg_periods(rcc_train_prod_cliente_agg, rangos, list_names)
rcc_train_prod_cliente_periods_agg.columns = ['C_'+ x if x not in ['key_value'] else x for x in rcc_train_prod_cliente_periods_agg.columns]

rangos = [[0,2],[0,3],[0,4],[0,5],[0,6],[0,7],[0,8],[0,9],[0,11]]

rcc_test_prod_cliente_periods_agg = agg_periods(rcc_test_prod_cliente_agg, rangos, list_names)
rcc_test_prod_cliente_periods_agg.columns = ['C_'+ x if x not in ['key_value'] else x for x in rcc_test_prod_cliente_periods_agg.columns]
rcc_train_prod_cliente_periods_agg.head()
rcc_train_prod_cliente_periods_agg.to_hdf('../resources/mydata/rcc_train_cliente_periods_agg.h5', 'results_table', mode='w',format='table')

rcc_test_prod_cliente_periods_agg.to_hdf('../resources/mydata/rcc_test_cliente_periods_agg.h5', 'results_table', mode='w',format='table')
del rcc_train_prod_cliente_periods_agg
del rcc_test_prod_cliente_periods_agg
gc.collect()
ft={}
mode_agg = lambda x: x.mode().iloc[0]

mode_agg = lambda x: mode(x)[0][0]

lt = ['condicion','saldo','log_saldo']
for x in lt:
    ft[x]=['max','min','mean','sum']    
rcc_train_prod_cliente.columns
rcc_train_prod_cliente.PRODUCTO.unique()
del rcc_train_prod_cliente_agg
del rcc_test_prod_cliente_agg
gc.collect()
rcc_train_prod_cliente_agg = pd.pivot_table(rcc_train_prod_cliente,index=['key_value','PRODUCTO'], columns=['diff_month'],
                                            values=list(ft.keys()),aggfunc=ft,fill_value=0).reset_index()
rcc_train_prod_cliente_agg.columns = ['Prod_Cliente_'+x[0]+'_'+str(x[1])+'_'+str(x[2]) if x[0] not in ['key_value','PRODUCTO'] else x[0] for idx,x in enumerate(rcc_train_prod_cliente_agg.columns)]


rcc_test_prod_cliente_agg = pd.pivot_table(rcc_test_prod_cliente,index=['key_value','PRODUCTO'], columns=['diff_month'],
                                            values=list(ft.keys()),aggfunc=ft,fill_value=0).reset_index()
aux = rcc_test_prod_cliente_agg.columns
rcc_test_prod_cliente_agg.columns = ['Prod_Cliente_'+x[0]+'_'+str(x[1])+'_'+str(x[2]) if x[0] not in ['key_value','PRODUCTO'] else x[0] for idx,x in enumerate(rcc_test_prod_cliente_agg.columns)]

list_names = {}
for i in aux:
    if not i[0] in ['key_value','PRODUCTO']:
        list_names['Prod_Cliente_'+i[0]+'_'+i[1]]=None
list_names = list_names.keys()
rcc_train_prod_cliente_agg.to_hdf('../resources/mydata/rcc_train_PROD_cliente_agg.h5', 'results_table', mode='w',format='table')
rcc_test_prod_cliente_agg.to_hdf('../resources/mydata/rcc_test_PROD_cliente_agg.h5', 'results_table', mode='w',format='table')<jupyter_output><empty_output><jupyter_text>rcc_train_prod_cliente_agg = pd.read_hdf('../resources/mydata/rcc_train_prod_cliente_agg.h5', 'results_table')rcc_test_prod_cliente_agg = pd.read_hdf('../resources/mydata/rcc_test_prod_cliente_agg.h5', 'results_table')<jupyter_code>rangos = [[0,2],[0,5],[0,8]]

rcc_train_prod_cliente_periods_agg = agg_periods(rcc_train_prod_cliente_agg, rangos, list_names)
rcc_train_prod_cliente_periods_agg.columns = ['C_'+ x if x not in ['key_value','PRODUCTO'] else x for x in rcc_train_prod_cliente_periods_agg.columns]

rangos = [[0,2],[0,5],[0,8]]

rcc_test_prod_cliente_periods_agg = agg_periods(rcc_test_prod_cliente_agg, rangos, list_names)
rcc_test_prod_cliente_periods_agg.columns = ['C_'+ x if x not in ['key_value','PRODUCTO'] else x for x in rcc_test_prod_cliente_periods_agg.columns]
rcc_train_prod_cliente_periods_agg.to_hdf('../resources/mydata/rcc_train_PROD_cliente_periods_agg.h5', 'results_table', mode='w',format='table')

rcc_test_prod_cliente_periods_agg.to_hdf('../resources/mydata/rcc_test_PROD_cliente_periods_agg.h5', 'results_table', mode='w',format='table')
del rcc_train_prod_cliente_agg
del rcc_test_prod_cliente_agg
gc.collect()
del rcc_train_prod_cliente_periods_agg
del rcc_test_prod_cliente_periods_agg
gc.collect()
rcc_train_prod_cliente_agg = pd.pivot_table(rcc_train_prod_cliente,index=['PRODUCTO'], columns=['diff_month'],
                                            values=list(ft.keys()),aggfunc=ft,fill_value=0).reset_index()
rcc_train_prod_cliente_agg.columns = ['Prod_Cliente_'+x[0]+'_'+str(x[1])+'_'+str(x[2]) if x[0] not in ['PRODUCTO'] else x[0] for idx,x in enumerate(rcc_train_prod_cliente_agg.columns)]


rcc_test_prod_cliente_agg = pd.pivot_table(rcc_test_prod_cliente,index=['PRODUCTO'], columns=['diff_month'],
                                            values=list(ft.keys()),aggfunc=ft,fill_value=0).reset_index()
aux = rcc_test_prod_cliente_agg.columns
rcc_test_prod_cliente_agg.columns = ['Prod_Cliente_'+x[0]+'_'+str(x[1])+'_'+str(x[2]) if x[0] not in ['PRODUCTO'] else x[0] for idx,x in enumerate(rcc_test_prod_cliente_agg.columns)]

list_names = {}
for i in aux:
    if not i[0] in ['key_value','PRODUCTO']:
        list_names['Prod_Cliente_'+i[0]+'_'+i[1]]=None
list_names = list_names.keys()
rcc_train_prod_cliente_agg.to_hdf('../resources/mydata/rcc_train_PROD_agg.h5', 'results_table', mode='w',format='table')
rcc_test_prod_cliente_agg.to_hdf('../resources/mydata/rcc_test_PROD_agg.h5', 'results_table', mode='w',format='table')
rangos = [[0,2],[0,3],[0,4],[0,5],[0,6],[0,7],[0,8],[0,9],[0,11]]

rcc_train_prod_cliente_periods_agg = agg_periods(rcc_train_prod_cliente_agg, rangos, list_names)
rcc_train_prod_cliente_periods_agg.columns = ['C_'+ x if x not in ['PRODUCTO'] else x for x in rcc_train_prod_cliente_periods_agg.columns]

rangos = [[0,2],[0,3],[0,4],[0,5],[0,6],[0,7],[0,8],[0,9],[0,11]]

rcc_test_prod_cliente_periods_agg = agg_periods(rcc_test_prod_cliente_agg, rangos, list_names)
rcc_test_prod_cliente_periods_agg.columns = ['C_'+ x if x not in ['PRODUCTO'] else x for x in rcc_test_prod_cliente_periods_agg.columns]
rcc_train_prod_cliente_periods_agg.to_hdf('../resources/mydata/rcc_train_PROD_periods_agg.h5', 'results_table', mode='w',format='table')

rcc_test_prod_cliente_periods_agg.to_hdf('../resources/mydata/rcc_test_PROD_periods_agg.h5', 'results_table', mode='w',format='table')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16122686871630482, 'fraction_numerical': 0.01719364709310797, 'mean_word_length': 7.2792521109770805, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '274303', 'license': 'MIT', 'n_tokens_mistral': 5675, 'n_tokens_neox': 5586, 'n_words': 627, 'path': 'codes/Rcc_model.ipynb', 'repo_name': 'CristianLazoQuispe/Datathon-Interbank-2020', 'size': '13726'}