{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Desafio AMERICAN EXPRESS - XGBRegressor\n### Por cuestiones de hardware vamo a dividir tanto el dataset de entrenamiento como el dataset de testeo","metadata":{}},{"cell_type":"code","source":"#Importamos todas las librerias que vamos a usar\nimport pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nfrom sklearn.preprocessing import MinMaxScaler\nimport xgboost as xgb\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import accuracy_score\nfrom sklearn.metrics import roc_auc_score\nfrom sklearn.metrics import roc_curve\nfrom sklearn.metrics import mean_squared_log_error\nfrom sklearn.metrics import mean_absolute_error\nfrom sklearn.metrics import mean_squared_error, max_error\nfrom sklearn.metrics import r2_score, classification_report\nfrom sklearn.preprocessing import LabelEncoder\nimport gc\nfrom sklearn.impute import SimpleImputer\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.552748Z","iopub.execute_input":"2022-08-17T06:32:55.553912Z","iopub.status.idle":"2022-08-17T06:32:55.561518Z","shell.execute_reply.started":"2022-08-17T06:32:55.553856Z","shell.execute_reply":"2022-08-17T06:32:55.560341Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Cargamos los dataframe de entrenamiento en dos parte por que es muy grande","metadata":{}},{"cell_type":"code","source":"#Cargamos los dataframes \ndf_train = pd.read_csv('train_data.csv', nrows=4000000 )","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_2 = pd.read_csv('train_data.csv', skiprows=4000001)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.703064Z","iopub.status.idle":"2022-08-17T06:32:55.703707Z","shell.execute_reply.started":"2022-08-17T06:32:55.703495Z","shell.execute_reply":"2022-08-17T06:32:55.703518Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.704816Z","iopub.status.idle":"2022-08-17T06:32:55.705443Z","shell.execute_reply.started":"2022-08-17T06:32:55.705214Z","shell.execute_reply":"2022-08-17T06:32:55.705235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#colocamos la cabecera al segundo df\ndf_train_2.columns = list(df_train.columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.706565Z","iopub.status.idle":"2022-08-17T06:32:55.706974Z","shell.execute_reply.started":"2022-08-17T06:32:55.706766Z","shell.execute_reply":"2022-08-17T06:32:55.706785Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Concatenamos los dataframes para su procesamiento\ndf_train_tot = pd.concat([df_train, df_train_2])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.708999Z","iopub.status.idle":"2022-08-17T06:32:55.709437Z","shell.execute_reply.started":"2022-08-17T06:32:55.709232Z","shell.execute_reply":"2022-08-17T06:32:55.709252Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train_tot.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.710922Z","iopub.status.idle":"2022-08-17T06:32:55.711354Z","shell.execute_reply.started":"2022-08-17T06:32:55.711148Z","shell.execute_reply":"2022-08-17T06:32:55.711168Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Leemos el train labels, con los customers y el target\ndf_train_labels = pd.read_csv('train_labels.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.712487Z","iopub.status.idle":"2022-08-17T06:32:55.713337Z","shell.execute_reply.started":"2022-08-17T06:32:55.713102Z","shell.execute_reply":"2022-08-17T06:32:55.713131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Agrupamos por customers\ntrain = df_train_tot.groupby(['customer_ID']).last()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.714477Z","iopub.status.idle":"2022-08-17T06:32:55.714857Z","shell.execute_reply.started":"2022-08-17T06:32:55.714665Z","shell.execute_reply":"2022-08-17T06:32:55.714683Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Agregamos al dataframe los customers y el target\ntrain = pd.merge(train, df_train_labels, on=\"customer_ID\", how=\"left\")","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.716206Z","iopub.status.idle":"2022-08-17T06:32:55.716589Z","shell.execute_reply.started":"2022-08-17T06:32:55.716401Z","shell.execute_reply":"2022-08-17T06:32:55.716419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Obtenemos los datos de la columna customer_ID\ncus_id = train['customer_ID']","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.718125Z","iopub.status.idle":"2022-08-17T06:32:55.718524Z","shell.execute_reply.started":"2022-08-17T06:32:55.71833Z","shell.execute_reply":"2022-08-17T06:32:55.718348Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cus_id.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.719631Z","iopub.status.idle":"2022-08-17T06:32:55.720015Z","shell.execute_reply.started":"2022-08-17T06:32:55.719829Z","shell.execute_reply":"2022-08-17T06:32:55.719846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Graficamos los porcentajes de mora de acuerdo a la columna target\ndata = train['target'].value_counts()\nkeys = [0,1]\nexplode = [0, 0.1]\n\nplt.pie(data, labels=keys,explode=explode,autopct='%.0f%%')\n\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.721737Z","iopub.status.idle":"2022-08-17T06:32:55.722326Z","shell.execute_reply.started":"2022-08-17T06:32:55.722005Z","shell.execute_reply":"2022-08-17T06:32:55.722054Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Eliminamos las columnas con mas del 80 % de valores nulos\ntrain=train.dropna(axis=1, thresh=int(0.80*len(train)))","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.725802Z","iopub.status.idle":"2022-08-17T06:32:55.726801Z","shell.execute_reply.started":"2022-08-17T06:32:55.726504Z","shell.execute_reply":"2022-08-17T06:32:55.726533Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.728426Z","iopub.status.idle":"2022-08-17T06:32:55.729475Z","shell.execute_reply.started":"2022-08-17T06:32:55.729171Z","shell.execute_reply":"2022-08-17T06:32:55.729198Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#eliminamos la caracteristica fecha que no la utilizamos \ntrain.drop([\"S_2\"], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.731354Z","iopub.status.idle":"2022-08-17T06:32:55.731903Z","shell.execute_reply.started":"2022-08-17T06:32:55.731623Z","shell.execute_reply":"2022-08-17T06:32:55.731648Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Imputamos los valores hacia atras bfill() y hacia adelante fill()\ntrain=train.ffill().bfill()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.733847Z","iopub.status.idle":"2022-08-17T06:32:55.734463Z","shell.execute_reply.started":"2022-08-17T06:32:55.734175Z","shell.execute_reply":"2022-08-17T06:32:55.734202Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#reseteamos el indice,  reagrupamos por customer id y colocamos la columna customer_ID como index\ntrain=train.reset_index()\ntrain=train.groupby('customer_ID').tail(1)\ntrain=train.set_index(['customer_ID'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.735761Z","iopub.status.idle":"2022-08-17T06:32:55.736327Z","shell.execute_reply.started":"2022-08-17T06:32:55.736022Z","shell.execute_reply":"2022-08-17T06:32:55.736068Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.737658Z","iopub.status.idle":"2022-08-17T06:32:55.738229Z","shell.execute_reply.started":"2022-08-17T06:32:55.737911Z","shell.execute_reply":"2022-08-17T06:32:55.737936Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Chequeamos si hay valores nulos \ntrain.isnull().sum()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.739875Z","iopub.status.idle":"2022-08-17T06:32:55.740447Z","shell.execute_reply.started":"2022-08-17T06:32:55.740162Z","shell.execute_reply":"2022-08-17T06:32:55.740187Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Observamos las caracteristicas categoricas \ntrain.select_dtypes(include=['object'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.74298Z","iopub.status.idle":"2022-08-17T06:32:55.744079Z","shell.execute_reply.started":"2022-08-17T06:32:55.743748Z","shell.execute_reply":"2022-08-17T06:32:55.743776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#copy_train = train.copy()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.745436Z","iopub.status.idle":"2022-08-17T06:32:55.746539Z","shell.execute_reply.started":"2022-08-17T06:32:55.746244Z","shell.execute_reply":"2022-08-17T06:32:55.746272Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Codificamos las variables categoricas para aplicar el modelo de regresion \nfor col in train.columns:\n  if(train[col].dtype == 'object'):\n      le=LabelEncoder()\n      train[col]=le.fit_transform(train[col])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.747765Z","iopub.status.idle":"2022-08-17T06:32:55.748813Z","shell.execute_reply.started":"2022-08-17T06:32:55.748516Z","shell.execute_reply":"2022-08-17T06:32:55.748544Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.750309Z","iopub.status.idle":"2022-08-17T06:32:55.751001Z","shell.execute_reply.started":"2022-08-17T06:32:55.750707Z","shell.execute_reply":"2022-08-17T06:32:55.750735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.752567Z","iopub.status.idle":"2022-08-17T06:32:55.753142Z","shell.execute_reply.started":"2022-08-17T06:32:55.752824Z","shell.execute_reply":"2022-08-17T06:32:55.752849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Eliminamos las columnas que tienen una correlacion menor que 0.4\ntrain.drop(train.columns[train.corrwith(train['target']).abs()<0.4],axis=1,inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.755287Z","iopub.status.idle":"2022-08-17T06:32:55.756356Z","shell.execute_reply.started":"2022-08-17T06:32:55.756056Z","shell.execute_reply":"2022-08-17T06:32:55.756086Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.758238Z","iopub.status.idle":"2022-08-17T06:32:55.758778Z","shell.execute_reply.started":"2022-08-17T06:32:55.758499Z","shell.execute_reply":"2022-08-17T06:32:55.758525Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.760103Z","iopub.status.idle":"2022-08-17T06:32:55.760475Z","shell.execute_reply.started":"2022-08-17T06:32:55.760291Z","shell.execute_reply":"2022-08-17T06:32:55.760308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Creamos una lista con las columnas que vamos a utilizar para el modelo\ncol_para_cargar = list(train.columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.76168Z","iopub.status.idle":"2022-08-17T06:32:55.76207Z","shell.execute_reply.started":"2022-08-17T06:32:55.761859Z","shell.execute_reply":"2022-08-17T06:32:55.761876Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Realizamos una copia del Dataframe de entrenamiento\ntrain_final = train.copy()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.763392Z","iopub.status.idle":"2022-08-17T06:32:55.763756Z","shell.execute_reply.started":"2022-08-17T06:32:55.763573Z","shell.execute_reply":"2022-08-17T06:32:55.763591Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_final.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.764819Z","iopub.status.idle":"2022-08-17T06:32:55.76523Z","shell.execute_reply.started":"2022-08-17T06:32:55.765003Z","shell.execute_reply":"2022-08-17T06:32:55.765027Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#escalamos los datos \ntrain_final = MinMaxScaler().fit_transform(train_final)\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.766148Z","iopub.status.idle":"2022-08-17T06:32:55.766975Z","shell.execute_reply.started":"2022-08-17T06:32:55.766774Z","shell.execute_reply":"2022-08-17T06:32:55.766795Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Lo pasamos a dataframe \ntrain_final =pd.DataFrame(train_final)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.768131Z","iopub.status.idle":"2022-08-17T06:32:55.768494Z","shell.execute_reply.started":"2022-08-17T06:32:55.768314Z","shell.execute_reply":"2022-08-17T06:32:55.768331Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_final.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.769648Z","iopub.status.idle":"2022-08-17T06:32:55.770011Z","shell.execute_reply.started":"2022-08-17T06:32:55.769832Z","shell.execute_reply":"2022-08-17T06:32:55.769848Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Volvemos a colocar las cabeceras\ntrain_final.columns = list(train.columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.771004Z","iopub.status.idle":"2022-08-17T06:32:55.771765Z","shell.execute_reply.started":"2022-08-17T06:32:55.771567Z","shell.execute_reply":"2022-08-17T06:32:55.771587Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_final.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.774974Z","iopub.status.idle":"2022-08-17T06:32:55.77539Z","shell.execute_reply.started":"2022-08-17T06:32:55.775203Z","shell.execute_reply":"2022-08-17T06:32:55.775222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Separamos el target del conjunto de datos \ny_train=train_final['target']\nx_train=train_final.drop(['target'],axis=1)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.776479Z","iopub.status.idle":"2022-08-17T06:32:55.77686Z","shell.execute_reply.started":"2022-08-17T06:32:55.776677Z","shell.execute_reply":"2022-08-17T06:32:55.776695Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#definimos los conjuntos de entrenamiento y prueba\nX_train, X_test, y_train, y_test = train_test_split(x_train, y_train, test_size=0.3, random_state=42)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.777794Z","iopub.status.idle":"2022-08-17T06:32:55.778184Z","shell.execute_reply.started":"2022-08-17T06:32:55.777966Z","shell.execute_reply":"2022-08-17T06:32:55.777984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#instanciamos el modelo XGBoost\nmodel_xgb = xgb.XGBRegressor(colsample_bytree=0.4603, gamma=0.0468, \n                             learning_rate=0.1, max_depth=3, \n                             min_child_weight=1.7817, n_estimators=2200,\n                             reg_alpha=0.4640, reg_lambda=0.8571,\n                             subsample=0.5213, silent=1,\n                             random_state =7, nthread = -1)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.779596Z","iopub.status.idle":"2022-08-17T06:32:55.779955Z","shell.execute_reply.started":"2022-08-17T06:32:55.779777Z","shell.execute_reply":"2022-08-17T06:32:55.779794Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#entrenamos el modelo\nmodel_xgb.fit(X_train,y_train)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.781193Z","iopub.status.idle":"2022-08-17T06:32:55.781563Z","shell.execute_reply.started":"2022-08-17T06:32:55.781374Z","shell.execute_reply":"2022-08-17T06:32:55.78139Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#obtenemos la prediccion\npred=model_xgb.predict(X_test)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.782664Z","iopub.status.idle":"2022-08-17T06:32:55.78307Z","shell.execute_reply.started":"2022-08-17T06:32:55.782857Z","shell.execute_reply":"2022-08-17T06:32:55.782874Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(pred)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.785189Z","iopub.status.idle":"2022-08-17T06:32:55.785846Z","shell.execute_reply.started":"2022-08-17T06:32:55.785459Z","shell.execute_reply":"2022-08-17T06:32:55.785486Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#evaluamos el desempeño del modelo en el conjunto de prueba\ny_train_pred = model_xgb.predict(X_train)\ny_test_pred = model_xgb.predict(X_test)\n#print(accuracy_score(y_train, y_train_pred))\n#print(accuracy_score(y_test, y_test_pred))\nprint(\"Error absoluto Maximo:\",max_error(y_test,y_test_pred))\nprint(\"AUC score:\",roc_auc_score(y_test,y_test_pred)*100)\nprint(\"R2:\",r2_score(y_test,y_test_pred))\nprint (\"Error Absoluto Medio (MAE): \" , mean_absolute_error(y_true=y_test ,y_pred=y_test_pred ))\nprint (\"Error Cuadratico Medio (MSE): \" , mean_squared_error(y_true=y_test ,y_pred=y_test_pred ))\nprint (\"Raiz Cuadrada del Error Cuadratico Medio (RMSE): \" , mean_squared_error(y_true=y_test ,y_pred=y_test_pred, squared=False ))\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.78732Z","iopub.status.idle":"2022-08-17T06:32:55.787875Z","shell.execute_reply.started":"2022-08-17T06:32:55.787594Z","shell.execute_reply":"2022-08-17T06:32:55.787621Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Guardamos el modelo entrenado \nmodel_xgb.save_model(\"modelo_XGB.model\")","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.789472Z","iopub.status.idle":"2022-08-17T06:32:55.790055Z","shell.execute_reply.started":"2022-08-17T06:32:55.78974Z","shell.execute_reply":"2022-08-17T06:32:55.789765Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#guardamos la lista de las columnas que vamos a recuperar para utilizar mas adelante\ncol_para_cargar.to_csv('./colParaCargar.csv',index=False)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.791827Z","iopub.status.idle":"2022-08-17T06:32:55.792395Z","shell.execute_reply.started":"2022-08-17T06:32:55.792104Z","shell.execute_reply":"2022-08-17T06:32:55.792127Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# --------------------------------------------------------------------------------------------------","metadata":{}},{"cell_type":"markdown","source":"### Vamos a dividir el dataset de testeo en 12 partes para tratarlo de manera individual y volverlo a unir para aplicar el modelo.","metadata":{}},{"cell_type":"code","source":"#Cargamos 5 dataframes con 1.000.000 de registros cada uno por una cuestion de tamaño\ndf_test = pd.read_csv('test_data.csv', nrows=10)\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.7939Z","iopub.status.idle":"2022-08-17T06:32:55.794704Z","shell.execute_reply.started":"2022-08-17T06:32:55.79417Z","shell.execute_reply":"2022-08-17T06:32:55.794196Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndf_test_2 = pd.read_csv('test_data.csv', skiprows=1000001, nrows=1000000)\ndf_test_3 = pd.read_csv('test_data.csv', skiprows=2000001, nrows=1000000)\ndf_test_4 = pd.read_csv('test_data.csv', skiprows=3000001, nrows=1000000)\ndf_test_5 = pd.read_csv('test_data.csv', skiprows=4000001, nrows=1000000)\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.796123Z","iopub.status.idle":"2022-08-17T06:32:55.796662Z","shell.execute_reply.started":"2022-08-17T06:32:55.796386Z","shell.execute_reply":"2022-08-17T06:32:55.79641Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.798163Z","iopub.status.idle":"2022-08-17T06:32:55.798551Z","shell.execute_reply.started":"2022-08-17T06:32:55.79836Z","shell.execute_reply":"2022-08-17T06:32:55.798379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Colocamos las cabeceras a todos lo df\ndf_test_2.columns = list(df_test.columns)\ndf_test_3.columns = list(df_test.columns)\ndf_test_4.columns = list(df_test.columns)\ndf_test_5.columns = list(df_test.columns)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.799657Z","iopub.status.idle":"2022-08-17T06:32:55.800067Z","shell.execute_reply.started":"2022-08-17T06:32:55.79985Z","shell.execute_reply":"2022-08-17T06:32:55.799868Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_2.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.802174Z","iopub.status.idle":"2022-08-17T06:32:55.802741Z","shell.execute_reply.started":"2022-08-17T06:32:55.802462Z","shell.execute_reply":"2022-08-17T06:32:55.802483Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#eliminamos las columnas con mas del 80 % de valores nulos\ndf_test=df_test.dropna(axis=1, thresh=int(0.80*len(df_test)))\ndf_test_2=df_test_2.dropna(axis=1, thresh=int(0.80*len(df_test_2)))\ndf_test_3=df_test_3.dropna(axis=1, thresh=int(0.80*len(df_test_3)))\ndf_test_4=df_test_4.dropna(axis=1, thresh=int(0.80*len(df_test_4)))\ndf_test_5=df_test_5.dropna(axis=1, thresh=int(0.80*len(df_test_5)))\n\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.804101Z","iopub.status.idle":"2022-08-17T06:32:55.80462Z","shell.execute_reply.started":"2022-08-17T06:32:55.804346Z","shell.execute_reply":"2022-08-17T06:32:55.804372Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#eliminamos la columna de fecha \ndf_test.drop(['S_2'],axis=1,inplace=True)\ndf_test_2.drop(['S_2'],axis=1,inplace=True)\ndf_test_3.drop(['S_2'],axis=1,inplace=True)\ndf_test_4.drop(['S_2'],axis=1,inplace=True)\ndf_test_5.drop(['S_2'],axis=1,inplace=True)\n\n\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.806184Z","iopub.status.idle":"2022-08-17T06:32:55.806702Z","shell.execute_reply.started":"2022-08-17T06:32:55.806424Z","shell.execute_reply":"2022-08-17T06:32:55.806443Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.809117Z","iopub.status.idle":"2022-08-17T06:32:55.809659Z","shell.execute_reply.started":"2022-08-17T06:32:55.809378Z","shell.execute_reply":"2022-08-17T06:32:55.809404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Convertimos todo en dataframes\ndf_test = pd.DataFrame(df_test)\ndf_test_2 = pd.DataFrame(df_test_2)\ndf_test_3 = pd.DataFrame(df_test_3)\ndf_test_4 = pd.DataFrame(df_test_4)\ndf_test_5 = pd.DataFrame(df_test_5)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.81201Z","iopub.status.idle":"2022-08-17T06:32:55.812456Z","shell.execute_reply.started":"2022-08-17T06:32:55.812235Z","shell.execute_reply":"2022-08-17T06:32:55.812254Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.813443Z","iopub.status.idle":"2022-08-17T06:32:55.813834Z","shell.execute_reply.started":"2022-08-17T06:32:55.813636Z","shell.execute_reply":"2022-08-17T06:32:55.81366Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Concatenamos los dataframes en uno solo\ndf_test_0 = pd.concat([df_test,df_test_2,df_test_3,df_test_4,df_test_5])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.815286Z","iopub.status.idle":"2022-08-17T06:32:55.815688Z","shell.execute_reply.started":"2022-08-17T06:32:55.815495Z","shell.execute_reply":"2022-08-17T06:32:55.815514Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_0.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.817017Z","iopub.status.idle":"2022-08-17T06:32:55.817461Z","shell.execute_reply.started":"2022-08-17T06:32:55.817259Z","shell.execute_reply":"2022-08-17T06:32:55.817278Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_0.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.838905Z","iopub.status.idle":"2022-08-17T06:32:55.839374Z","shell.execute_reply.started":"2022-08-17T06:32:55.839156Z","shell.execute_reply":"2022-08-17T06:32:55.839175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Obtenemos los datos de la columna customer_ID\ncus_id_test = df_test_0['customer_ID']","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.840725Z","iopub.status.idle":"2022-08-17T06:32:55.841204Z","shell.execute_reply.started":"2022-08-17T06:32:55.840973Z","shell.execute_reply":"2022-08-17T06:32:55.840991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Reseteamos el indice,  reagrupamos por customer id y colocamos la columna customer_ID como index\n'''df_test_0=df_test_0.groupby('customer_ID').tail(1)\ndf_test_0=df_test_0.reset_index()'''\ndf_test_0=df_test_0.set_index(['customer_ID'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.842697Z","iopub.status.idle":"2022-08-17T06:32:55.843251Z","shell.execute_reply.started":"2022-08-17T06:32:55.842947Z","shell.execute_reply":"2022-08-17T06:32:55.842973Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_0.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.844782Z","iopub.status.idle":"2022-08-17T06:32:55.845361Z","shell.execute_reply.started":"2022-08-17T06:32:55.84506Z","shell.execute_reply":"2022-08-17T06:32:55.845088Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_test_0.isna().sum())","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.846586Z","iopub.status.idle":"2022-08-17T06:32:55.847141Z","shell.execute_reply.started":"2022-08-17T06:32:55.846841Z","shell.execute_reply":"2022-08-17T06:32:55.846866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Imputamos los valores hacia atras bfill() y hacia adelante fill()\ndf_test_sin_nulos=df_test_0.ffill().bfill()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.848473Z","iopub.status.idle":"2022-08-17T06:32:55.849025Z","shell.execute_reply.started":"2022-08-17T06:32:55.84873Z","shell.execute_reply":"2022-08-17T06:32:55.848756Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.850651Z","iopub.status.idle":"2022-08-17T06:32:55.851233Z","shell.execute_reply.started":"2022-08-17T06:32:55.850911Z","shell.execute_reply":"2022-08-17T06:32:55.850938Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.852499Z","iopub.status.idle":"2022-08-17T06:32:55.853067Z","shell.execute_reply.started":"2022-08-17T06:32:55.852767Z","shell.execute_reply":"2022-08-17T06:32:55.852793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos.select_dtypes(include=['object'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.854494Z","iopub.status.idle":"2022-08-17T06:32:55.855026Z","shell.execute_reply.started":"2022-08-17T06:32:55.854752Z","shell.execute_reply":"2022-08-17T06:32:55.854776Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Codificamos las variables categoricas para aplicar el modelo de regresion \nfor col in df_test_sin_nulos.columns:\n  if(df_test_sin_nulos[col].dtype == 'object'):\n      le=LabelEncoder()\n      df_test_sin_nulos[col]=le.fit_transform(df_test_sin_nulos[col])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.856461Z","iopub.status.idle":"2022-08-17T06:32:55.856997Z","shell.execute_reply.started":"2022-08-17T06:32:55.856719Z","shell.execute_reply":"2022-08-17T06:32:55.856745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.860117Z","iopub.status.idle":"2022-08-17T06:32:55.860497Z","shell.execute_reply.started":"2022-08-17T06:32:55.860311Z","shell.execute_reply":"2022-08-17T06:32:55.860329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.861627Z","iopub.status.idle":"2022-08-17T06:32:55.861995Z","shell.execute_reply.started":"2022-08-17T06:32:55.861811Z","shell.execute_reply":"2022-08-17T06:32:55.861829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Recuperamos las columnas que vamos a utilizar \ncolumns = pd.read_csv('./colParaCargar.csv')\n\n#La convertimos en una lista\ncolumns = columns['0'].tolist()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.863372Z","iopub.status.idle":"2022-08-17T06:32:55.863736Z","shell.execute_reply.started":"2022-08-17T06:32:55.863554Z","shell.execute_reply":"2022-08-17T06:32:55.863572Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final = df_test_sin_nulos[columns]","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.865838Z","iopub.status.idle":"2022-08-17T06:32:55.866585Z","shell.execute_reply.started":"2022-08-17T06:32:55.866234Z","shell.execute_reply":"2022-08-17T06:32:55.866263Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.868275Z","iopub.status.idle":"2022-08-17T06:32:55.868833Z","shell.execute_reply.started":"2022-08-17T06:32:55.868551Z","shell.execute_reply":"2022-08-17T06:32:55.868577Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Guardamos el dataframe para unirlo mas adelante\ndf_test_final.to_csv('./tot_part01_final.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.870653Z","iopub.status.idle":"2022-08-17T06:32:55.871229Z","shell.execute_reply.started":"2022-08-17T06:32:55.87092Z","shell.execute_reply":"2022-08-17T06:32:55.870946Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Volvemos a colocar las cabeceras\ndf_test_final.columns = list(columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.872685Z","iopub.status.idle":"2022-08-17T06:32:55.873262Z","shell.execute_reply.started":"2022-08-17T06:32:55.872944Z","shell.execute_reply":"2022-08-17T06:32:55.872969Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.874935Z","iopub.status.idle":"2022-08-17T06:32:55.875506Z","shell.execute_reply.started":"2022-08-17T06:32:55.875217Z","shell.execute_reply":"2022-08-17T06:32:55.875243Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.87691Z","iopub.status.idle":"2022-08-17T06:32:55.87747Z","shell.execute_reply.started":"2022-08-17T06:32:55.877186Z","shell.execute_reply":"2022-08-17T06:32:55.877212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ---------------------------------------------------------------------------------------------------","metadata":{}},{"cell_type":"markdown","source":"### Realizamos la carga de los datos para su tratamiento ","metadata":{}},{"cell_type":"code","source":"df_test_6 = pd.read_csv('test_data.csv', skiprows=5000001, nrows=1000000)\ndf_test_7 = pd.read_csv('test_data.csv', skiprows=6000001, nrows=1000000)\ndf_test_8 = pd.read_csv('test_data.csv', skiprows=7000001, nrows=1000000)\ndf_test_9 = pd.read_csv('test_data.csv', skiprows=8000001, nrows=1000000)\ndf_test_10 = pd.read_csv('test_data.csv', skiprows=9000001, nrows=1000000)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.878953Z","iopub.status.idle":"2022-08-17T06:32:55.879534Z","shell.execute_reply.started":"2022-08-17T06:32:55.879228Z","shell.execute_reply":"2022-08-17T06:32:55.879255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Colocamos las cabeceras a todos lo df\ndf_test_6.columns = list(df_test.columns)\ndf_test_7.columns = list(df_test.columns)\ndf_test_8.columns = list(df_test.columns)\ndf_test_9.columns = list(df_test.columns)\ndf_test_10.columns = list(df_test.columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.880875Z","iopub.status.idle":"2022-08-17T06:32:55.88139Z","shell.execute_reply.started":"2022-08-17T06:32:55.881165Z","shell.execute_reply":"2022-08-17T06:32:55.881188Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#eliminamos las columnas con muchos datos nulos\ndf_test_6=df_test_6.dropna(axis=1, thresh=int(0.80*len(df_test_6)))\ndf_test_7=df_test_7.dropna(axis=1, thresh=int(0.80*len(df_test_7)))\ndf_test_8=df_test_8.dropna(axis=1, thresh=int(0.80*len(df_test_8)))\ndf_test_9=df_test_9.dropna(axis=1, thresh=int(0.80*len(df_test_9)))\ndf_test_10=df_test_10.dropna(axis=1, thresh=int(0.80*len(df_test_10)))","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.887379Z","iopub.status.idle":"2022-08-17T06:32:55.887785Z","shell.execute_reply.started":"2022-08-17T06:32:55.887591Z","shell.execute_reply":"2022-08-17T06:32:55.88761Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_6.select_dtypes(include=['object'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.889124Z","iopub.status.idle":"2022-08-17T06:32:55.88952Z","shell.execute_reply.started":"2022-08-17T06:32:55.889328Z","shell.execute_reply":"2022-08-17T06:32:55.889346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_6.drop(['S_2'],axis=1,inplace=True)\ndf_test_7.drop(['S_2'],axis=1,inplace=True)\ndf_test_8.drop(['S_2'],axis=1,inplace=True)\ndf_test_9.drop(['S_2'],axis=1,inplace=True)\ndf_test_10.drop(['S_2'],axis=1,inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.890751Z","iopub.status.idle":"2022-08-17T06:32:55.891177Z","shell.execute_reply.started":"2022-08-17T06:32:55.890945Z","shell.execute_reply":"2022-08-17T06:32:55.890963Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_6 = pd.DataFrame(df_test_6)\ndf_test_7 = pd.DataFrame(df_test_7)\ndf_test_8 = pd.DataFrame(df_test_8)\ndf_test_9 = pd.DataFrame(df_test_9)\ndf_test_10 = pd.DataFrame(df_test_10)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.89215Z","iopub.status.idle":"2022-08-17T06:32:55.892528Z","shell.execute_reply.started":"2022-08-17T06:32:55.892341Z","shell.execute_reply":"2022-08-17T06:32:55.892359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Concatenamos los dataframes en uno solo\ndf_test_01 = pd.concat([df_test_6,df_test_7,df_test_8,df_test_9,df_test_10])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.894982Z","iopub.status.idle":"2022-08-17T06:32:55.89538Z","shell.execute_reply.started":"2022-08-17T06:32:55.895194Z","shell.execute_reply":"2022-08-17T06:32:55.895212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Obtenemos los datos de la columna customer_ID\ncus_id_test = df_test_01['customer_ID']\n\n#Seteamos el indice y colocamos la columna customer_ID como index\ndf_test_01=df_test_01.set_index(['customer_ID'])\n\nprint(df_test_01.isna().sum())\n\n#Imputamos los valores hacia atras bfill() y hacia adelante fill()\ndf_test_sin_nulos01=df_test_01.ffill().bfill()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.896709Z","iopub.status.idle":"2022-08-17T06:32:55.897153Z","shell.execute_reply.started":"2022-08-17T06:32:55.896921Z","shell.execute_reply":"2022-08-17T06:32:55.896939Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos01.select_dtypes(include=['object'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.898283Z","iopub.status.idle":"2022-08-17T06:32:55.898678Z","shell.execute_reply.started":"2022-08-17T06:32:55.898476Z","shell.execute_reply":"2022-08-17T06:32:55.898495Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Codificamos las variables categoricas para aplicar el modelo de regresion \nfor col in df_test_sin_nulos01.columns:\n  if(df_test_sin_nulos01[col].dtype == 'object'):\n      le=LabelEncoder()\n      df_test_sin_nulos01[col]=le.fit_transform(df_test_sin_nulos01[col])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.899946Z","iopub.status.idle":"2022-08-17T06:32:55.900376Z","shell.execute_reply.started":"2022-08-17T06:32:55.900181Z","shell.execute_reply":"2022-08-17T06:32:55.9002Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Recuperamos las columnas que vamos a utilizar \ncolumns = pd.read_csv('./colParaCargar.csv')\n\n#La convertimos en una lista\ncolumns = columns['0'].tolist()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.901503Z","iopub.status.idle":"2022-08-17T06:32:55.901886Z","shell.execute_reply.started":"2022-08-17T06:32:55.901695Z","shell.execute_reply":"2022-08-17T06:32:55.901714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final02 = df_test_sin_nulos01[columns]","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.90485Z","iopub.status.idle":"2022-08-17T06:32:55.905279Z","shell.execute_reply.started":"2022-08-17T06:32:55.905081Z","shell.execute_reply":"2022-08-17T06:32:55.905101Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.90659Z","iopub.status.idle":"2022-08-17T06:32:55.906979Z","shell.execute_reply.started":"2022-08-17T06:32:55.906787Z","shell.execute_reply":"2022-08-17T06:32:55.906806Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ------------------------------------------------------------------------------","metadata":{}},{"cell_type":"markdown","source":"### Cargamos los ultimos datos para tratarlos","metadata":{}},{"cell_type":"code","source":"df_test_11 = pd.read_csv('test_data.csv', skiprows=10000001, nrows=1000000)\ndf_test_12 = pd.read_csv('test_data.csv', skiprows=11000001, nrows=1000000)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.908746Z","iopub.status.idle":"2022-08-17T06:32:55.909172Z","shell.execute_reply.started":"2022-08-17T06:32:55.908942Z","shell.execute_reply":"2022-08-17T06:32:55.90896Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_11.columns = list(df_test.columns)\ndf_test_12.columns = list(df_test.columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.911298Z","iopub.status.idle":"2022-08-17T06:32:55.911693Z","shell.execute_reply.started":"2022-08-17T06:32:55.911501Z","shell.execute_reply":"2022-08-17T06:32:55.911519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_11=df_test_10.dropna(axis=1, thresh=int(0.80*len(df_test_11)))\ndf_test_12=df_test_12.dropna(axis=1, thresh=int(0.80*len(df_test_12)))","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.912594Z","iopub.status.idle":"2022-08-17T06:32:55.912969Z","shell.execute_reply.started":"2022-08-17T06:32:55.91278Z","shell.execute_reply":"2022-08-17T06:32:55.912798Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_12.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.914383Z","iopub.status.idle":"2022-08-17T06:32:55.914771Z","shell.execute_reply.started":"2022-08-17T06:32:55.914583Z","shell.execute_reply":"2022-08-17T06:32:55.914601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_11.drop(['S_2'],axis=1,inplace=True)\ndf_test_12.drop(['S_2'],axis=1,inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.916099Z","iopub.status.idle":"2022-08-17T06:32:55.916482Z","shell.execute_reply.started":"2022-08-17T06:32:55.916294Z","shell.execute_reply":"2022-08-17T06:32:55.916313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_11 = pd.DataFrame(df_test_11)\ndf_test_12 = pd.DataFrame(df_test_12)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.917906Z","iopub.status.idle":"2022-08-17T06:32:55.918337Z","shell.execute_reply.started":"2022-08-17T06:32:55.91814Z","shell.execute_reply":"2022-08-17T06:32:55.91816Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_02 = pd.concat([df_test_11,df_test_12])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.919661Z","iopub.status.idle":"2022-08-17T06:32:55.920074Z","shell.execute_reply.started":"2022-08-17T06:32:55.919853Z","shell.execute_reply":"2022-08-17T06:32:55.919872Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Obtenemos los datos de la columna customer_ID\ncus_id_test = df_test_02['customer_ID']\n\n#Seteamos el indice y colocamos la columna customer_ID como index\n\ndf_test_02=df_test_02.set_index(['customer_ID'])\n\nprint(df_test_02.isna().sum())\n\n#Imputamos los valores hacia atras bfill() y hacia adelante fill()\ndf_test_sin_nulos02=df_test_02.ffill().bfill()\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.921262Z","iopub.status.idle":"2022-08-17T06:32:55.921637Z","shell.execute_reply.started":"2022-08-17T06:32:55.921442Z","shell.execute_reply":"2022-08-17T06:32:55.921459Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_sin_nulos02.select_dtypes(include=['object'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.923003Z","iopub.status.idle":"2022-08-17T06:32:55.923411Z","shell.execute_reply.started":"2022-08-17T06:32:55.923222Z","shell.execute_reply":"2022-08-17T06:32:55.92324Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Codificamos las variables categoricas para aplicar el modelo de regresion \nfor col in df_test_sin_nulos02.columns:\n  if(df_test_sin_nulos02[col].dtype == 'object'):\n      le=LabelEncoder()\n      df_test_sin_nulos02[col]=le.fit_transform(df_test_sin_nulos02[col])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.924592Z","iopub.status.idle":"2022-08-17T06:32:55.924972Z","shell.execute_reply.started":"2022-08-17T06:32:55.924785Z","shell.execute_reply":"2022-08-17T06:32:55.924803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Recuperamos las columnas que vamos a utilizar \ncolumns = pd.read_csv('./colParaCargar.csv')\n\n#La convertimos en una lista\ncolumns = columns['0'].tolist()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.926736Z","iopub.status.idle":"2022-08-17T06:32:55.927158Z","shell.execute_reply.started":"2022-08-17T06:32:55.926931Z","shell.execute_reply":"2022-08-17T06:32:55.926949Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final03 = df_test_sin_nulos02[columns]","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.928157Z","iopub.status.idle":"2022-08-17T06:32:55.928546Z","shell.execute_reply.started":"2022-08-17T06:32:55.928352Z","shell.execute_reply":"2022-08-17T06:32:55.92837Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final03.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.929746Z","iopub.status.idle":"2022-08-17T06:32:55.930151Z","shell.execute_reply.started":"2022-08-17T06:32:55.929929Z","shell.execute_reply":"2022-08-17T06:32:55.929946Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final02.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.931265Z","iopub.status.idle":"2022-08-17T06:32:55.931654Z","shell.execute_reply.started":"2022-08-17T06:32:55.93145Z","shell.execute_reply":"2022-08-17T06:32:55.931468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final02.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.93279Z","iopub.status.idle":"2022-08-17T06:32:55.933224Z","shell.execute_reply.started":"2022-08-17T06:32:55.932994Z","shell.execute_reply":"2022-08-17T06:32:55.933024Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_test_final03.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.937458Z","iopub.status.idle":"2022-08-17T06:32:55.937846Z","shell.execute_reply.started":"2022-08-17T06:32:55.937659Z","shell.execute_reply":"2022-08-17T06:32:55.937676Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.938773Z","iopub.status.idle":"2022-08-17T06:32:55.939291Z","shell.execute_reply.started":"2022-08-17T06:32:55.938953Z","shell.execute_reply":"2022-08-17T06:32:55.93897Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Unimos (concatenamos) los dataset con los datos tratados \ndf_tot_final = pd.concat([ df_test_final02, df_test_final03])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.940717Z","iopub.status.idle":"2022-08-17T06:32:55.941124Z","shell.execute_reply.started":"2022-08-17T06:32:55.940904Z","shell.execute_reply":"2022-08-17T06:32:55.940921Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_tot_final.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.942478Z","iopub.status.idle":"2022-08-17T06:32:55.942888Z","shell.execute_reply.started":"2022-08-17T06:32:55.942696Z","shell.execute_reply":"2022-08-17T06:32:55.942715Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_tot_final.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.944077Z","iopub.status.idle":"2022-08-17T06:32:55.944471Z","shell.execute_reply.started":"2022-08-17T06:32:55.944284Z","shell.execute_reply":"2022-08-17T06:32:55.944302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Guardamos  el dataframe  para utilizarlo mas adelante\ndf_tot_final.to_csv('./tot_part02_final.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.945803Z","iopub.status.idle":"2022-08-17T06:32:55.946221Z","shell.execute_reply.started":"2022-08-17T06:32:55.946001Z","shell.execute_reply":"2022-08-17T06:32:55.94602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Recuperamos el primer dataframe tratado\ntot_part01 = pd.read_csv('./tot_part01_final.csv' )","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.947389Z","iopub.status.idle":"2022-08-17T06:32:55.947771Z","shell.execute_reply.started":"2022-08-17T06:32:55.947581Z","shell.execute_reply":"2022-08-17T06:32:55.947599Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tot_part01.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.949411Z","iopub.status.idle":"2022-08-17T06:32:55.949768Z","shell.execute_reply.started":"2022-08-17T06:32:55.949589Z","shell.execute_reply":"2022-08-17T06:32:55.949605Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tot_part01.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.952211Z","iopub.status.idle":"2022-08-17T06:32:55.952749Z","shell.execute_reply.started":"2022-08-17T06:32:55.952476Z","shell.execute_reply":"2022-08-17T06:32:55.952503Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Recuperamos el segundo dataframe tratado\ntot_part02 = pd.read_csv('./tot_part02_final.csv')","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.954205Z","iopub.status.idle":"2022-08-17T06:32:55.954741Z","shell.execute_reply.started":"2022-08-17T06:32:55.954461Z","shell.execute_reply":"2022-08-17T06:32:55.954488Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tot_part02.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.956084Z","iopub.status.idle":"2022-08-17T06:32:55.956617Z","shell.execute_reply.started":"2022-08-17T06:32:55.956339Z","shell.execute_reply":"2022-08-17T06:32:55.956365Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tot_part02.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.958337Z","iopub.status.idle":"2022-08-17T06:32:55.958867Z","shell.execute_reply.started":"2022-08-17T06:32:55.958591Z","shell.execute_reply":"2022-08-17T06:32:55.958617Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Unimos (concatenamos) los dataframes tratados\ntotal_final =  pd.concat([tot_part01, tot_part02])\ntotal_final.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.960173Z","iopub.status.idle":"2022-08-17T06:32:55.960707Z","shell.execute_reply.started":"2022-08-17T06:32:55.960427Z","shell.execute_reply":"2022-08-17T06:32:55.960452Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.961991Z","iopub.status.idle":"2022-08-17T06:32:55.962541Z","shell.execute_reply.started":"2022-08-17T06:32:55.962264Z","shell.execute_reply":"2022-08-17T06:32:55.96229Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.select_dtypes(include=['object'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.963903Z","iopub.status.idle":"2022-08-17T06:32:55.9644Z","shell.execute_reply.started":"2022-08-17T06:32:55.964184Z","shell.execute_reply":"2022-08-17T06:32:55.964206Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.966772Z","iopub.status.idle":"2022-08-17T06:32:55.967239Z","shell.execute_reply.started":"2022-08-17T06:32:55.966943Z","shell.execute_reply":"2022-08-17T06:32:55.96696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Seteamos el indice y colocamos la columna customer_ID como index\ntotal_final=total_final.set_index(['customer_ID'])","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.968534Z","iopub.status.idle":"2022-08-17T06:32:55.968928Z","shell.execute_reply.started":"2022-08-17T06:32:55.96873Z","shell.execute_reply":"2022-08-17T06:32:55.968748Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.970442Z","iopub.status.idle":"2022-08-17T06:32:55.970835Z","shell.execute_reply.started":"2022-08-17T06:32:55.970645Z","shell.execute_reply":"2022-08-17T06:32:55.970664Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.drop(['index'], axis = 1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.972229Z","iopub.status.idle":"2022-08-17T06:32:55.972613Z","shell.execute_reply.started":"2022-08-17T06:32:55.972422Z","shell.execute_reply":"2022-08-17T06:32:55.972441Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.head()","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.973647Z","iopub.status.idle":"2022-08-17T06:32:55.974059Z","shell.execute_reply.started":"2022-08-17T06:32:55.973842Z","shell.execute_reply":"2022-08-17T06:32:55.973859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.975941Z","iopub.status.idle":"2022-08-17T06:32:55.976357Z","shell.execute_reply.started":"2022-08-17T06:32:55.976162Z","shell.execute_reply":"2022-08-17T06:32:55.976181Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#escalamos los datos \ntotal_final = MinMaxScaler().fit_transform(total_final)\n#Lo pasamos a dataframe \ntotal_final =pd.DataFrame(total_final)\n#Volvemos a colocar las cabeceras\ntotal_final.columns = list(columns)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.977963Z","iopub.status.idle":"2022-08-17T06:32:55.978383Z","shell.execute_reply.started":"2022-08-17T06:32:55.978188Z","shell.execute_reply":"2022-08-17T06:32:55.978208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"total_final.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.979595Z","iopub.status.idle":"2022-08-17T06:32:55.979974Z","shell.execute_reply.started":"2022-08-17T06:32:55.979787Z","shell.execute_reply":"2022-08-17T06:32:55.979805Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Cargamos el modelo que habiamos entrenado.\nmodelo_importado = xgb.XGBRegressor()\nmodelo_importado.load_model(\"./modelo_XGB.model\")","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.981661Z","iopub.status.idle":"2022-08-17T06:32:55.982068Z","shell.execute_reply.started":"2022-08-17T06:32:55.981855Z","shell.execute_reply":"2022-08-17T06:32:55.981873Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Realizamos la Prediccion\ntest_predict = modelo_importado.predict(total_final)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.983337Z","iopub.status.idle":"2022-08-17T06:32:55.983723Z","shell.execute_reply.started":"2022-08-17T06:32:55.983532Z","shell.execute_reply":"2022-08-17T06:32:55.983551Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Lo pasamos a un dataframe \ntest_predict = pd.DataFrame(test_predict)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.985366Z","iopub.status.idle":"2022-08-17T06:32:55.985756Z","shell.execute_reply.started":"2022-08-17T06:32:55.985562Z","shell.execute_reply":"2022-08-17T06:32:55.98558Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_predict.shape","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.987105Z","iopub.status.idle":"2022-08-17T06:32:55.987495Z","shell.execute_reply.started":"2022-08-17T06:32:55.987305Z","shell.execute_reply":"2022-08-17T06:32:55.987323Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = pd.read_csv(\"./submission_final.csv\")","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.988624Z","iopub.status.idle":"2022-08-17T06:32:55.989004Z","shell.execute_reply.started":"2022-08-17T06:32:55.988815Z","shell.execute_reply":"2022-08-17T06:32:55.988833Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = pd.read_csv(\"./sample_submission.csv\")\nsubmission.loc[:,'prediction'] = test_predict[0]","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.991336Z","iopub.status.idle":"2022-08-17T06:32:55.991733Z","shell.execute_reply.started":"2022-08-17T06:32:55.991541Z","shell.execute_reply":"2022-08-17T06:32:55.99156Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = submission.groupby(['customer_ID']).tail(1)","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.993215Z","iopub.status.idle":"2022-08-17T06:32:55.993608Z","shell.execute_reply.started":"2022-08-17T06:32:55.993414Z","shell.execute_reply":"2022-08-17T06:32:55.993433Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:55.995402Z","iopub.execute_input":"2022-08-17T06:32:55.995714Z","iopub.status.idle":"2022-08-17T06:32:56.021356Z","shell.execute_reply.started":"2022-08-17T06:32:55.995683Z","shell.execute_reply":"2022-08-17T06:32:56.019764Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Guardamos el dataframe con las predicciones\nsubmission.to_csv('./submission_final.csv', index=False)\n\n","metadata":{"execution":{"iopub.status.busy":"2022-08-17T06:32:56.022546Z","iopub.status.idle":"2022-08-17T06:32:56.023146Z","shell.execute_reply.started":"2022-08-17T06:32:56.022826Z","shell.execute_reply":"2022-08-17T06:32:56.022854Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Gracias por leer este documento, espero les ayude en sus proyectos de Machine Learning, saludos...","metadata":{}}]}