{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"}],"dockerImageVersionId":30684,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Home Credict Predicter. Inference in model. \n**LightGBM**","metadata":{}},{"cell_type":"markdown","source":"## Import Libraries:","metadata":{}},{"cell_type":"code","source":"# Import libraries:\n\nimport polars as pl  # Fast and efficient data processing\nimport numpy as np  # Linear algebra\nimport pandas as pd  # Data processing\nimport lightgbm as lgb  # Library for Gradient Boosting models\nfrom sklearn.model_selection import train_test_split  # \nfrom sklearn.metrics import roc_auc_score  # Metric to evaluate classification models\nimport category_encoders as ce  # Tools for coding categorical variables\nimport gc  # Garbage collection to manage memory\nimport os # Operating System Management\nimport seaborn as sns  # Matplotlib-based data visualization\nimport matplotlib.pyplot as plt  # Plot library for creating graphs and figures","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:48.828706Z","iopub.execute_input":"2024-05-27T09:20:48.829077Z","iopub.status.idle":"2024-05-27T09:20:54.267846Z","shell.execute_reply.started":"2024-05-27T09:20:48.829047Z","shell.execute_reply":"2024-05-27T09:20:54.266780Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Create Path's:","metadata":{}},{"cell_type":"code","source":"# Base path:\nBASE = '/kaggle/input/home-credit-credit-risk-model-stability/'\n\n# CSV files:\ncsv_path = BASE+'csv_files/'\n\n# Train and test csv files:\ntest_csv = csv_path+'test/'\ntrain_csv = csv_path+'train/'\n\n# Parquet files:\nparquet_path = BASE+'parquet_files/'\n\n# Train and test parquet files:\ntest_pq = parquet_path+'test/'\ntrain_pq = parquet_path+'train/'\n\n# Features definitions path:\nfeature_path = BASE+'feature_definitions.csv'\n\n# Sample sumbmission:\nsample = BASE+'sample_submission.csv'","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.269949Z","iopub.execute_input":"2024-05-27T09:20:54.270662Z","iopub.status.idle":"2024-05-27T09:20:54.277797Z","shell.execute_reply.started":"2024-05-27T09:20:54.270622Z","shell.execute_reply":"2024-05-27T09:20:54.276780Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Paths for Train and test bases in parquet and CSV files:","metadata":{}},{"cell_type":"code","source":"testBase_csv = test_csv+'test_base.csv'\ntrainBase_csv = train_csv+'train_base.csv'\n\ntestBase_pq = test_pq+'test_base.parquet'\ntrainBase_pq = train_pq+'train_base.parquet'","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.279401Z","iopub.execute_input":"2024-05-27T09:20:54.279812Z","iopub.status.idle":"2024-05-27T09:20:54.296349Z","shell.execute_reply.started":"2024-05-27T09:20:54.279773Z","shell.execute_reply":"2024-05-27T09:20:54.295384Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Feature importance.\n\nCreate a sample model for feature importance.","metadata":{}},{"cell_type":"code","source":"# Specify data types for each column\ndtype_dict = {'Variable': str, 'Description': str}\n# Upload the features_definitions.csv file\nfeatures = pd.read_csv(feature_path, dtype=dtype_dict)\ndisplay(features)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.299003Z","iopub.execute_input":"2024-05-27T09:20:54.299388Z","iopub.status.idle":"2024-05-27T09:20:54.333363Z","shell.execute_reply.started":"2024-05-27T09:20:54.299351Z","shell.execute_reply":"2024-05-27T09:20:54.332250Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### We will create a dictionary to filter a search and thus categorize the characteristics:","metadata":{}},{"cell_type":"markdown","source":"This dictionary can be modified, since it only went to an open interpretation, after carefully reviewing the value of each variable, and what its relativity is.\n\nSubsequently, he creates a category to be able to carry out a more balanced search, according to the level of importance when filtering a good prospect for a loan.\n\nIn the research, I was able to conclude that people who have a greater number of characteristics are considered active people, many of them, even in the same Tests, carried out by people who have a family, no more than 2 children. They have higher education and a partner.\n\nPeople who also have a lower income, but are single, are optimal for a loan, only people who present their papers in order, translated into data, are people who have characteristics without a null value, except for late payments, since the person would present a delinquent character.\n\nFirst, we are going to do a filter to find relative features, according to keywords, built within a dictionary, to filter our file **features_definitions.csv**\n\nLater, we will calculate the calculation for each category, per user, in this case called **\"case_id\"**","metadata":{}},{"cell_type":"code","source":"dictionary_importance = {\n    \"important\": [\n        \"Credit\",\"Deposit\",\"Incoming\",\"Outgoing\",\"Instalments\",\n        \"Interest\",\"Monthly\",\"Average\", \"Loan\", \"Amount\", \"Balance\", \n        \"Transactions\", \"Past\", \"Due\", \"Children\", \"blocking\", \n        \"cancellation\", \"Conflict\", \"Card\", \"Family\",\"Education\"\n    ],\n    \"medium\": [\n        \"Zip\",\"Number\",\"Applications\",\"Contracts\",\"Tax\",\n        \"Deductions\",\"Employment\",\"Length\",\"Expenses\",\"History\",\n        \"Previous\",\"Address\",\"Bank\",\"Account\",\"Date\",\"Client\",\"Income\",\n        \"Type\",\"Status\",\"Rejections\",\"Cancellations\",\"Marital\", \"Date\"\n    ],\n    \"low\": [\n        \"Classification\",\"Phone\",\"Mobile\",\"Employer\",\"Instalment\",\n        \"Payments\",\"Debt\",\"Holiday\",\"Flag\",\"Language\",\"Type\",\"Product\",\n        \"Customer\",\"Service\",\"Provider\",\"Source\",\"Campaign\",\"Sale\",\"Process\",\n        \"Relationship\",\"Channel\",\"Category\",\"Purpose\",\"Contact\",\"Person\",\"Subject\",\n        \"Name\",\"Role\",\"Location\",\"Point\",\"Seller\",\"Gender\",\"Term\",\"Reason\",\"Industry\",\n        \"Profession\",\"Probability\",\"Performance\",\"Guarantee\",\"Flag\",\"Order\",\"Prolongations\",\n        \"Estimated\",\"Record\",\"Type\",\"Deduction\",\"Disbursement\",\"Transaction\",\"Revolving\",\n        \"Cross-sell\",\"Reference\",\"Primary\",\"Commodity\",\"Result\",\"Status\",\"Type\",\"Category\",\"Date\",\n        \"Total\",\"Frequency\",\"Method\",\"Result\",\"Role\",\"Frequency\",\"Count\",\"Total\",\"Amount\",\"Count\",\"Person\",\n        \"Type\",\"Flag\",\"Residual\",\"Open\",\"Close\",\"Sum\",\"Value\",\"Payment\",\"Days\",\"Overdue\",\"Year\",\"Month\"\n    ]\n}","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.334855Z","iopub.execute_input":"2024-05-27T09:20:54.335284Z","iopub.status.idle":"2024-05-27T09:20:54.347190Z","shell.execute_reply.started":"2024-05-27T09:20:54.335242Z","shell.execute_reply":"2024-05-27T09:20:54.345879Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Defines the assign importance_function\ndef assign_importance(description):\n    for importance, keywords in dictionary_importance.items():\n        for keyword in keywords:\n            if keyword.lower() in description.lower():\n                if importance == \"important\":\n                    return \"Important\"\n                elif importance == \"medium\":\n                    return \"Medium\"\n                elif importance == \"low\":\n                    return \"Low\"\n    return \"Default\"  # We assign a default value if there is no specific keyword","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.348661Z","iopub.execute_input":"2024-05-27T09:20:54.349017Z","iopub.status.idle":"2024-05-27T09:20:54.360027Z","shell.execute_reply.started":"2024-05-27T09:20:54.348987Z","shell.execute_reply":"2024-05-27T09:20:54.358917Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Apply the assign_importance function to the 'Description' column of the DataFrame\nfeatures['Importance_Name'] = features['Description'].apply(assign_importance)\n# Change categorical variables to numeric:\nencoder = ce.OrdinalEncoder(cols=['Importance_Name'])\nft_importance = encoder.fit_transform(features)\nfeatures['Importance_Number'] = ft_importance['Importance_Name']\nfeatures.head()","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.361377Z","iopub.execute_input":"2024-05-27T09:20:54.361758Z","iopub.status.idle":"2024-05-27T09:20:54.401406Z","shell.execute_reply.started":"2024-05-27T09:20:54.361727Z","shell.execute_reply":"2024-05-27T09:20:54.400398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"features.tail()","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.402895Z","iopub.execute_input":"2024-05-27T09:20:54.403577Z","iopub.status.idle":"2024-05-27T09:20:54.417079Z","shell.execute_reply.started":"2024-05-27T09:20:54.403543Z","shell.execute_reply":"2024-05-27T09:20:54.415995Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(features['Importance_Name'].unique())\nprint(features['Importance_Name'].value_counts())","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.418507Z","iopub.execute_input":"2024-05-27T09:20:54.418907Z","iopub.status.idle":"2024-05-27T09:20:54.431412Z","shell.execute_reply.started":"2024-05-27T09:20:54.418875Z","shell.execute_reply":"2024-05-27T09:20:54.430216Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Filter the DataFrame to show only High Importance data\nhigh_importance_data = features[features['Importance_Name'] == 'Important']\n# Filter the DataFrame to show only data of medium importance\nmedium_importance_data = features[features['Importance_Name'] == 'Medium']\n# Filter the DataFrame to show only low importance data\nlow_importance_data = features[features['Importance_Name'] == 'Low']","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.436714Z","iopub.execute_input":"2024-05-27T09:20:54.437206Z","iopub.status.idle":"2024-05-27T09:20:54.449747Z","shell.execute_reply.started":"2024-05-27T09:20:54.437167Z","shell.execute_reply":"2024-05-27T09:20:54.448614Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### High importance:","metadata":{}},{"cell_type":"code","source":"# Print all data with high importance in 'Importance_Number' column\nprint(high_importance_data[['Description', 'Importance_Number']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.450982Z","iopub.execute_input":"2024-05-27T09:20:54.451331Z","iopub.status.idle":"2024-05-27T09:20:54.466869Z","shell.execute_reply.started":"2024-05-27T09:20:54.451271Z","shell.execute_reply":"2024-05-27T09:20:54.465684Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print all data with highest importance in 'Importance_Name' and 'Variable' column\nprint(high_importance_data[['Variable', 'Importance_Name']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.468050Z","iopub.execute_input":"2024-05-27T09:20:54.468401Z","iopub.status.idle":"2024-05-27T09:20:54.481702Z","shell.execute_reply.started":"2024-05-27T09:20:54.468370Z","shell.execute_reply":"2024-05-27T09:20:54.480538Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Medium importance:","metadata":{}},{"cell_type":"code","source":"# Print all medium importance data in 'Importance_Number' column\nprint(medium_importance_data[['Description', 'Importance_Number']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.483078Z","iopub.execute_input":"2024-05-27T09:20:54.483562Z","iopub.status.idle":"2024-05-27T09:20:54.497479Z","shell.execute_reply.started":"2024-05-27T09:20:54.483530Z","shell.execute_reply":"2024-05-27T09:20:54.496235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print all data with medium importance in 'Importance_Name' and 'Variable' column\nprint(medium_importance_data[['Variable', 'Importance_Name']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.499076Z","iopub.execute_input":"2024-05-27T09:20:54.499537Z","iopub.status.idle":"2024-05-27T09:20:54.513608Z","shell.execute_reply.started":"2024-05-27T09:20:54.499497Z","shell.execute_reply":"2024-05-27T09:20:54.512364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Low Importance:","metadata":{}},{"cell_type":"code","source":"# Print all low importance data in 'Importance_Number' column\nprint(low_importance_data[['Description', 'Importance_Number']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.514894Z","iopub.execute_input":"2024-05-27T09:20:54.515242Z","iopub.status.idle":"2024-05-27T09:20:54.533597Z","shell.execute_reply.started":"2024-05-27T09:20:54.515211Z","shell.execute_reply":"2024-05-27T09:20:54.532244Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print all low importance data in 'Importance_Name' and 'Variable' column\nprint(low_importance_data[['Variable', 'Importance_Name']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.534911Z","iopub.execute_input":"2024-05-27T09:20:54.535327Z","iopub.status.idle":"2024-05-27T09:20:54.548464Z","shell.execute_reply.started":"2024-05-27T09:20:54.535281Z","shell.execute_reply":"2024-05-27T09:20:54.547285Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Default metod:","metadata":{}},{"cell_type":"code","source":"# Filter the DataFrame to show only the default data\ndefault_importance_data = features[features['Importance_Name'] == 'Default']\nprint(default_importance_data[['Description', 'Importance_Number']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.549677Z","iopub.execute_input":"2024-05-27T09:20:54.550053Z","iopub.status.idle":"2024-05-27T09:20:54.567000Z","shell.execute_reply.started":"2024-05-27T09:20:54.550021Z","shell.execute_reply":"2024-05-27T09:20:54.565731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print all low importance data in 'Importance_Name' column\nprint(default_importance_data[['Variable', 'Importance_Name']])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.568362Z","iopub.execute_input":"2024-05-27T09:20:54.568684Z","iopub.status.idle":"2024-05-27T09:20:54.590994Z","shell.execute_reply.started":"2024-05-27T09:20:54.568656Z","shell.execute_reply":"2024-05-27T09:20:54.589719Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Save the DataFrame to a CSV file\nfeatures.to_csv('features_importance.csv', index=False)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.592407Z","iopub.execute_input":"2024-05-27T09:20:54.592760Z","iopub.status.idle":"2024-05-27T09:20:54.609476Z","shell.execute_reply.started":"2024-05-27T09:20:54.592729Z","shell.execute_reply":"2024-05-27T09:20:54.608377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create path:\nft_importance_path = '/kaggle/working/features_importance.csv'","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.611127Z","iopub.execute_input":"2024-05-27T09:20:54.611512Z","iopub.status.idle":"2024-05-27T09:20:54.617030Z","shell.execute_reply.started":"2024-05-27T09:20:54.611472Z","shell.execute_reply":"2024-05-27T09:20:54.615640Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Create functions for data ingestion","metadata":{}},{"cell_type":"code","source":"from datetime import datetime\n\ndef safe_int_conversion(value):\n    try:\n        return int(float(value))\n    except ValueError:\n        return value  # Returns the original value if it cannot be converted\n\ndef convert_to_date(value):\n    try:\n        return datetime.strptime(value, \"%Y-%m-%d\").date()\n    except ValueError:\n        return None  # Returns None if the conversion fails\n\ndef set_table_dtypes(df: pl.DataFrame) -> pl.DataFrame:\n    for col in df.columns:\n        if col.endswith(\"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n        elif col.endswith(\"D\"):\n            df = df.with_columns(pl.col(col).cast(pl.String).alias(col))  # Convert to String\n        elif col.endswith(\"P\"):\n            df = df.with_columns(pl.col(col).map_elements(safe_int_conversion, return_dtype=pl.Int32).alias(col))\n        elif col.endswith(\"T\") or col.endswith(\"L\"):\n            df = df.with_columns(pl.col(col).cast(pl.String).alias(col))\n        elif col in [\"case_id\", \"WEEK_NUM\", \"MONTH\", \"num_group1\", \"num_group2\"]:\n            pass\n        elif col == \"target\":\n            df = df.with_columns(pl.col(col).cast(pl.Boolean).alias(col))\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.618625Z","iopub.execute_input":"2024-05-27T09:20:54.619008Z","iopub.status.idle":"2024-05-27T09:20:54.631747Z","shell.execute_reply.started":"2024-05-27T09:20:54.618976Z","shell.execute_reply":"2024-05-27T09:20:54.630625Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Function to convert the strings of a Pandas DataFrame to category type\ndef convert_strings(df: pd.DataFrame) -> pd.DataFrame:\n    for col in df.columns:\n        if df[col].dtype.name in ['object', 'string']:\n            # Handling null values\n            df[col] = df[col].fillna(\"Unknown\")\n            \n            # Remove whitespace\n            df[col] = df[col].str.strip()\n            \n            # Convert to category type\n            df[col] = df[col].astype(\"string\").astype('category')\n            \n            # Get current categories and add \"Unknown\" if it doesn't exist\n            current_categories = df[col].cat.categories\n            if \"Unknown\" not in current_categories:\n                new_categories = current_categories.to_list() + [\"Unknown\"]\n            else:\n                new_categories = current_categories.to_list()\n            \n            # Define new categorical type\n            new_dtype = pd.CategoricalDtype(categories=new_categories, ordered=True)\n            df[col] = df[col].astype(new_dtype)\n            \n    return df\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.633474Z","iopub.execute_input":"2024-05-27T09:20:54.633843Z","iopub.status.idle":"2024-05-27T09:20:54.647403Z","shell.execute_reply.started":"2024-05-27T09:20:54.633813Z","shell.execute_reply":"2024-05-27T09:20:54.646379Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Upload files:","metadata":{}},{"cell_type":"markdown","source":"### Applying functions and concatenating the columns into a single dataframe","metadata":{}},{"cell_type":"code","source":"# Reading training data and applying desired data types to Train:\ntrain_basetable = pl.read_csv(train_csv + \"train_base.csv\")\ntrain_static = pl.concat(\n    [\n        pl.read_csv(train_csv + \"train_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(train_csv + \"train_static_0_1.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\ntrain_static_cb = pl.read_csv(train_csv + \"train_static_cb_0.csv\").pipe(set_table_dtypes)\ntrain_person_1 = pl.read_csv(train_csv + \"train_person_1.csv\").pipe(set_table_dtypes) \ntrain_credit_bureau_b_2 = pl.read_csv(train_csv + \"train_credit_bureau_b_2.csv\").pipe(set_table_dtypes)\n\n# Reading test data and applying desired data types for Test:\ntest_basetable = pl.read_csv(test_csv + \"test_base.csv\")\ntest_static = pl.concat(\n    [\n        pl.read_csv(test_csv + \"test_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(test_csv + \"test_static_0_1.csv\").pipe(set_table_dtypes),\n        pl.read_csv(test_csv + \"test_static_0_2.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\ntest_static_cb = pl.read_csv(test_csv + \"test_static_cb_0.csv\").pipe(set_table_dtypes)\ntest_person_1 = pl.read_csv(test_csv + \"test_person_1.csv\").pipe(set_table_dtypes) \ntest_credit_bureau_b_2 = pl.read_csv(test_csv + \"test_credit_bureau_b_2.csv\").pipe(set_table_dtypes) ","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:20:54.648784Z","iopub.execute_input":"2024-05-27T09:20:54.649135Z","iopub.status.idle":"2024-05-27T09:21:35.358649Z","shell.execute_reply.started":"2024-05-27T09:20:54.649106Z","shell.execute_reply":"2024-05-27T09:21:35.357616Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Feature Engineering","metadata":{}},{"cell_type":"code","source":"# Feature Engineering\n# Creating new features using column aggregation and selection\ntrain_person_1_feats_1 = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\ntrain_person_1_feats_2 = train_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\ntrain_credit_bureau_b_2_feats = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n\n\n# Make sure the columns exist in the original DataFrame\nif 'pmts_pmtsoverdue_635A' in train_credit_bureau_b_2.columns and 'pmts_dpdvalue_108P' in train_credit_bureau_b_2.columns:\n    train_credit_bureau_b_2_feats_with_additional = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n        pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n        (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\"),\n        # New features\n        pl.col(\"pmts_pmtsoverdue_635A\").sum().alias(\"total_pmts_pmtsoverdue_635A\"),\n        pl.col(\"pmts_dpdvalue_108P\").sum().alias(\"total_pmts_dpdvalue_108P\"),\n    )\nelse:\n    print(\"Las columnas 'pmts_pmtsoverdue_635A' y/o 'pmts_dpdvalue_108P' no están presentes en train_credit_bureau_b_2\")\n\n# Create new additional features for train_person_1\ntrain_person_1_feats_1_with_additional = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").min().alias(\"mainoccupationinc_384A_min\"),\n    pl.col(\"incometype_1044T\").count().alias(\"num_incometype_1044T\"),\n    pl.col(\"incometype_1044T\").n_unique().alias(\"num_unique_incometype_1044T\"),\n)\n\ntrain_person_1_feats_2_with_additional = train_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"}).group_by(\"case_id\").agg(\n    pl.col(\"person_housetype\").n_unique().alias(\"num_unique_person_housetype\"),\n    pl.col(\"person_housetype\").count().alias(\"num_person_housetype_records\"),\n)\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:35.360225Z","iopub.execute_input":"2024-05-27T09:21:35.360697Z","iopub.status.idle":"2024-05-27T09:21:39.164892Z","shell.execute_reply.started":"2024-05-27T09:21:35.360653Z","shell.execute_reply":"2024-05-27T09:21:39.163410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Selection features in data:","metadata":{}},{"cell_type":"code","source":"# Selecting specific columns from static and credit tables\nselected_static_cols = []\nfor col in train_static.columns:\n    if col[-1] in (\"A\", \"M\"):\n        selected_static_cols.append(col)\n\nselected_static_cb_cols = []\nfor col in train_static_cb.columns:\n    if col[-1] in (\"A\", \"M\"):\n        selected_static_cb_cols.append(col)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:39.166507Z","iopub.execute_input":"2024-05-27T09:21:39.166956Z","iopub.status.idle":"2024-05-27T09:21:39.174858Z","shell.execute_reply.started":"2024-05-27T09:21:39.166915Z","shell.execute_reply":"2024-05-27T09:21:39.173453Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Data union:","metadata":{}},{"cell_type":"code","source":"# Union of all tables\ndata = train_basetable.join(\n    train_static.select([\"case_id\"]+selected_static_cols), how=\"left\", on=\"case_id\"\n).join(\n    train_static_cb.select([\"case_id\"]+selected_static_cb_cols), how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(\n    train_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n)\n\ntest_person_1_feats_1 = test_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\ntest_person_1_feats_2 = test_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\ntest_credit_bureau_b_2_feats = test_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:39.176643Z","iopub.execute_input":"2024-05-27T09:21:39.177104Z","iopub.status.idle":"2024-05-27T09:21:40.544178Z","shell.execute_reply.started":"2024-05-27T09:21:39.177064Z","shell.execute_reply":"2024-05-27T09:21:40.542937Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Concatenate the form:","metadata":{}},{"cell_type":"code","source":"# Concatenate submission form data:\ndata_submission = test_basetable.join(\n    test_static.select([\"case_id\"] + selected_static_cols), how=\"left\", on=\"case_id\"\n    # Join test table with selected columns of static data\n).join(\n    test_static_cb.select([\"case_id\"] + selected_static_cb_cols), how=\"left\", on=\"case_id\"\n    # Join with selected columns of encoded static data\n).join(\n    test_person_1_feats_1, how=\"left\", on=\"case_id\"\n    # Match with the first characteristics of person 1\n).join(\n    test_person_1_feats_2, how=\"left\", on=\"case_id\"\n    # Match with the second characteristics of person 1\n).join(\n    test_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n    # Join with credit bureau features\n)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:40.545784Z","iopub.execute_input":"2024-05-27T09:21:40.546152Z","iopub.status.idle":"2024-05-27T09:21:40.557182Z","shell.execute_reply.started":"2024-05-27T09:21:40.546120Z","shell.execute_reply":"2024-05-27T09:21:40.555846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create features importances, and add to DataFrame:","metadata":{}},{"cell_type":"code","source":"# Define the importance threshold\nthreshold = 0.01\n\n# Reading feature definitions from a CSV file\nfeature_definitions = pd.read_csv(ft_importance_path)\nfeature_definitions = feature_definitions[['Variable', 'Importance_Number']]\n# Filter the most important features\nimportant_features = feature_definitions[feature_definitions['Importance_Number'] > threshold]['Variable'].tolist()\n\n# Make sure all important features are in string format\nimportant_features = [str(feature) for feature in important_features]\nprint(important_features)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:40.565683Z","iopub.execute_input":"2024-05-27T09:21:40.566087Z","iopub.status.idle":"2024-05-27T09:21:40.584038Z","shell.execute_reply.started":"2024-05-27T09:21:40.566053Z","shell.execute_reply":"2024-05-27T09:21:40.582588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Filter important features in the data set","metadata":{}},{"cell_type":"code","source":"# Check important features in the DataFrame\nexisting_important_features = [feature for feature in important_features if feature in data.columns]\n\n# Filter important features in the data set\ndata_filtered = data.select(pl.col('case_id'), *[pl.col(feature) for feature in existing_important_features])\ndata_submission_filtered = data_submission.select(pl.col('case_id'), *[pl.col(feature) for feature in existing_important_features])","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:40.585539Z","iopub.execute_input":"2024-05-27T09:21:40.585910Z","iopub.status.idle":"2024-05-27T09:21:40.598059Z","shell.execute_reply.started":"2024-05-27T09:21:40.585876Z","shell.execute_reply":"2024-05-27T09:21:40.596631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission_filtered","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:40.599558Z","iopub.execute_input":"2024-05-27T09:21:40.599923Z","iopub.status.idle":"2024-05-27T09:21:40.634026Z","shell.execute_reply.started":"2024-05-27T09:21:40.599891Z","shell.execute_reply":"2024-05-27T09:21:40.632668Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Add Feature: Number of Records per User","metadata":{}},{"cell_type":"code","source":"# Count frequency of important features by case_id\ndata_counts = data_filtered.group_by('case_id').len()\ndata_submission_counts = data_submission_filtered.group_by('case_id').len()\n# Generate aggregation list dynamically\nagg_list = [pl.count(col).alias(f'{col}_count') for col in data_filtered.columns if col != 'case_id']\n\n# Count frequency of important features by case_id\ndata_counts = data_filtered.group_by('case_id').agg(agg_list)\ndata_submission_counts = data_submission_filtered.group_by('case_id').agg(agg_list)\n\nprint(data_counts)\ndisplay(data_submission_counts)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:40.635455Z","iopub.execute_input":"2024-05-27T09:21:40.635929Z","iopub.status.idle":"2024-05-27T09:21:41.130497Z","shell.execute_reply.started":"2024-05-27T09:21:40.635886Z","shell.execute_reply":"2024-05-27T09:21:41.129145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Create predictions:","metadata":{}},{"cell_type":"code","source":"# Get the unique case ids and mix them\ncase_ids = data_counts[\"case_id\"].unique().shuffle(seed=1)\n\n# Split case identifiers into training and test sets\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\n# Split test set into validation and test sets\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\n# Select columns for prediction\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\n# Print the columns selected for prediction\nprint(cols_pred)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:41.132035Z","iopub.execute_input":"2024-05-27T09:21:41.132498Z","iopub.status.idle":"2024-05-27T09:21:41.417558Z","shell.execute_reply.started":"2024-05-27T09:21:41.132457Z","shell.execute_reply":"2024-05-27T09:21:41.416231Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Reconvert data from Polar to Pandas:","metadata":{}},{"cell_type":"code","source":"# Defines a function to convert data from Polars to Pandas\ndef from_polars_to_pandas(case_ids: pl.DataFrame) -> pl.DataFrame:\n    return (\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[[\"case_id\", \"WEEK_NUM\", \"target\"]].to_pandas(),\n        # Filter and convert 'case_id', 'WEEK_NUM' and 'target' columns to Pandas DataFrame\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[cols_pred].to_pandas(),\n        # Filter and convert columns selected for prediction to Pandas DataFrame\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[\"target\"].to_pandas()\n        # Filter and convert 'target' column to Pandas Series\n    )","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:41.418997Z","iopub.execute_input":"2024-05-27T09:21:41.419366Z","iopub.status.idle":"2024-05-27T09:21:41.426581Z","shell.execute_reply.started":"2024-05-27T09:21:41.419296Z","shell.execute_reply":"2024-05-27T09:21:41.425394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Apply function and create training, validation and test data:","metadata":{}},{"cell_type":"code","source":"# Convert training, validation and testing data from Polars to Pandas\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\n# Convert the strings to the training, validation, and test data sets\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n# Print the shapes (dimensions) of the training, validation, and test data sets\nprint(f\"Train: {X_train.shape}\")\nprint(f\"Valid: {X_valid.shape}\")\nprint(f\"Test: {X_test.shape}\")\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:41.428251Z","iopub.execute_input":"2024-05-27T09:21:41.428726Z","iopub.status.idle":"2024-05-27T09:21:58.483953Z","shell.execute_reply.started":"2024-05-27T09:21:41.428684Z","shell.execute_reply":"2024-05-27T09:21:58.482675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Creating the LightGBM Model","metadata":{}},{"cell_type":"code","source":"# Create the training data set for LightGBM\nlgb_train = lgb.Dataset(X_train, label=y_train)\n# Create the validation data set for LightGBM, referencing the training set\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n\n# Define LightGBM model parameters\nparams = {\n    \"boosting_type\": \"gbdt\",  # Boosting type (Gradient Boosting Decision Tree)\n    \"objective\": \"binary\",  # Task objective (binary classification)\n    \"metric\": \"auc\",  # Evaluation Metric (Area Under the Curve)\n    \"max_depth\": 3,  # Maximum tree depth\n    \"num_leaves\": 32,  # Maximum number of leaves on each tree\n    \"learning_rate\": 0.07,  # Learning rate\n    \"feature_fraction\": 0.9,  # Fraction of features to consider in each iteration\n    \"bagging_fraction\": 0.8,  # Fraction of data to use in each iteration\n    \"bagging_freq\": 5,  # Bagging frequency (every 5 iterations)\n    \"verbose\": -1,  # Verbosity level (-1 to not show messages)\n}\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:58.485180Z","iopub.execute_input":"2024-05-27T09:21:58.485542Z","iopub.status.idle":"2024-05-27T09:21:58.493217Z","shell.execute_reply.started":"2024-05-27T09:21:58.485509Z","shell.execute_reply":"2024-05-27T09:21:58.492023Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Train the LightGBM model\ngbm = lgb.train(\n    params,  # Model parameters\n    lgb_train,  # Training data set\n    num_boost_round=1000,  # Number of trees to train\n    valid_sets=lgb_valid,  # Validation data set\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]  \n    # Callbacks to record evaluation every 50 iterations and stop training early if there is no improvement in 10 iterations \n)\n\n# Print the best iteration and the corresponding AUC\nprint(f\"Best iteration: {gbm.best_iteration}\")\nprint(f\"Best AUC: {gbm.best_score['valid_0']['auc']}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:21:58.494671Z","iopub.execute_input":"2024-05-27T09:21:58.495071Z","iopub.status.idle":"2024-05-27T09:23:34.720552Z","shell.execute_reply.started":"2024-05-27T09:21:58.495039Z","shell.execute_reply":"2024-05-27T09:23:34.719199Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Evaluation of the LightGBM Model","metadata":{}},{"cell_type":"code","source":"# Iterate over data sets and calculate predictions\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n\n# Calculates and displays AUC scores\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:34.722074Z","iopub.execute_input":"2024-05-27T09:23:34.722489Z","iopub.status.idle":"2024-05-27T09:23:58.354732Z","shell.execute_reply.started":"2024-05-27T09:23:34.722454Z","shell.execute_reply":"2024-05-27T09:23:58.353415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Evaluation through the gini_stability algorithm:","metadata":{}},{"cell_type":"code","source":"def gini_stability(base, w_fallingrate=88.0, w_resstd=-0.5):\n    # Calculates the Gini index in each week and saves it in a list\n    gini_in_time = base.loc[:, [\"WEEK_NUM\", \"target\", \"score\"]]\\\n        .sort_values(\"WEEK_NUM\")\\\n        .groupby(\"WEEK_NUM\")[[\"target\", \"score\"]]\\\n        .apply(lambda x: 2*roc_auc_score(x[\"target\"], x[\"score\"])-1).tolist()\n    \n    # Fit a straight line to the Gini values ​​over time\n    x = np.arange(len(gini_in_time))\n    y = gini_in_time\n    a, b = np.polyfit(x, y, 1)  # Fits a straight line (a*x + b) to the data\n    y_hat = a*x + b  # Fitted straight line values\n    residuals = y - y_hat  # Linear fit residuals\n    res_std = np.std(residuals)  # Standard deviation of residuals\n    avg_gini = np.mean(gini_in_time)  # Average Gini over time\n    \n    # Calculates the stability score using weights given to the decay rate and standard deviation of the residuals\n    return avg_gini + w_fallingrate * min(0, a) + w_resstd * res_std","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:58.356146Z","iopub.execute_input":"2024-05-27T09:23:58.356606Z","iopub.status.idle":"2024-05-27T09:23:58.369937Z","shell.execute_reply.started":"2024-05-27T09:23:58.356565Z","shell.execute_reply":"2024-05-27T09:23:58.368779Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"#### Calculate and print stability score:","metadata":{}},{"cell_type":"code","source":"# Calculates the stability score for the training, validation and test sets\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\n# Print the calculated stability scores\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}') ","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:58.371384Z","iopub.execute_input":"2024-05-27T09:23:58.371739Z","iopub.status.idle":"2024-05-27T09:23:59.496873Z","shell.execute_reply.started":"2024-05-27T09:23:58.371709Z","shell.execute_reply":"2024-05-27T09:23:59.495726Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Create our Dataframe for sending:","metadata":{}},{"cell_type":"code","source":"X_submission = data_submission[cols_pred].to_pandas()\nX_submission = convert_strings(X_submission)\ncategorical_cols = X_train.select_dtypes(include=['category']).columns\n\nfor col in categorical_cols:\n    train_categories = set(X_train[col].cat.categories)\n    submission_categories = set(X_submission[col].cat.categories)\n    new_categories = submission_categories - train_categories\n    X_submission.loc[X_submission[col].isin(new_categories), col] = \"Unknown\"\n    new_dtype = pd.CategoricalDtype(categories=train_categories, ordered=True)\n    X_train[col] = X_train[col].astype(new_dtype)\n    X_submission[col] = X_submission[col].astype(new_dtype)\n\ny_submission_pred = gbm.predict(X_submission, num_iteration=gbm.best_iteration)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:59.498253Z","iopub.execute_input":"2024-05-27T09:23:59.498643Z","iopub.status.idle":"2024-05-27T09:23:59.620892Z","shell.execute_reply.started":"2024-05-27T09:23:59.498610Z","shell.execute_reply":"2024-05-27T09:23:59.619434Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = pd.DataFrame({\n    \"case_id\": data_submission[\"case_id\"].to_numpy(),\n    \"score\": y_submission_pred\n}).set_index('case_id')\nsubmission.to_csv(\"./submission.csv\")","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:59.622444Z","iopub.execute_input":"2024-05-27T09:23:59.622817Z","iopub.status.idle":"2024-05-27T09:23:59.633822Z","shell.execute_reply.started":"2024-05-27T09:23:59.622782Z","shell.execute_reply":"2024-05-27T09:23:59.632580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"display(submission)\nprint(submission.shape)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:59.635097Z","iopub.execute_input":"2024-05-27T09:23:59.635477Z","iopub.status.idle":"2024-05-27T09:23:59.649371Z","shell.execute_reply.started":"2024-05-27T09:23:59.635439Z","shell.execute_reply":"2024-05-27T09:23:59.648289Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample = pd.read_csv(sample)\ndisplay(sample)\nprint(sample.shape)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:59.650819Z","iopub.execute_input":"2024-05-27T09:23:59.651230Z","iopub.status.idle":"2024-05-27T09:23:59.673807Z","shell.execute_reply.started":"2024-05-27T09:23:59.651192Z","shell.execute_reply":"2024-05-27T09:23:59.672760Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Apply algorithm to Parquet files:","metadata":{}},{"cell_type":"code","source":"def set_table_dtypesPq(df: pl.DataFrame) -> pl.DataFrame:\n    for col in df.columns:\n        if col.endswith(\"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n        elif col.endswith(\"D\"):\n            df = df.with_columns(pl.col(col).cast(pl.String).alias(col))  # Convertir a String\n        elif col.endswith(\"P\"):\n            df = df.with_columns(pl.col(col).cast(pl.Int32).alias(col))\n        elif col.endswith(\"T\") or col.endswith(\"L\"):\n            df = df.with_columns(pl.col(col).cast(pl.String).alias(col))\n        elif col in [\"case_id\", \"WEEK_NUM\", \"MONTH\", \"num_group1\", \"num_group2\"]:\n            pass\n        elif col == \"target\":\n            df = df.with_columns(pl.col(col).cast(pl.Boolean).alias(col))\n    return df\n\ndef convert_stringsPq(df: pd.DataFrame) -> pd.DataFrame:\n    for col in df.columns:\n        if df[col].dtype.name in ['object', 'string']:\n            df[col] = df[col].fillna(\"Unknown\").str.strip().astype(\"string\").astype('category')\n            current_categories = df[col].cat.categories\n            if \"Unknown\" not in current_categories:\n                new_categories = current_categories.to_list() + [\"Unknown\"]\n            else:\n                new_categories = current_categories.to_list()\n            new_dtype = pd.CategoricalDtype(categories=new_categories, ordered=True)\n            df[col] = df[col].astype(new_dtype)\n    return df\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:59.675041Z","iopub.execute_input":"2024-05-27T09:23:59.675487Z","iopub.status.idle":"2024-05-27T09:23:59.691103Z","shell.execute_reply.started":"2024-05-27T09:23:59.675446Z","shell.execute_reply":"2024-05-27T09:23:59.689773Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Load training data\ntrain_base = pl.read_parquet(train_pq + \"train_base.parquet\")\ntrain_static = pl.concat([\n    pl.read_parquet(train_pq + \"train_static_0_0.parquet\").pipe(set_table_dtypesPq),\n    pl.read_parquet(train_pq + \"train_static_0_1.parquet\").pipe(set_table_dtypesPq)\n], how=\"vertical\")\ntrain_static_cb = pl.read_parquet(train_pq + \"train_static_cb_0.parquet\").pipe(set_table_dtypesPq)\ntrain_person_1 = pl.read_parquet(train_pq + \"train_person_1.parquet\").pipe(set_table_dtypesPq) \ntrain_credit_bureau_b_2 = pl.read_parquet(train_pq + \"train_credit_bureau_b_2.parquet\").pipe(set_table_dtypesPq)\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:23:59.692591Z","iopub.execute_input":"2024-05-27T09:23:59.693016Z","iopub.status.idle":"2024-05-27T09:24:20.912692Z","shell.execute_reply.started":"2024-05-27T09:23:59.692976Z","shell.execute_reply":"2024-05-27T09:24:20.911582Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Reading test data\ntest_base = pl.read_parquet(test_pq + \"test_base.parquet\")\ntest_static = pl.concat(\n    [\n        pl.read_parquet(test_pq + \"test_static_0_0.parquet\").pipe(set_table_dtypesPq),\n        pl.read_parquet(test_pq + \"test_static_0_1.parquet\").pipe(set_table_dtypesPq),\n        pl.read_parquet(test_pq + \"test_static_0_2.parquet\").pipe(set_table_dtypesPq),\n    ],\n    how=\"vertical_relaxed\",\n)\ntest_static_cb = pl.read_parquet(test_pq + \"test_static_cb_0.parquet\").pipe(set_table_dtypesPq)\ntest_person_1 = pl.read_parquet(test_pq + \"test_person_1.parquet\").pipe(set_table_dtypesPq) \ntest_credit_bureau_b_2 = pl.read_parquet(test_pq + \"test_credit_bureau_b_2.parquet\").pipe(set_table_dtypesPq) ","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:24:20.914092Z","iopub.execute_input":"2024-05-27T09:24:20.914557Z","iopub.status.idle":"2024-05-27T09:24:21.075664Z","shell.execute_reply.started":"2024-05-27T09:24:20.914517Z","shell.execute_reply":"2024-05-27T09:24:21.074417Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Feature Engineering parquet:","metadata":{}},{"cell_type":"code","source":"# Creating new features using column aggregation and selection\ntrain_person_1_feats_1 = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\ntrain_person_1_feats_2 = train_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\ntrain_credit_bureau_b_2_feats = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n\n# Make sure the columns exist in the original DataFrame\nif 'pmts_pmtsoverdue_635A' in train_credit_bureau_b_2.columns and 'pmts_dpdvalue_108P' in train_credit_bureau_b_2.columns:\n    train_credit_bureau_b_2_feats_with_additional = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n        pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n        (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\"),\n        # New Features\n        pl.col(\"pmts_pmtsoverdue_635A\").sum().alias(\"total_pmts_pmtsoverdue_635A\"),\n        pl.col(\"pmts_dpdvalue_108P\").sum().alias(\"total_pmts_dpdvalue_108P\"),\n    )\nelse:\n    print(\"Las columnas 'pmts_pmtsoverdue_635A' y/o 'pmts_dpdvalue_108P' no están presentes en train_credit_bureau_b_2\")\n\n# Create new additional features for train_person_1\ntrain_person_1_feats_1_with_additional = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").min().alias(\"mainoccupationinc_384A_min\"),\n    pl.col(\"incometype_1044T\").count().alias(\"num_incometype_1044T\"),\n    pl.col(\"incometype_1044T\").n_unique().alias(\"num_unique_incometype_1044T\"),\n)\n\ntrain_person_1_feats_2_with_additional = train_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"}).group_by(\"case_id\").agg(\n    pl.col(\"person_housetype\").n_unique().alias(\"num_unique_person_housetype\"),\n    pl.col(\"person_housetype\").count().alias(\"num_person_housetype_records\"),\n)\n\n# Selecting specific columns from static and credit tables\nselected_static_cols = []\nfor col in train_static.columns:\n    if col[-1] in (\"A\", \"M\"):\n        selected_static_cols.append(col)\n\nselected_static_cb_cols = []\nfor col in train_static_cb.columns:\n    if col[-1] in (\"A\", \"M\"):\n        selected_static_cb_cols.append(col)\n# Union of all tables\ndataPq = train_base.join(\n    train_static.select([\"case_id\"]+selected_static_cols), how=\"left\", on=\"case_id\"\n).join(\n    train_static_cb.select([\"case_id\"]+selected_static_cb_cols), how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(\n    train_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n)\n\ntest_person_1_feats_1 = test_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\ntest_person_1_feats_2 = test_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\ntest_credit_bureau_b_2_feats = test_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n\n# Concatenate submission form data:\ndata_submissionPq = test_base.join(\n    test_static.select([\"case_id\"] + selected_static_cols), how=\"left\", on=\"case_id\"\n    # Join test table with selected columns of static data\n).join(\n    test_static_cb.select([\"case_id\"] + selected_static_cb_cols), how=\"left\", on=\"case_id\"\n    # Join with selected columns of encoded static data\n).join(\n    test_person_1_feats_1, how=\"left\", on=\"case_id\"\n    # Match with the first characteristics of person 1\n).join(\n    test_person_1_feats_2, how=\"left\", on=\"case_id\"\n    # Match with the second characteristics of person 1\n).join(\n    test_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n    # Join with credit bureau features\n)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:24:21.077765Z","iopub.execute_input":"2024-05-27T09:24:21.078641Z","iopub.status.idle":"2024-05-27T09:24:24.089386Z","shell.execute_reply.started":"2024-05-27T09:24:21.078596Z","shell.execute_reply":"2024-05-27T09:24:24.088141Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Filter important features in Parquet dataset","metadata":{}},{"cell_type":"code","source":"# Check important features in the DataFrame\nexisting_important_featuresPq = [feature for feature in important_features if feature in dataPq.columns]\n\n# Filter important features in the data set\ndata_filteredPq = dataPq.select(pl.col('case_id'), *[pl.col(feature) for feature in existing_important_featuresPq])\ndata_submission_filteredPq = data_submissionPq.select(pl.col('case_id'), *[pl.col(feature) for feature in existing_important_features])\n\n# Generate aggregation list dynamically\nagg_list = [pl.count(col).alias(f'{col}_count') for col in data_filteredPq.columns if col != 'case_id']\n\n# Count frequency of important features by case_id\ndata_countsPq = data_filteredPq.group_by('case_id').agg(agg_list)\ndata_submission_countsPq = data_submission_filteredPq.group_by('case_id').agg(agg_list)\n\nprint(data_countsPq)\nprint(data_submission_countsPq)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:24:24.090823Z","iopub.execute_input":"2024-05-27T09:24:24.091178Z","iopub.status.idle":"2024-05-27T09:24:24.412368Z","shell.execute_reply.started":"2024-05-27T09:24:24.091148Z","shell.execute_reply":"2024-05-27T09:24:24.411425Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Get the unique case ids and mix them\ncase_ids = data_countsPq[\"case_id\"].unique().shuffle(seed=1)\n\n# Split case identifiers into training and test sets\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\n# Split test set into validation and test sets\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\n# Select columns for prediction\ncols_pred = []\nfor col in data.columns:\n    if col[-1].isupper() and col[:-1].islower():\n        cols_pred.append(col)\n\n# Print the columns selected for prediction\nprint(cols_pred)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:24:24.413560Z","iopub.execute_input":"2024-05-27T09:24:24.413880Z","iopub.status.idle":"2024-05-27T09:24:24.668636Z","shell.execute_reply.started":"2024-05-27T09:24:24.413852Z","shell.execute_reply":"2024-05-27T09:24:24.667481Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert training, validation and testing data from Polars to Pandas\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\n# Convert the strings to the training, validation, and test data sets\nfor df in [X_train, X_valid, X_test]:\n    df = convert_strings(df)\n\n# Print the shapes (dimensions) of the training, validation, and test data sets\nprint(f\"Train: {X_train.shape}\")\nprint(f\"Valid: {X_valid.shape}\")\nprint(f\"Test: {X_test.shape}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:24:24.670096Z","iopub.execute_input":"2024-05-27T09:24:24.670536Z","iopub.status.idle":"2024-05-27T09:24:40.841903Z","shell.execute_reply.started":"2024-05-27T09:24:24.670496Z","shell.execute_reply":"2024-05-27T09:24:40.840710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Create LightGBM Model for Parquet files:","metadata":{}},{"cell_type":"code","source":"# Create the training data set for LightGBM\nlgb_train = lgb.Dataset(X_train, label=y_train)\n# Create the validation data set for LightGBM, referencing the training set\nlgb_valid = lgb.Dataset(X_valid, label=y_valid, reference=lgb_train)\n# Define LightGBM model parameters\nparams = {\n    \"boosting_type\": \"gbdt\",\n    \"objective\": \"binary\",\n    \"metric\": \"auc\",\n    \"max_depth\": 3,\n    \"num_leaves\": 32,\n    \"learning_rate\": 0.07,\n    \"feature_fraction\": 0.9,\n    \"bagging_fraction\": 0.8,\n    \"bagging_freq\": 5,\n    \"verbose\": -1,\n}\n\n# Train the LightGBM model\ngbm = lgb.train(\n    params,  \n    lgb_train,\n    num_boost_round=1000,  \n    valid_sets=lgb_valid,\n    callbacks=[lgb.log_evaluation(50), lgb.early_stopping(10)]  \n)\n\n# Print the best iteration and the corresponding AUC\nprint(f\"Best iteration: {gbm.best_iteration}\")\nprint(f\"Best AUC: {gbm.best_score['valid_0']['auc']}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:24:40.843014Z","iopub.execute_input":"2024-05-27T09:24:40.843360Z","iopub.status.idle":"2024-05-27T09:26:15.871990Z","shell.execute_reply.started":"2024-05-27T09:24:40.843330Z","shell.execute_reply":"2024-05-27T09:26:15.870709Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Itera sobre los conjuntos de datos y calcula las predicciones\nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n    base[\"score\"] = y_pred\n    print(base.head())  # Verificar que 'score' se haya agregado correctamente\n# Calcula y muestra los AUC scores\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}')\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:26:15.873669Z","iopub.execute_input":"2024-05-27T09:26:15.874101Z","iopub.status.idle":"2024-05-27T09:26:40.581367Z","shell.execute_reply.started":"2024-05-27T09:26:15.874061Z","shell.execute_reply":"2024-05-27T09:26:40.580104Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculates the stability score for the training, validation and test sets\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\n# Print the calculated stability scores\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}') ","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:26:40.582440Z","iopub.execute_input":"2024-05-27T09:26:40.582762Z","iopub.status.idle":"2024-05-27T09:26:41.694420Z","shell.execute_reply.started":"2024-05-27T09:26:40.582733Z","shell.execute_reply":"2024-05-27T09:26:41.693329Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## **Create graphics and submission file from parquet files.**","metadata":{}},{"cell_type":"code","source":"X_submission = data_submission[cols_pred].to_pandas()\nX_submission = convert_strings(X_submission)\ncategorical_cols = X_train.select_dtypes(include=['category']).columns\n\nfor col in categorical_cols:\n    train_categories = set(X_train[col].cat.categories)\n    submission_categories = set(X_submission[col].cat.categories)\n    new_categories = submission_categories - train_categories\n    X_submission.loc[X_submission[col].isin(new_categories), col] = \"Unknown\"\n    new_dtype = pd.CategoricalDtype(categories=train_categories, ordered=True)\n    X_train[col] = X_train[col].astype(new_dtype)\n    X_submission[col] = X_submission[col].astype(new_dtype)\n\ny_submission_pred = gbm.predict(X_submission, num_iteration=gbm.best_iteration)\nsubmission = pd.DataFrame({\n    \"case_id\": data_submission[\"case_id\"].to_numpy(),\n    \"score\": y_submission_pred\n}).set_index('case_id')\nsubmission.to_csv(\"./submission.csv\")\ndisplay(submission)\nprint(submission.shape)","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:26:41.695600Z","iopub.execute_input":"2024-05-27T09:26:41.695907Z","iopub.status.idle":"2024-05-27T09:26:41.824101Z","shell.execute_reply.started":"2024-05-27T09:26:41.695880Z","shell.execute_reply":"2024-05-27T09:26:41.822985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Plot the distribution of the predictions and the target variable\nsns.kdeplot(base_valid['score'], label='Predictions')\nsns.kdeplot(base_valid['target'], label='Target Variable')\nplt.xlabel('Values')\nplt.ylabel('Density')\nplt.title('Distribution of Predictions vs. Target Variable')\nplt.legend()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:26:41.825660Z","iopub.execute_input":"2024-05-27T09:26:41.826097Z","iopub.status.idle":"2024-05-27T09:26:45.332190Z","shell.execute_reply.started":"2024-05-27T09:26:41.826056Z","shell.execute_reply":"2024-05-27T09:26:45.331183Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Graph a correlation matrix\ncorrelation_matrix = base_valid[['target', 'score']].corr()\nsns.heatmap(correlation_matrix, annot=True, cmap='coolwarm', fmt=\".2f\")\nplt.title('Correlation Matrix between Predictions and Target Variable')\nplt.show()\n","metadata":{"execution":{"iopub.status.busy":"2024-05-27T09:26:45.333419Z","iopub.execute_input":"2024-05-27T09:26:45.333725Z","iopub.status.idle":"2024-05-27T09:26:45.665665Z","shell.execute_reply.started":"2024-05-27T09:26:45.333698Z","shell.execute_reply":"2024-05-27T09:26:45.664509Z"},"trusted":true},"execution_count":null,"outputs":[]}]}