📊 **Other Related Variables**:\n \n📈 **riskassesment_302T**: Estimated probability of client default.\n \n📈 **overdueamount_659A**: Amount overdue for active contract.\n \n📈 **totaloutstanddebtvalue_39A**: Total outstanding debt for active contracts.\n \n📈 **numactivecreds_622L**: Number of active credits.","metadata":{}},{"cell_type":"code","source":"# Path to the CSV file with feature definitions\nfeature_definitions_path = \"/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv\"\n\n# List of relevant features\nrelevant_features = [\n 'actualdpd_943P', # Days of delay in payment of previous contracts\n 'dpd_550P', # Days of delay for active loans with guarantee\n 'dpd_733P', # Days of delay for terminated secured loans\n 'maxdpdlast24m_143P', # Maximum days of delay in the last 24 months\n 'amount_416A', # Represents the amount deposited by the customer\n 'amount_1115A', # Quantity of credit for active contract\n 'credamount_770A', # Amount of loan or credit card limit\n 'totaldebt_9A', # Total amount of debt\n 'currdebt_22A', # Current debt amount of the client\n 'collater_valueofguarantee_1124L', # Value of guarantee for active contract\n #'collaterals_typeofguarante_669M', # Type of guarantee for the active contract\n 'maininc_215A', # Main income of the client\n 'credquantity_984L', # Number of closed credits in the credit bureau\n 'numactivecreds_622L', # Number of active credits\n 'education_1103M', # Education level of the client\n 'maritalst_385M', # Marital status of the client\n 'riskassesment_302T', # Risk assessment\n 'overdueamount_659A', # Amount overdue for active contract\n 'totaloutstanddebtvalue_39A', # Total outstanding debt value\n 'empl_employedtotal_800L', # Total employment time\n 'contractenddate_991D', # contract end date associated with a specific credit case\n 'openingdate_313D', # indicates the date the client opened a deposit account\n 'contractdate_551D', # Contract date of the active contract\n 'familystate_447L', # Family status of the client\n 'employmentstatus_411L', # Employment status of the client\n 'housetype_905L', # Type of housing of the client\n 'incomelevel_513L' # Income level of the client\n]\n\n# Load and print information of the relevant features\nwith open(feature_definitions_path, newline='') as csvfile:\n reader = csv.reader(csvfile)\n for row in reader:\n # Check if the feature is in the list of relevant features\n if row[0] in relevant_features:\n print(row)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:26:18.010039Z","iopub.execute_input":"2024-05-01T19:26:18.010517Z","iopub.status.idle":"2024-05-01T19:26:18.020597Z","shell.execute_reply.started":"2024-05-01T19:26:18.010478Z","shell.execute_reply":"2024-05-01T19:26:18.019736Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Identification of Files Containing Specific Variables","metadata":{}},{"cell_type":"code","source":"# Path to the 'train' directory\ndata_path = '/kaggle/input/home-credit-credit-risk-model-stability/'\n\n# Variables to be searched for\nvariables_to_find = [\n 'actualdpd_943P', 'dpd_550P', 'dpd_733P', 'maxdpdlast24m_143P', \n 'amount_1115A', 'credamount_770A', 'totaldebt_9A', 'currdebt_22A', \n 'collater_valueofguarantee_1124L', #'collaterals_typeofguarante_669M', \n 'maininc_215A', 'credquantity_984L', 'numactivecreds_622L', \n 'education_1103M', 'maritalst_385M', 'riskassesment_302T', \n 'overdueamount_659A', 'totaloutstanddebtvalue_39A', 'empl_employedtotal_800L',\n 'contractenddate_991D', 'amount_416A', 'openingdate_313D', 'contractdate_551D',\n 'familystate_447L', 'employmentstatus_411L', 'housetype_905L', 'incomelevel_513L'\n]\n\n# Dictionary to store the files containing each variable\nfiles_with_variables = {var: [] for var in variables_to_find}\n\n# Traverse all CSV files in the directory and subdirectories\nfor dirname, _, filenames in os.walk(data_path):\n for filename in filenames:\n if filename.endswith('.csv'):\n file_path = os.path.join(dirname, filename)\n \n try:\n # Load a small sample of the file for verification\n df_sample = pd.read_csv(file_path, nrows=5)\n \n # Check if the desired variables are present\n for var in variables_to_find:\n if var in df_sample.columns:\n files_with_variables[var].append(file_path)\n except Exception as e:\n print(f\"Error while reading the file: {file_path}, Error: {e}\")\n\n# Print the paths of the files containing the variables\nfor var, files in files_with_variables.items():\n print(f\"Files containing '{var}':\")\n for file in files:\n print(file)\n print(\"\\n\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:26:18.023517Z","iopub.execute_input":"2024-05-01T19:26:18.023940Z","iopub.status.idle":"2024-05-01T19:26:18.703557Z","shell.execute_reply.started":"2024-05-01T19:26:18.023911Z","shell.execute_reply":"2024-05-01T19:26:18.702661Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Data Treatment with Different Depths: Depth=1 and Depth=2","metadata":{}},{"cell_type":"code","source":"# Path to the 'train' directory\ndata_path = '/kaggle/input/home-credit-credit-risk-model-stability/'\n\n# Variables to be searched\nvariables_to_find = [\n 'actualdpd_943P', 'dpd_550P', 'dpd_733P', 'maxdpdlast24m_143P', \n 'amount_1115A', 'credamount_770A', 'totaldebt_9A', 'currdebt_22A', \n 'collater_valueofguarantee_1124L', #'collaterals_typeofguarante_669M', \n 'maininc_215A', 'credquantity_984L', 'numactivecreds_622L', \n 'education_1103M', 'maritalst_385M', 'riskassesment_302T', \n 'overdueamount_659A', 'totaloutstanddebtvalue_39A', 'empl_employedtotal_800L',\n 'contractenddate_991D', 'amount_416A', 'openingdate_313D', 'contractdate_551D',\n 'familystate_447L', 'employmentstatus_411L', 'housetype_905L', 'incomelevel_513L'\n]\n\n# Dictionaries to store the files containing each variable, separated by depth\nfiles_with_variables_depth1 = {var: [] for var in variables_to_find}\nfiles_with_variables_depth2 = {var: [] for var in variables_to_find}\n\n# Patterns to identify Depth=1 and Depth=2 files\ndepth1_patterns = [\"_a_1\", \"_b_1\", \"_1\"]\ndepth2_patterns = [\"_a_2\", \"_b_2\"]\n\n# Traverse through all CSV files in the directory and subdirectories\nfor dirname, _, filenames in os.walk(data_path):\n for filename in filenames:\n if filename.endswith('.csv'):\n file_path = os.path.join(dirname, filename)\n\n try:\n # Load a small sample of the file for verification\n df_sample = pd.read_csv(file_path, nrows=5)\n\n # Check the depth based on the file name\n if \"_0\" not in filename: # Exclude files starting with \"_0\"\n if any(pattern in filename for pattern in depth1_patterns):\n target_dict = files_with_variables_depth1\n elif any(pattern in filename for pattern in depth2_patterns):\n target_dict = files_with_variables_depth2\n else:\n continue # Skip files that don't match Depth=1 or Depth=2 patterns\n\n # Check if the desired variables are present\n for var in variables_to_find:\n if var in df_sample.columns:\n target_dict[var].append(file_path)\n except Exception as e:\n print(f\"Error reading file: {file_path}, Error: {e}\")\n\n# Print the paths of files containing the variables, separated by depth\nfor depth, files_dict in [('Depth=1', files_with_variables_depth1), ('Depth=2', files_with_variables_depth2)]:\n print(f\"--- {depth} ---\")\n for var, files in files_dict.items():\n print(f\"Files containing '{var}' ({depth}):\")\n for file in files:\n print(file)\n print(\"\\n\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:26:18.704847Z","iopub.execute_input":"2024-05-01T19:26:18.705187Z","iopub.status.idle":"2024-05-01T19:26:19.040989Z","shell.execute_reply.started":"2024-05-01T19:26:18.705160Z","shell.execute_reply":"2024-05-01T19:26:19.040068Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Preliminary Data Quality Analysis","metadata":{}},{"cell_type":"code","source":"import os\nimport pandas as pd\nfrom concurrent.futures import ThreadPoolExecutor\n\n# Path to the 'train' directory\ndata_path = '/kaggle/input/home-credit-credit-risk-model-stability/'\n\n# Variables to be searched\nvariables_to_find = [\n 'actualdpd_943P', 'dpd_550P', 'dpd_733P', 'maxdpdlast24m_143P', \n 'amount_1115A', 'credamount_770A', 'totaldebt_9A', 'currdebt_22A', \n 'collater_valueofguarantee_1124L', 'collaterals_typeofguarante_669M', \n 'maininc_215A', 'credquantity_984L', 'numactivecreds_622L', \n 'education_1103M', 'maritalst_385M', 'riskassesment_302T', \n 'overdueamount_659A', 'totaloutstanddebtvalue_39A', 'empl_employedtotal_800L',\n 'contractenddate_991D', 'amount_416A', 'openingdate_313D', 'contractdate_551D',\n 'familystate_447L', 'employmentstatus_411L', 'housetype_905L', 'incomelevel_513L'\n]\n\n# Dictionaries to store the file paths by variable and depth\nfiles_by_variable_depth = {var: {'train_depth1': [], 'test_depth1': [], 'train_depth2': [], 'test_depth2': []} for var in variables_to_find}\n\n# Traversing through CSV files and storing paths\nfor dirname, _, filenames in os.walk(data_path):\n for filename in filenames:\n if filename.endswith('.csv'):\n file_path = os.path.join(dirname, filename)\n depth = 'depth2' if '_2' in filename else 'depth1'\n file_type = 'train' if 'train' in file_path else 'test'\n df_sample = pd.read_csv(file_path, nrows=1)\n for var in variables_to_find:\n if var in df_sample.columns:\n key = f\"{file_type}_{depth}\"\n files_by_variable_depth[var][key].append(file_path)\n\n# Function to load and analyze CSV files in batches\ndef analyze_and_load(files, variable, batch_size=30000):\n total_rows = 0\n nan_count = 0\n for file in files:\n for chunk in pd.read_csv(file, usecols=[variable], chunksize=batch_size, low_memory=False):\n total_rows += chunk.shape[0]\n nan_count += chunk[variable].isna().sum()\n return total_rows, nan_count\n\n# Wrapper para a função analyze_and_load\ndef analyze_and_load_wrapper(file_paths, variable, data_type):\n total_rows, nan_count = analyze_and_load(file_paths, variable)\n return (variable, data_type, {'total_rows': total_rows, 'nan_count': nan_count})\n\n# Função para análise paralela\ndef parallel_analysis(files_by_variable_depth):\n results = {}\n with ThreadPoolExecutor(max_workers=4) as executor:\n futures = []\n for var in variables_to_find:\n for data_type in ['train_depth1', 'test_depth1', 'train_depth2', 'test_depth2']:\n file_paths = files_by_variable_depth[var][data_type]\n if file_paths:\n futures.append(executor.submit(analyze_and_load_wrapper, file_paths, var, data_type))\n \n for future in futures:\n var, data_type, result = future.result()\n if var not in results:\n results[var] = {}\n results[var][data_type] = result\n return results\n\n# Preparando os arquivos para análise\nfiles_to_analyze = {var: {'train_depth1': [], 'test_depth1': [], 'train_depth2': [], 'test_depth2': []} for var in variables_to_find}\nfor var in variables_to_find:\n for data_type in files_by_variable_depth[var]:\n files_to_analyze[var][data_type] = files_by_variable_depth[var][data_type]\n\n# Executando a análise paralela\nanalysis_results = parallel_analysis(files_to_analyze)\n\n# Exibindo os resultados\nfor var, data_types in analysis_results.items():\n for data_type, result in data_types.items():\n total_rows = result['total_rows']\n nan_count = result['nan_count']\n nan_percentage = nan_count / total_rows * 100 if total_rows > 0 else 0\n print(f\"Results for {var} ({data_type}): Total rows = {total_rows}, NaNs = {nan_count}, NaN Percentage = {nan_percentage:.2f}%\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:26:19.042612Z","iopub.execute_input":"2024-05-01T19:26:19.042929Z","iopub.status.idle":"2024-05-01T19:34:44.562729Z","shell.execute_reply.started":"2024-05-01T19:26:19.042903Z","shell.execute_reply":"2024-05-01T19:34:44.561633Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Exibindo a estrutura de analysis_results\nfor var, results in analysis_results.items():\n print(f\"{var}: {results}\")\n\n# Verificando o tipo de 'results'\nfor var, results in analysis_results.items():\n for key, res in results.items():\n print(f\"Tipo de 'res' para a variável {var} e chave {key}: {type(res)}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:34:44.564193Z","iopub.execute_input":"2024-05-01T19:34:44.564523Z","iopub.status.idle":"2024-05-01T19:34:44.571824Z","shell.execute_reply.started":"2024-05-01T19:34:44.564495Z","shell.execute_reply":"2024-05-01T19:34:44.570793Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"warnings.filterwarnings('ignore', category=FutureWarning)\n\n# Preparando os dados para o gráfico\ndata_for_graph = []\n\nfor var, data_types in analysis_results.items():\n for data_type, result in data_types.items():\n total_rows = result['total_rows']\n nan_count = result['nan_count']\n nan_percentage = nan_count / total_rows * 100 if total_rows > 0 else 0\n data_for_graph.append({\n 'variable': var,\n 'data_type': data_type,\n 'total_rows': total_rows,\n 'nan_count': nan_count,\n 'nan_percentage': nan_percentage\n })\n\n# Convertendo para DataFrame\ndf_graph = pd.DataFrame(data_for_graph)\n\n# Criando o gráfico de barras\nfig = px.bar(df_graph, x='variable', y='nan_percentage', color='data_type',\n labels={'variable': 'Variables', 'nan_percentage': 'Percentage of Missing Values (%)', 'data_type': 'Data Type'},\n title='Percentage of Missing Values per Variable and Data Type')\n\n# Personalizando o gráfico\nfig.update_layout(barmode='group', xaxis={'categoryorder':'total descending'})\nfig.update_traces(texttemplate='%{y:.2f}%', textposition='outside')\n\n# Exibindo o gráfico\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:34:44.573244Z","iopub.execute_input":"2024-05-01T19:34:44.573539Z","iopub.status.idle":"2024-05-01T19:34:46.142212Z","shell.execute_reply.started":"2024-05-01T19:34:44.573514Z","shell.execute_reply":"2024-05-01T19:34:46.141306Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"warnings.filterwarnings('ignore', category=FutureWarning)\n\n# Preparando os dados para o gráfico\ndata_for_graph = []\n\nfor var, data_types in analysis_results.items():\n for data_type, result in data_types.items():\n total_rows = result['total_rows']\n nan_count = result['nan_count']\n nan_percentage = nan_count / total_rows * 100 if total_rows > 0 else 0\n data_for_graph.append({\n 'variable': var,\n 'data_type': data_type,\n 'total_rows': total_rows,\n 'nan_count': nan_count,\n 'nan_percentage': nan_percentage\n })\n\n# Filtrando as variáveis com mais de 10% de valores ausentes\nfiltered_data = [row for row in data_for_graph if row['nan_percentage'] > 10]\n\n# Convertendo para DataFrame\ndf_filtered_graph = pd.DataFrame(filtered_data)\n\n# Criando o gráfico de barras\nimport plotly.express as px\nfig = px.bar(df_filtered_graph, x='variable', y='nan_percentage', color='data_type',\n labels={'variable': 'Variables', 'nan_percentage': 'Percentage of Missing Values (%)', 'data_type': 'Data Type'},\n title='Variables with More than 10% Missing Values')\n\n# Personalizando o gráfico\nfig.update_layout(barmode='group', xaxis={'categoryorder':'total descending'})\nfig.update_traces(texttemplate='%{y:.2f}%', textposition='outside')\n\n# Exibindo o gráfico\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:34:46.143663Z","iopub.execute_input":"2024-05-01T19:34:46.144055Z","iopub.status.idle":"2024-05-01T19:34:46.241535Z","shell.execute_reply.started":"2024-05-01T19:34:46.144023Z","shell.execute_reply":"2024-05-01T19:34:46.240026Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"🛑 Alert: Features were removed due to too many empty rows in the data.
","metadata":{}},{"cell_type":"markdown","source":"## **Variables to be kept:**\n\n- **✔️ credamount_770A:** Has few missing data and is relevant for financial analysis.\n \n- **✔️ numactivecreds_622L:** Has complete data and can be useful for credit analysis.\n \n- **✔️ education_1103M** e **maritalst_385M:** These are demographic variables with complete data and can help understand the customer profile.\n \n- **✔️ actualdpd_943P:** Although it has some missing data, it can be crucial for default analysis.\n \n- **✔️ contractenddate_991D:** e openingdate_313D: Important for temporal analysis and understanding the duration and longevity of credit relationships.\n\n- **✔️ amount_416A:** Can help understand the relationship between saving and credit behavior of customers.\n \n- **✔️ openingdate_313D**: Stability and customer history. \n \n- **✔️ totaldebt_9A**: Total debt amount.\n \n- **✔️ contractdate_551D**: Contract date of the active contract.\n \n- **✔️ currdebt_22A**: Current debt amount of the client.\n \n## **Variables to be discarded:**\n\n- 🗑️ **collater_valueofguarantee_1124L** e **totaloutstanddebtvalue_39A:** Due to high missing data rates, they may not be reliable for modeling.\n \n- 🗑️ **Outras variáveis:** Including those with high rates of missing data or that do not align with the main objective of your analysis.","metadata":{"execution":{"iopub.status.busy":"2024-02-09T17:48:49.804097Z","iopub.execute_input":"2024-02-09T17:48:49.805946Z","iopub.status.idle":"2024-02-09T17:48:50.594753Z","shell.execute_reply.started":"2024-02-09T17:48:49.805894Z","shell.execute_reply":"2024-02-09T17:48:50.589375Z"}}},{"cell_type":"markdown","source":"# Variable Depth Data Pipeline","metadata":{}},{"cell_type":"code","source":"# Path to the 'train' directory\ndata_path = '/kaggle/input/home-credit-credit-risk-model-stability/'\n\n# Selected variables based on missing data analysis and relevance\nselected_variables = [\n 'credamount_770A', 'numactivecreds_622L', 'education_1103M', 'maritalst_385M', \n 'actualdpd_943P', 'contractenddate_991D', 'amount_416A', 'openingdate_313D', 'totaldebt_9A', \n 'contractdate_551D', 'currdebt_22A'\n]\n\n# Updating the dictionary to store file paths only for selected variables\nfiles_by_variable_depth = {var: {'train_depth1': [], 'test_depth1': [], 'train_depth2': [], 'test_depth2': []} for var in selected_variables}\n\n# Traversing through CSV files and storing paths for selected variables\nfor dirname, _, filenames in os.walk(data_path):\n for filename in filenames:\n if filename.endswith('.csv'):\n file_path = os.path.join(dirname, filename)\n depth = 'depth2' if '_2' in filename else 'depth1'\n file_type = 'train' if 'train' in file_path else 'test'\n df_sample = pd.read_csv(file_path, nrows=1)\n for var in selected_variables:\n if var in df_sample.columns:\n key = f\"{file_type}_{depth}\"\n files_by_variable_depth[var][key].append(file_path)\n del df_sample # Discard df_sample after use\n gc.collect() # Garbage collection to free up memory\n \ndef load_and_concatenate(files, variable, dtype, batch_size=50000):\n if not files:\n return pd.DataFrame() # Returns an empty DataFrame if no files are found\n df_list = []\n for file in files:\n for chunk in pd.read_csv(file, usecols=[variable], chunksize=batch_size):\n if dtype == 'categorical':\n chunk[variable] = chunk[variable].astype('category')\n elif dtype == 'date':\n chunk[variable] = pd.to_datetime(chunk[variable])\n else:\n chunk[variable] = chunk[variable].fillna(0).astype(dtype)\n df_list.append(chunk)\n gc.collect() # Garbage collection after each chunk\n return pd.concat(df_list, ignore_index=True)\n\n# Defining data types\ndata_types = {\n 'credamount_770A': 'float32',\n 'numactivecreds_622L': 'int16',\n 'actualdpd_943P': 'int32',\n 'education_1103M': 'categorical',\n 'maritalst_385M': 'categorical',\n 'contractenddate_991D': 'date', # Adjusted to 'date'\n 'amount_416A': 'float32',\n 'openingdate_313D': 'date', # Adjusted to 'date'\n 'totaldebt_9A': 'float32',\n 'contractdate_551D': 'date' # Added and adjusted to 'date'\n}\n\n# Initialization of the dictionary to store the DataFrames\ndataframes = {}\n\n# Batch processing with optimized data types\nfor var in selected_variables:\n dtype = data_types.get(var, 'float64')\n print(f\"Processing variable: {var}\")\n dataframes[var] = {}\n for key in files_by_variable_depth[var]:\n files = files_by_variable_depth[var][key]\n if files:\n df = load_and_concatenate(files, var, dtype)\n dataframes[var][key] = df\n print(f\"{key} Data - {var}:\")\n print(df.head())\n else:\n print(f\"No files found for {var} ({key}).\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:34:46.243326Z","iopub.execute_input":"2024-05-01T19:34:46.243637Z","iopub.status.idle":"2024-05-01T19:38:04.876697Z","shell.execute_reply.started":"2024-05-01T19:34:46.243613Z","shell.execute_reply":"2024-05-01T19:38:04.875846Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Analysis of Missing Values by Variable","metadata":{}},{"cell_type":"code","source":"# Function to calculate missing values\ndef missing_values_analysis(df):\n missing_count = df.isnull().sum() # Count of missing values\n missing_percentage = (missing_count / len(df)) * 100 # Percentage of missing values\n\n missing_df = pd.DataFrame({'Column': df.columns,\n 'Missing Count': missing_count,\n 'Missing Percentage': missing_percentage})\n\n # Filtering columns with at least one missing value\n missing_df = missing_df[missing_df['Missing Count'] > 0].sort_values('Missing Percentage', ascending=False)\n\n return missing_df\n\n# Applying missing values analysis for each relevant dataframe\nfor var in ['credamount_770A', 'numactivecreds_622L', 'education_1103M', 'maritalst_385M', 'actualdpd_943P', \n 'contractenddate_991D', 'amount_416A', 'openingdate_313D', 'contractdate_551D', 'currdebt_22A']:\n df = dataframes[var]['train_depth1'] # Adjust as necessary for train/test and depth1/depth2\n print(f\"Missing Values Analysis for {var}:\")\n print(missing_values_analysis(df), \"\\n\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:04.878040Z","iopub.execute_input":"2024-05-01T19:38:04.878833Z","iopub.status.idle":"2024-05-01T19:38:04.923289Z","shell.execute_reply.started":"2024-05-01T19:38:04.878798Z","shell.execute_reply":"2024-05-01T19:38:04.922397Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Visualization of missing values for selected variables","metadata":{}},{"cell_type":"code","source":"# Assuming you have the individual DataFrames\ncredamount_770A_df = dataframes['credamount_770A']['train_depth1']\nnumactivecreds_622L_df = dataframes['numactivecreds_622L']['train_depth1']\neducation_1103M_df = dataframes['education_1103M']['train_depth1']\nmaritalst_385M_df = dataframes['maritalst_385M']['train_depth1']\nactualdpd_943P_df = dataframes['actualdpd_943P']['train_depth1']\ncontractenddate_991D_df = dataframes['contractenddate_991D']['train_depth1']\namount_416A_df = dataframes['amount_416A']['train_depth1']\nopeningdate_313D_df = dataframes['openingdate_313D']['train_depth1']\ntotaldebt_9A_df = dataframes['totaldebt_9A']['train_depth1']\ncontractdate_551D_df = dataframes['contractdate_551D']['train_depth1']\ncurrdebt_22A_df = dataframes['currdebt_22A']['train_depth1']\n\n# Combine the DataFrames into one DataFrame\ncombined_df = pd.concat([credamount_770A_df, numactivecreds_622L_df, education_1103M_df, maritalst_385M_df, \n actualdpd_943P_df, contractenddate_991D_df, amount_416A_df, openingdate_313D_df,\n totaldebt_9A_df, contractdate_551D_df, currdebt_22A_df], axis=1)\n\n# Calculate the sum of missing values for all columns\nmissing_values_count = combined_df.isnull().sum()\n\n# Create a new DataFrame for the graph\nmissing_df = pd.DataFrame({\n 'Columns': missing_values_count.index,\n 'Total Missing Values': missing_values_count.values,\n 'Percentage of Missing Values': (missing_values_count / combined_df.shape[0] * 100).values\n})\n\n# Create the bar graph for missing values\nfig = px.bar(missing_df, x='Columns', y='Total Missing Values', text='Percentage of Missing Values')\n\n# Customize the title and colors\nfig.update_layout(title=\"Total Missing Values per Column\",\n xaxis_title=\"Columns\",\n yaxis_title=\"Total Missing Values\",\n bargap=0.1,\n plot_bgcolor=\"white\",\n paper_bgcolor=\"white\")\n\n# Display the graph\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:04.924600Z","iopub.execute_input":"2024-05-01T19:38:04.924974Z","iopub.status.idle":"2024-05-01T19:38:07.794308Z","shell.execute_reply.started":"2024-05-01T19:38:04.924943Z","shell.execute_reply":"2024-05-01T19:38:07.793383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Dataset Preparation and Understanding","metadata":{}},{"cell_type":"code","source":"combined_df","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:07.799480Z","iopub.execute_input":"2024-05-01T19:38:07.799751Z","iopub.status.idle":"2024-05-01T19:38:07.822453Z","shell.execute_reply.started":"2024-05-01T19:38:07.799729Z","shell.execute_reply":"2024-05-01T19:38:07.821431Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace 'None' with NaN\ncombined_df.replace('None', pd.NA, inplace=True)\n\n# Calculate the proportion of missing values per column\nproportion_missing = combined_df.isna().mean()\nprint(\"Proportion of Missing Values per Column:\")\nprint(proportion_missing)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:07.824652Z","iopub.execute_input":"2024-05-01T19:38:07.825349Z","iopub.status.idle":"2024-05-01T19:38:07.938326Z","shell.execute_reply.started":"2024-05-01T19:38:07.825321Z","shell.execute_reply":"2024-05-01T19:38:07.937337Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# First, let's check for columns with missing data\nmissing_data = combined_df.isna().sum()\nmissing_data = missing_data[missing_data > 0]\nprint(\"Columns with Missing Data:\")\nprint(missing_data)\n\n# Now, let's check which of these columns are numeric\nnumeric_columns_with_missing_data = combined_df[missing_data.index].select_dtypes(include=[np.number]).columns\nprint(\"\\nNumeric Columns with Missing Data:\")\nprint(numeric_columns_with_missing_data)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:07.939677Z","iopub.execute_input":"2024-05-01T19:38:07.940084Z","iopub.status.idle":"2024-05-01T19:38:08.852816Z","shell.execute_reply.started":"2024-05-01T19:38:07.940049Z","shell.execute_reply":"2024-05-01T19:38:08.851906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Iterating over each column and printing the unique values\nfor col in combined_df.columns:\n print(f\"Unique values in column '{col}':\")\n print(combined_df[col].unique())\n print(\"\\n\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:08.854652Z","iopub.execute_input":"2024-05-01T19:38:08.855384Z","iopub.status.idle":"2024-05-01T19:38:09.315846Z","shell.execute_reply.started":"2024-05-01T19:38:08.855350Z","shell.execute_reply":"2024-05-01T19:38:09.314884Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replacing NaN with the median\nmedian_value = combined_df['credamount_770A'].median()\ncombined_df['credamount_770A'] = combined_df['credamount_770A'].fillna(median_value)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:09.317101Z","iopub.execute_input":"2024-05-01T19:38:09.317415Z","iopub.status.idle":"2024-05-01T19:38:09.392423Z","shell.execute_reply.started":"2024-05-01T19:38:09.317389Z","shell.execute_reply":"2024-05-01T19:38:09.391626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replacing NaN with the median\nmedian_value = combined_df['actualdpd_943P'].median()\ncombined_df['actualdpd_943P'] = combined_df['actualdpd_943P'].fillna(median_value)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:09.393503Z","iopub.execute_input":"2024-05-01T19:38:09.393784Z","iopub.status.idle":"2024-05-01T19:38:09.503439Z","shell.execute_reply.started":"2024-05-01T19:38:09.393760Z","shell.execute_reply":"2024-05-01T19:38:09.502653Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Checking for outliers in numerical columns\nprint(\"Descriptive Statistics for Numerical Columns:\")\nprint(combined_df.describe())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:09.504702Z","iopub.execute_input":"2024-05-01T19:38:09.505086Z","iopub.status.idle":"2024-05-01T19:38:11.565104Z","shell.execute_reply.started":"2024-05-01T19:38:09.505053Z","shell.execute_reply":"2024-05-01T19:38:11.564119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Plotting histograms for numerical column\nfor col in combined_df.select_dtypes(include=[np.number]).columns:\n plt.figure(figsize=(8, 4))\n plt.hist(combined_df[col], bins=30, edgecolor='k', alpha=0.7)\n plt.title(f'Distribution of column {col}')\n plt.xlabel(col)\n plt.ylabel('Frequency')\n plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:11.566427Z","iopub.execute_input":"2024-05-01T19:38:11.567558Z","iopub.status.idle":"2024-05-01T19:38:13.487360Z","shell.execute_reply.started":"2024-05-01T19:38:11.567520Z","shell.execute_reply":"2024-05-01T19:38:13.486420Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Coding and Transformation of Variables","metadata":{}},{"cell_type":"code","source":"# Analyze patterns for 'contractenddate_991D'\nprint(\"Analysis of Patterns for 'contractenddate_991D':\")\nprint(combined_df[combined_df['contractenddate_991D'].isna()].describe(include='all'))","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:13.488732Z","iopub.execute_input":"2024-05-01T19:38:13.489329Z","iopub.status.idle":"2024-05-01T19:38:15.761384Z","shell.execute_reply.started":"2024-05-01T19:38:13.489295Z","shell.execute_reply":"2024-05-01T19:38:15.760337Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Treating 'None' as a separate category\ncombined_df['contractenddate_991D'] = combined_df['contractenddate_991D'].fillna('NoEndDate')","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:15.762566Z","iopub.execute_input":"2024-05-01T19:38:15.762835Z","iopub.status.idle":"2024-05-01T19:38:16.286789Z","shell.execute_reply.started":"2024-05-01T19:38:15.762811Z","shell.execute_reply":"2024-05-01T19:38:16.285815Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Converting the 'contractenddate_991D' column to datetime, specifying the format\ncombined_df['contractenddate_991D'] = pd.to_datetime(combined_df['contractenddate_991D'], format='%Y-%m-%d %H:%M:%S', errors='coerce')\n\n# Since the time is always '00:00:00', you may choose to extract only the date\ncombined_df['contractenddate_991D'] = combined_df['contractenddate_991D'].dt.date\n\n# Extracting date features\ncombined_df['contractenddate_year'] = combined_df['contractenddate_991D'].apply(lambda x: x.year if pd.notnull(x) else x)\ncombined_df['contractenddate_month'] = combined_df['contractenddate_991D'].apply(lambda x: x.month if pd.notnull(x) else x)\ncombined_df['contractenddate_day'] = combined_df['contractenddate_991D'].apply(lambda x: x.day if pd.notnull(x) else x)\ncombined_df['contractenddate_weekday'] = combined_df['contractenddate_991D'].apply(lambda x: x.weekday() if pd.notnull(x) else x)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:16.288196Z","iopub.execute_input":"2024-05-01T19:38:16.288570Z","iopub.status.idle":"2024-05-01T19:38:43.701286Z","shell.execute_reply.started":"2024-05-01T19:38:16.288534Z","shell.execute_reply":"2024-05-01T19:38:43.700268Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Applying One-Hot Coding to Categorical Variables\nfor col in ['education_1103M', 'maritalst_385M']:\n freqs = combined_df[col].value_counts(normalize=True)\n combined_df[f'{col}_freq'] = combined_df[col].map(freqs)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:43.702520Z","iopub.execute_input":"2024-05-01T19:38:43.702902Z","iopub.status.idle":"2024-05-01T19:38:43.741678Z","shell.execute_reply.started":"2024-05-01T19:38:43.702847Z","shell.execute_reply":"2024-05-01T19:38:43.740861Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Normalization and Standardization","metadata":{}},{"cell_type":"code","source":"# Shapiro-Wilk test for normality (applicable for smaller samples)\nfor col in combined_df.select_dtypes(include='number').columns:\n sample_size = min(5000, len(combined_df[col])) # Limit sample size to 5000 for efficiency\n stat, p = shapiro(combined_df[col].dropna().sample(sample_size))\n print(f'{col}: Statistic={stat}, p-value={p}')\n if p > 0.05:\n print(f\"Distribution of {col} appears to be normal.\\n\")\n else:\n print(f\"Distribution of {col} is not normal.\\n\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:43.742802Z","iopub.execute_input":"2024-05-01T19:38:43.743086Z","iopub.status.idle":"2024-05-01T19:38:44.629323Z","shell.execute_reply.started":"2024-05-01T19:38:43.743063Z","shell.execute_reply":"2024-05-01T19:38:44.628264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Applying the Yeo-Johnson transformation\ncombined_df['credamount_770A'], yeo_johnson_params = stats.yeojohnson(combined_df['credamount_770A'])\n\n# Checking the result\nprint(\"Yeo-Johnson transformation parameters:\", yeo_johnson_params)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:44.630595Z","iopub.execute_input":"2024-05-01T19:38:44.630922Z","iopub.status.idle":"2024-05-01T19:38:47.440065Z","shell.execute_reply.started":"2024-05-01T19:38:44.630891Z","shell.execute_reply":"2024-05-01T19:38:47.439102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Detection and Treatment of Outliers","metadata":{}},{"cell_type":"code","source":"# Calculation of IQR for 'credamount_770A'\nQ1 = combined_df['credamount_770A'].quantile(0.25)\nQ3 = combined_df['credamount_770A'].quantile(0.75)\nIQR = Q3 - Q1\n\n# Defining limits for outliers\nlower_bound = Q1 - 1.5 * IQR\nupper_bound = Q3 + 1.5 * IQR\n\n# Adding a column to indicate outliers\ncombined_df['is_outlier_credamount_770A'] = (combined_df['credamount_770A'] < lower_bound) | (combined_df['credamount_770A'] > upper_bound)\n\n# Verifying the result\nprint(\"Flag column for outliers added to the DataFrame.\")\nprint(combined_df[['credamount_770A', 'is_outlier_credamount_770A']].head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:47.441450Z","iopub.execute_input":"2024-05-01T19:38:47.441774Z","iopub.status.idle":"2024-05-01T19:38:47.609475Z","shell.execute_reply.started":"2024-05-01T19:38:47.441748Z","shell.execute_reply":"2024-05-01T19:38:47.608546Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Review of Transformed Data","metadata":{}},{"cell_type":"code","source":"# Listing the new columns created by one-hot encoding\none_hot_columns = [col for col in combined_df.columns if 'education_1103M' in col or 'maritalst_385M' in col]\nprint(\"One-Hot Encoding Columns:\")\nprint(one_hot_columns)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:47.610607Z","iopub.execute_input":"2024-05-01T19:38:47.610922Z","iopub.status.idle":"2024-05-01T19:38:47.616201Z","shell.execute_reply.started":"2024-05-01T19:38:47.610893Z","shell.execute_reply":"2024-05-01T19:38:47.615315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Creating the histogram using Matplotlib\nplt.hist(combined_df['credamount_770A'], bins=30, edgecolor='black', alpha=0.7)\n\n# Adding KDE (Kernel Density Estimate)\ndensity = scipy.stats.gaussian_kde(combined_df['credamount_770A'].dropna())\nxs = np.linspace(combined_df['credamount_770A'].min(), combined_df['credamount_770A'].max(), 200)\nplt.plot(xs, density(xs), label='KDE')\n\n# Adding title and labels\nplt.title('Distribution of credamount_770A After Yeo-Johnson Transformation')\nplt.xlabel('credamount_770A')\nplt.ylabel('Frequency')\n\n# Showing the legend\nplt.legend()\n\n# Displaying the plot\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:38:47.617404Z","iopub.execute_input":"2024-05-01T19:38:47.617709Z","iopub.status.idle":"2024-05-01T19:39:21.151016Z","shell.execute_reply.started":"2024-05-01T19:38:47.617686Z","shell.execute_reply":"2024-05-01T19:39:21.150054Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Check the number of columns in the DataFrame to assess dimensionality","metadata":{}},{"cell_type":"code","source":"# Counting the number of columns in the DataFrame\nnum_columns = combined_df.shape[1]\nprint(f\"Number of columns in the DataFrame: {num_columns}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:39:21.152392Z","iopub.execute_input":"2024-05-01T19:39:21.153216Z","iopub.status.idle":"2024-05-01T19:39:21.158276Z","shell.execute_reply.started":"2024-05-01T19:39:21.153183Z","shell.execute_reply":"2024-05-01T19:39:21.157238Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Correlation Heatmaps","metadata":{}},{"cell_type":"code","source":"# Selecting only numeric columns for correlation calculation\nnumeric_combined_df = combined_df.select_dtypes(include=[np.number])\n\n# Calculating the correlation matrix and p-values only for numeric columns\ncorrelation_matrix = numeric_combined_df.corr()\np_values = pd.DataFrame(data=np.ones(correlation_matrix.shape), columns=correlation_matrix.columns, index=correlation_matrix.index)\n\n# Checking if the correlation matrix still contains NaNs\nif correlation_matrix.isna().any().any():\n print(\"The correlation matrix still contains NaNs.\")\nelse:\n # Plotting the correlation heatmap\n plt.figure(figsize=(12, 6))\n sns.heatmap(correlation_matrix, annot=True, fmt=\".2f\", cmap='coolwarm')\n plt.title('Correlation Heatmap')\n plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:39:21.159535Z","iopub.execute_input":"2024-05-01T19:39:21.159805Z","iopub.status.idle":"2024-05-01T19:39:22.461192Z","shell.execute_reply.started":"2024-05-01T19:39:21.159777Z","shell.execute_reply":"2024-05-01T19:39:22.460269Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# List of numeric columns\nnum_cols = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P']\n\n# Replacing NaNs with mean in each numeric column\nfor col in num_cols:\n combined_df[col] = combined_df[col].fillna(combined_df[col].mean())\n\n# Calculation of correlation between each pair of numeric columns\nfor i in range(len(num_cols)):\n for j in range(i+1, len(num_cols)):\n corr, p_value = pearsonr(combined_df[num_cols[i]], combined_df[num_cols[j]])\n print(f\"Correlation between {num_cols[i]} and {num_cols[j]}: {corr}, P-value: {p_value}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:39:22.462457Z","iopub.execute_input":"2024-05-01T19:39:22.462741Z","iopub.status.idle":"2024-05-01T19:39:23.035547Z","shell.execute_reply.started":"2024-05-01T19:39:22.462717Z","shell.execute_reply":"2024-05-01T19:39:23.034269Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Spearman or Kendall correlations between variables","metadata":{}},{"cell_type":"code","source":"# Choose a number of features for the hash encoding\nn_features = 10 # Adjust this number as needed\n\nhasher = FeatureHasher(n_features=n_features, input_type='string')\nhashed_features = hasher.transform(combined_df[['education_1103M', 'maritalst_385M']].astype(str).values)\nhashed_df = pd.DataFrame(hashed_features.toarray())\n\n# Concatenate with the original DataFrame\ncombined_df = pd.concat([combined_df, hashed_df], axis=1)\n\n# Remove the original columns\ncombined_df.drop(['education_1103M', 'maritalst_385M'], axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:39:23.037226Z","iopub.execute_input":"2024-05-01T19:39:23.038388Z","iopub.status.idle":"2024-05-01T19:39:45.560908Z","shell.execute_reply.started":"2024-05-01T19:39:23.038338Z","shell.execute_reply":"2024-05-01T19:39:45.560056Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate correlation between 'credamount_770A' and each of the encoded variables\ncorrelations = {}\nfor col in hashed_df.columns:\n if combined_df[col].nunique() > 1: # Check if the column is not constant\n correlations[col] = combined_df['credamount_770A'].corr(combined_df[col], method='spearman')\n else:\n correlations[col] = None # For constant columns, set correlation to None\n\n# Display correlations\nfor col, corr in correlations.items():\n if corr is not None:\n print(f\"Correlation between 'credamount_770A' and {col}: {corr}\")\n else:\n print(f\"{col} is a constant column, hence correlation is not defined.\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:39:45.562142Z","iopub.execute_input":"2024-05-01T19:39:45.562452Z","iopub.status.idle":"2024-05-01T19:39:57.741365Z","shell.execute_reply.started":"2024-05-01T19:39:45.562427Z","shell.execute_reply":"2024-05-01T19:39:57.740295Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Visualization with heatmap (for a reduced number of variables)\nplt.figure(figsize=(10, 8))\nsns.heatmap(hashed_df.corr(method='spearman'), annot=True, cmap='coolwarm')\nplt.title('Spearman Correlation Matrix (Hashed Features)')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:39:57.742442Z","iopub.execute_input":"2024-05-01T19:39:57.742732Z","iopub.status.idle":"2024-05-01T19:40:03.306783Z","shell.execute_reply.started":"2024-05-01T19:39:57.742708Z","shell.execute_reply":"2024-05-01T19:40:03.305919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Descriptive Analysis and Correlation Check","metadata":{}},{"cell_type":"code","source":"# Descriptive Analysis\nprint(\"Descriptive Analysis:\")\nprint(combined_df[['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P']].describe())\n\n# Correlation Check\nprint(\"\\nCorrelation between credamount_770A and actualdpd_943P:\")\nprint(combined_df[['credamount_770A', 'actualdpd_943P']].corr())\n\n# Creating a New Combined Variable\n# Example: Sum of 'credamount_770A' and 'numactivecreds_622L'\ncombined_df['combined_feature'] = combined_df['credamount_770A'] + combined_df['numactivecreds_622L']\n\n# Visualization of Relationship\nplt.figure(figsize=(10, 6))\nsns.scatterplot(data=combined_df, x='credamount_770A', y='numactivecreds_622L')\nplt.title('Relationship between Credit Amount and Number of Active Credits')\nplt.show()\n\n# Impact Analysis on Delinquency\nprint(\"\\nImpact of combined variable on delinquency (DPD):\")\nprint(combined_df.groupby('actualdpd_943P')['combined_feature'].mean())\n\n# Correlation Check with Delinquency\ncorr, p_value = pearsonr(combined_df['combined_feature'], combined_df['actualdpd_943P'])\nprint(f\"\\nCorrelation between 'combined_feature' and 'actualdpd_943P': {corr}, P-value: {p_value}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:03.308161Z","iopub.execute_input":"2024-05-01T19:40:03.308535Z","iopub.status.idle":"2024-05-01T19:40:16.711736Z","shell.execute_reply.started":"2024-05-01T19:40:03.308502Z","shell.execute_reply":"2024-05-01T19:40:16.710232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Calculating the Proportion of Credit Used","metadata":{}},{"cell_type":"code","source":"# Ensuring the values are numeric\ncombined_df['amount_416A'] = pd.to_numeric(combined_df['amount_416A'], errors='coerce')\ncombined_df['totaldebt_9A'] = pd.to_numeric(combined_df['totaldebt_9A'], errors='coerce')\n\n# Calculating the ratio\ncombined_df['credit_usage_ratio'] = combined_df['amount_416A'] / combined_df['totaldebt_9A']\n\n# Replacing infinite values with NaN\ncombined_df['credit_usage_ratio'] = combined_df['credit_usage_ratio'].replace([np.inf, -np.inf], np.nan)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:16.715004Z","iopub.execute_input":"2024-05-01T19:40:16.715934Z","iopub.status.idle":"2024-05-01T19:40:16.834689Z","shell.execute_reply.started":"2024-05-01T19:40:16.715887Z","shell.execute_reply":"2024-05-01T19:40:16.833570Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Displaying the first 5 rows of the 'credit usage ratio' column\nprint(combined_df['credit_usage_ratio'].head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:16.835913Z","iopub.execute_input":"2024-05-01T19:40:16.836213Z","iopub.status.idle":"2024-05-01T19:40:16.842239Z","shell.execute_reply.started":"2024-05-01T19:40:16.836189Z","shell.execute_reply":"2024-05-01T19:40:16.841235Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(combined_df['credit_usage_ratio'].describe())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:16.843433Z","iopub.execute_input":"2024-05-01T19:40:16.843995Z","iopub.status.idle":"2024-05-01T19:40:16.973876Z","shell.execute_reply.started":"2024-05-01T19:40:16.843964Z","shell.execute_reply":"2024-05-01T19:40:16.972897Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Creating a histogram with Plotly\nfig = px.histogram(\n combined_df, \n x='credit_usage_ratio',\n nbins=50, # Number of bins\n title='Distribution of Credit Usage Ratio',\n labels={'credit_usage_ratio': 'Credit Usage Ratio'} # Setting x-axis label\n)\n\n# Updating axis labels and adding title\nfig.update_xaxes(title_text='Credit Usage Ratio')\nfig.update_yaxes(title_text='Frequency')\n\n# Displaying the plot\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:16.985885Z","iopub.execute_input":"2024-05-01T19:40:16.986615Z","iopub.status.idle":"2024-05-01T19:40:17.700502Z","shell.execute_reply.started":"2024-05-01T19:40:16.986586Z","shell.execute_reply":"2024-05-01T19:40:17.698710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(combined_df[['amount_416A', 'totaldebt_9A', 'credit_usage_ratio']].head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:17.702638Z","iopub.execute_input":"2024-05-01T19:40:17.703284Z","iopub.status.idle":"2024-05-01T19:40:17.782177Z","shell.execute_reply.started":"2024-05-01T19:40:17.703225Z","shell.execute_reply":"2024-05-01T19:40:17.781204Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Temporal Trends and Patterns","metadata":{}},{"cell_type":"code","source":"# Converting 'openingdate_313D' to datetime format\ncombined_df['openingdate_313D'] = pd.to_datetime(combined_df['openingdate_313D'])\n\n# Filtering data from the initial year\ninitial_year_2013 = 2013\nfiltered_data = combined_df[combined_df['openingdate_313D'].dt.year >= initial_year_2013]\n\n# Grouping by year and counting the number of new credits\ntrends = filtered_data.groupby(filtered_data['openingdate_313D'].dt.year).size().reset_index(name='count')\n\n# Creating an interactive bar plot with Plotly\nfig = px.bar(trends, x='openingdate_313D', y='count', title='Number of New Credits Opened per Year from ' + str(initial_year_2013))\nfig.update_xaxes(title_text='Year')\nfig.update_yaxes(title_text='Number of New Credits')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:17.783507Z","iopub.execute_input":"2024-05-01T19:40:17.783886Z","iopub.status.idle":"2024-05-01T19:40:17.994665Z","shell.execute_reply.started":"2024-05-01T19:40:17.783844Z","shell.execute_reply":"2024-05-01T19:40:17.993771Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Converting to datetime\ncombined_df['openingdate_313D'] = pd.to_datetime(combined_df['openingdate_313D'])\n\n# Grouping by year and calculating the mean\nyearly_data = combined_df.groupby(combined_df['openingdate_313D'].dt.year)['amount_416A'].mean().reset_index()\n\n# Creating an interactive plot with Plotly\nfig = px.line(yearly_data, x='openingdate_313D', y='amount_416A', title='Annual Trends of amount_416A')\nfig.update_xaxes(title_text='Year')\nfig.update_yaxes(title_text='Average amount_416A')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:17.995959Z","iopub.execute_input":"2024-05-01T19:40:17.996250Z","iopub.status.idle":"2024-05-01T19:40:18.246443Z","shell.execute_reply.started":"2024-05-01T19:40:17.996227Z","shell.execute_reply":"2024-05-01T19:40:18.245511Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature Engineering","metadata":{}},{"cell_type":"code","source":"# Credit Amount Variation\ncombined_df.sort_values(by=['contractdate_551D', 'openingdate_313D'], inplace=True)\ncombined_df['credamount_change'] = combined_df.groupby('contractdate_551D')['credamount_770A'].pct_change()\n\n# DPD History\ncombined_df['dpd_history'] = combined_df.groupby('contractdate_551D')['actualdpd_943P'].transform('mean')\n\n# Credit Relationship Duration\ncombined_df['credit_duration'] = combined_df.groupby('contractdate_551D')['openingdate_313D'].transform(lambda x: (x.max() - x.min()).days)\n\n# Number of Active Credits\ncombined_df['active_credits'] = combined_df.groupby('contractdate_551D')['numactivecreds_622L'].transform('sum')\n\n# Credit/Debt Ratio\ncombined_df['credit_debt_ratio'] = combined_df['credamount_770A'] / combined_df['totaldebt_9A']\n\n# Replace possible infinite values with NaN in 'credit_debt_ratio'\ncombined_df['credit_debt_ratio'] = combined_df['credit_debt_ratio'].replace([np.inf, -np.inf], np.nan)\n\n# Checking the new features\nprint(combined_df[['contractdate_551D', 'credamount_change', 'dpd_history', 'credit_duration', 'active_credits', 'credit_debt_ratio']].head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:18.247585Z","iopub.execute_input":"2024-05-01T19:40:18.247880Z","iopub.status.idle":"2024-05-01T19:40:24.213954Z","shell.execute_reply.started":"2024-05-01T19:40:18.247834Z","shell.execute_reply":"2024-05-01T19:40:24.213002Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replacing infinite values with NaN in the 'credit_debt_ratio' column\ncombined_df['credit_debt_ratio'] = combined_df['credit_debt_ratio'].replace([np.inf, -np.inf], np.nan)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:24.215196Z","iopub.execute_input":"2024-05-01T19:40:24.215507Z","iopub.status.idle":"2024-05-01T19:40:24.250075Z","shell.execute_reply.started":"2024-05-01T19:40:24.215480Z","shell.execute_reply":"2024-05-01T19:40:24.249154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Customer Segmentation with Clustering Techniques","metadata":{}},{"cell_type":"code","source":"# Printing out the column names of the dataframe to verify the correct columns are being used\nprint(combined_df.columns)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:24.251247Z","iopub.execute_input":"2024-05-01T19:40:24.251543Z","iopub.status.idle":"2024-05-01T19:40:24.256809Z","shell.execute_reply.started":"2024-05-01T19:40:24.251520Z","shell.execute_reply":"2024-05-01T19:40:24.255887Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Initializing a StandardScaler object\nscaler = StandardScaler()\n\n# Specifying the columns to be scaled based on the model's input requirements\ncolumns_to_scale = ['credamount_770A', 'numactivecreds_622L', 'education_1103M_freq', 'maritalst_385M_freq', 'actualdpd_943P']\n\n# Scaling the specified columns and storing the result in a new dataframe\ndf_scaled = scaler.fit_transform(combined_df[columns_to_scale])","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:24.257946Z","iopub.execute_input":"2024-05-01T19:40:24.258219Z","iopub.status.idle":"2024-05-01T19:40:25.685811Z","shell.execute_reply.started":"2024-05-01T19:40:24.258196Z","shell.execute_reply":"2024-05-01T19:40:25.684979Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert df_scaled back to a pandas DataFrame\ndf_scaled = pd.DataFrame(df_scaled, columns=columns_to_scale)\n\n# Fill NaNs with the mean of each column\ndf_scaled.fillna(df_scaled.mean(), inplace=True)\n\n# Check if there are still any NaNs\nif df_scaled.isna().sum().sum() > 0:\n raise ValueError(\"There are still NaNs in the DataFrame.\")\n\n# Applying KMeans\nwcss = []\nfor i in range(1, 11):\n kmeans = KMeans(n_clusters=i, init='k-means++', n_init=10, random_state=42)\n kmeans.fit(df_scaled)\n wcss.append(kmeans.inertia_)\n\n# Plotting the Elbow Method\nplt.plot(range(1, 11), wcss)\nplt.title('Elbow Method')\nplt.xlabel('Number of Clusters')\nplt.ylabel('WCSS')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:40:25.686994Z","iopub.execute_input":"2024-05-01T19:40:25.687305Z","iopub.status.idle":"2024-05-01T19:46:13.943111Z","shell.execute_reply.started":"2024-05-01T19:40:25.687278Z","shell.execute_reply":"2024-05-01T19:46:13.942130Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Setting up a KMeans clustering algorithm with 3 clusters using k-means++ initialization\nkmeans = KMeans(n_clusters=3, init='k-means++', n_init=10, random_state=42)\n\n# Applying the KMeans algorithm to the scaled data to create clusters\nclusters = kmeans.fit_predict(df_scaled)\n\n# Adding the cluster assignments back to the original dataframe\ncombined_df['cluster'] = clusters","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:46:13.944385Z","iopub.execute_input":"2024-05-01T19:46:13.944697Z","iopub.status.idle":"2024-05-01T19:46:31.870691Z","shell.execute_reply.started":"2024-05-01T19:46:13.944670Z","shell.execute_reply":"2024-05-01T19:46:31.869893Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Identify numeric columns\nnumeric_cols = combined_df.select_dtypes(include=[np.number]).columns\n\n# Calculate grouped mean only for numeric columns\ngrouped_means = combined_df.groupby('cluster')[numeric_cols].mean()\n\n# Display the results\nprint(grouped_means)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:46:31.871750Z","iopub.execute_input":"2024-05-01T19:46:31.872041Z","iopub.status.idle":"2024-05-01T19:46:35.758052Z","shell.execute_reply.started":"2024-05-01T19:46:31.872016Z","shell.execute_reply":"2024-05-01T19:46:35.756989Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Reducing dimensions with PCA for visualization purposes\npca = PCA(n_components=2)\nprincipal_components = pca.fit_transform(df_scaled)\n\n# Plotting the first two principal components colored by cluster labels\nplt.figure(figsize=(10, 6))\nplt.scatter(principal_components[:, 0], principal_components[:, 1], c=clusters)\nplt.xlabel('Principal Component 1')\nplt.ylabel('Principal Component 2')\nplt.title('Cluster Visualization')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:46:35.759418Z","iopub.execute_input":"2024-05-01T19:46:35.759815Z","iopub.status.idle":"2024-05-01T19:48:34.007666Z","shell.execute_reply.started":"2024-05-01T19:46:35.759777Z","shell.execute_reply":"2024-05-01T19:48:34.006896Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"* **Cluster 0** 🏦: Appears to be a group of customers with higher financial engagement (larger credit amounts, more active credits, and higher total debt) but with a relatively stable payment history.\n\n* **Cluster 1** ⚠️: Has lower financial engagements and a higher credit usage ratio, potentially indicating a group with a higher credit risk.\n\n* **Cluster 2** 🔍: Exhibits anomalous characteristics, especially regarding Days Past Due (DPD), suggesting the need for further investigation to better understand this group.","metadata":{}},{"cell_type":"markdown","source":"# Cluster Temporal Stability Analysis","metadata":{}},{"cell_type":"code","source":"# Converting 'openingdate_313D' to datetime and extracting the year to a new column\ncombined_df['year'] = pd.to_datetime(combined_df['openingdate_313D']).dt.year\n\n# Selecting specific variables for clustering\ncolumns_to_cluster = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', 'amount_416A', 'totaldebt_9A']\n\n# Normalizing the data using StandardScaler\nscaler = StandardScaler()\ncombined_df_scaled = scaler.fit_transform(combined_df[columns_to_cluster].fillna(0))\n\n# Dataframe for storing clustering results for stability analysis\nstability_analysis = pd.DataFrame()\n\n# Looping over each year to perform clustering on data from that specific year\nfor year in combined_df['year'].unique():\n df_year = combined_df[combined_df['year'] == year].copy()\n if len(df_year) >= 3: # Ensuring there is enough data to form clusters\n df_year_scaled = scaler.transform(df_year[columns_to_cluster].fillna(0))\n\n # Determining an appropriate number of clusters\n n_clusters = min(3, len(df_year_scaled) // 2)\n\n kmeans = KMeans(n_clusters=n_clusters, n_init=10, random_state=42)\n clusters = kmeans.fit_predict(df_year_scaled)\n\n # Adding the cluster assignments to the dataframe\n df_year.loc[:, 'cluster'] = clusters\n stability_analysis = pd.concat([stability_analysis, df_year])\n\n# Analyzing stability: checking the consistency of clusters over time\ncluster_counts = stability_analysis.groupby(['year', 'cluster']).size().unstack(fill_value=0)\n\n# Visualizing cluster distribution over time\ncluster_counts.plot(kind='bar', stacked=True, figsize=(15, 6))\nplt.title('Distribution of Clusters Over Time')\nplt.xlabel('Year')\nplt.ylabel('Count')\nplt.show()\n\n# Printing cluster counts by year for numerical information\nprint(cluster_counts)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:34.008807Z","iopub.execute_input":"2024-05-01T19:48:34.009116Z","iopub.status.idle":"2024-05-01T19:48:37.509395Z","shell.execute_reply.started":"2024-05-01T19:48:34.009090Z","shell.execute_reply":"2024-05-01T19:48:37.508419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Observations and Possible Interpretations:\n\n* **Variation Over Time** 🕒: There is notable variation in the distribution of clusters over the years. For example, in 2013, Cluster 0 has the highest count, while in 2014, Cluster 1 dominates. This may indicate changes in credit patterns, default rates, or other customer-related factors during these years.\n\n* **Dominant Clusters** 📈: In certain years, such as 2016, one cluster (in this case, Cluster 0) is extremely dominant. This might suggest a behavior or characteristic that is very common among most customers in that year.\n\n* **Small Counts in Earlier Years** 📉: The early years, such as 2002-2011, have relatively smaller counts across all clusters. This could be due to a smaller data base or fewer active customers in those years.\n\n* **Recent Years with High Activity** 🚀: The most recent years show a significant increase in customer counts, possibly reflecting an expansion of the business or greater customer acquisition.","metadata":{}},{"cell_type":"markdown","source":"# Analysis of Cluster Characteristics","metadata":{}},{"cell_type":"code","source":"# Choose a year for detailed analysis, 2013.\nyear_to_analyze = 2013\n\n# Filter data for the selected year\ndf_year_2013 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Aggregate means of features for each cluster\ncluster_characteristics = df_year_2013.groupby('cluster')[columns_to_cluster].mean()\n\nprint(f\"Cluster Analysis for the year {year_to_analyze}:\")\nprint(cluster_characteristics)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:37.510671Z","iopub.execute_input":"2024-05-01T19:48:37.510979Z","iopub.status.idle":"2024-05-01T19:48:37.528881Z","shell.execute_reply.started":"2024-05-01T19:48:37.510954Z","shell.execute_reply":"2024-05-01T19:48:37.527986Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace 'df_year' with your DataFrame for the year 2013\ndf = df_year_2013\n\n# Variables for plots\nvariables = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', 'amount_416A', 'totaldebt_9A']\n\n# Creating a DataFrame to store the reshaped data\ndf_long = pd.DataFrame()\n\nfor var in variables:\n # Transforms the DataFrame into a long format\n df_temp = df[['cluster', var]].copy()\n df_temp['variable'] = var\n df_temp.rename(columns={var: 'value'}, inplace=True)\n df_long = pd.concat([df_long, df_temp])\n\n# Creating a Box Plot with the reshaped data\nfig = px.box(df_long, x='cluster', y='value', color='cluster', facet_col='variable', facet_col_wrap=2)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:37.530186Z","iopub.execute_input":"2024-05-01T19:48:37.530543Z","iopub.status.idle":"2024-05-01T19:48:37.761924Z","shell.execute_reply.started":"2024-05-01T19:48:37.530512Z","shell.execute_reply":"2024-05-01T19:48:37.760906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Choose a year for detailed analysis, 2014\nyear_to_analyze = 2014\n\n# Filter data for the selected year\ndf_year_2014 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Aggregate means of features for each cluster\ncluster_characteristics = df_year_2014.groupby('cluster')[columns_to_cluster].mean()\n\nprint(f\"Cluster Analysis for the year {year_to_analyze}:\")\nprint(cluster_characteristics)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:37.763085Z","iopub.execute_input":"2024-05-01T19:48:37.763361Z","iopub.status.idle":"2024-05-01T19:48:37.792360Z","shell.execute_reply.started":"2024-05-01T19:48:37.763337Z","shell.execute_reply":"2024-05-01T19:48:37.791464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Replace 'df_year' with your DataFrame for the year 2014\ndf = df_year_2014\n\n# Variables for plots\nvariables = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', 'amount_416A', 'totaldebt_9A']\n\n# Creating a DataFrame to store the reshaped data\ndf_long = pd.DataFrame()\n\nfor var in variables:\n # Transforms the DataFrame into a long format\n df_temp = df[['cluster', var]].copy()\n df_temp['variable'] = var\n df_temp.rename(columns={var: 'value'}, inplace=True)\n df_long = pd.concat([df_long, df_temp])\n\n# Creating a Box Plot with the reshaped data\nfig = px.box(df_long, x='cluster', y='value', color='cluster', facet_col='variable', facet_col_wrap=2)\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:37.793349Z","iopub.execute_input":"2024-05-01T19:48:37.793632Z","iopub.status.idle":"2024-05-01T19:48:38.060335Z","shell.execute_reply.started":"2024-05-01T19:48:37.793608Z","shell.execute_reply":"2024-05-01T19:48:38.058000Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Cluster Temporal Stability Analysis","metadata":{}},{"cell_type":"code","source":"# Choose a year for root cause analysis, 2016\nyear_for_cause_analysis = 2016\n\n# Filter data for the selected year\ndf_cause_analysis = stability_analysis[stability_analysis['year'] == year_for_cause_analysis]\n\n# Perform statistical analyses, such as hypothesis tests, to identify significant differences\n# between clusters for that specific year\n# Here, you can use tests like t-test, ANOVA, etc., depending on the nature of your data\n\n# T-test to compare means of a variable between two clusters\ncluster_0 = df_cause_analysis[df_cause_analysis['cluster'] == 0]['credamount_770A']\ncluster_1 = df_cause_analysis[df_cause_analysis['cluster'] == 1]['credamount_770A']\n\nt_stat, p_val = ttest_ind(cluster_0, cluster_1, nan_policy='omit')\nprint(f\"T-statistic: {t_stat}, P-value: {p_val}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.061895Z","iopub.execute_input":"2024-05-01T19:48:38.062269Z","iopub.status.idle":"2024-05-01T19:48:38.099337Z","shell.execute_reply.started":"2024-05-01T19:48:38.062237Z","shell.execute_reply":"2024-05-01T19:48:38.098521Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_2016 = df_cause_analysis # Replace with your DataFrame for 2016\n\n# Calculating the mean of credamount_770A per cluster\nmean_credamount_per_cluster = df_2016.groupby('cluster')['credamount_770A'].mean().reset_index()\n\n# Creating a bar chart using Plotly Express\nfig = px.bar(mean_credamount_per_cluster, x='cluster', y='credamount_770A',\n labels={'credamount_770A': 'Average of credamount_770A', 'cluster': 'Cluster'},\n title='Average credamount_770A per Cluster in 2016')\n\n# Displaying the figure\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.100423Z","iopub.execute_input":"2024-05-01T19:48:38.100702Z","iopub.status.idle":"2024-05-01T19:48:38.167477Z","shell.execute_reply.started":"2024-05-01T19:48:38.100678Z","shell.execute_reply":"2024-05-01T19:48:38.166602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Assuming 'df' is your DataFrame and 'variables' is a list containing the names of columns for which you want statistics by cluster\nvariables = ['credamount_770A', 'numactivecreds_622L', 'education_1103M_freq', 'maritalst_385M_freq', 'actualdpd_943P']\n\n# Looping through each variable in the list to compute and print descriptive statistics grouped by cluster\nfor var in variables:\n # Printing the name of the variable to clearly indicate which statistics are being shown\n print(f'Statistics of {var} by Cluster:')\n \n # Grouping the DataFrame by the 'cluster' column and calculating descriptive statistics for the current variable\n # The describe() method provides count, mean, std, min, quartiles, and max, which give a comprehensive summary of the distribution\n print(df.groupby('cluster')[var].describe(), '\\n')","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.168702Z","iopub.execute_input":"2024-05-01T19:48:38.169006Z","iopub.status.idle":"2024-05-01T19:48:38.225697Z","shell.execute_reply.started":"2024-05-01T19:48:38.168980Z","shell.execute_reply":"2024-05-01T19:48:38.224786Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Analysis Results:\n\n* **Analysis of Clusters for 2014** 📊: The clusters show significant differences in the characteristics 'credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', 'amount_416A', and 'totaldebt_9A'. This suggests distinct customer profiles or credit behaviors within each group.\n\n* **Temporal Stability Analysis for 2016** ⏳: The T-test between clusters 0 and 1 for the variable 'credamount_770A' resulted in a T-statistic of -0.439 and a P-value of 0.660. This statistic is not significant (p > 0.05), suggesting that there is no statistically significant difference in 'credamount_770A' between clusters 0 and 1 in 2016. This may indicate that, for this variable, the clusters do not differ significantly in this specific year.","metadata":{}},{"cell_type":"markdown","source":"# Detailed Cluster Analysis for Specific Years\n\n* I decided to focus on the years 2013, 2014, and 2015 for this analysis, as they presented an intriguing cluster distribution. In 2013, Cluster 0 stood out as dominant, suggesting possible distinct characteristics or trends. 📊✨","metadata":{}},{"cell_type":"markdown","source":"# 2013 🎉","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, for example, 2013\nyear_to_analyze = 2013\n\n# Filtering the data for the selected year\ndf_year_2013 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Creating a copy of the DataFrame to perform transformations\ndf_year_2013_transformed = df_year_2013.copy()\n\n# Aggregated characteristics of clusters for numerical variables\ncluster_characteristics_2013 = df_year_2013.groupby('cluster')[columns_to_cluster].mean()\nprint(f\"Cluster Characteristics for the year {year_to_analyze}:\")\nprint(cluster_characteristics_2013)\n\n# Frequency analysis for categorical variables\nadditional_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nfor column in additional_columns:\n freq_table = pd.crosstab(df_year_2013_transformed['cluster'], df_year_2013_transformed[column])\n print(f\"\\nFrequency Table for '{column}' by Cluster:\")\n print(freq_table)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.226823Z","iopub.execute_input":"2024-05-01T19:48:38.227127Z","iopub.status.idle":"2024-05-01T19:48:38.269372Z","shell.execute_reply.started":"2024-05-01T19:48:38.227102Z","shell.execute_reply":"2024-05-01T19:48:38.268468Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2014 🎉","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, for example, 2014\nyear_to_analyze = 2014\n\n# Filtering the data for the selected year\ndf_year_2014 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Creating a copy of the DataFrame to perform transformations\ndf_year_2014_transformed = df_year_2014.copy()\n\n# Aggregated characteristics of clusters for numerical variables\ncluster_characteristics_2014 = df_year_2014.groupby('cluster')[columns_to_cluster].mean()\nprint(f\"Cluster Characteristics for the year {year_to_analyze}:\")\nprint(cluster_characteristics_2014)\n\n# Frequency analysis for categorical variables\nadditional_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nfor column in additional_columns:\n freq_table = pd.crosstab(df_year_2014_transformed['cluster'], df_year_2014_transformed[column])\n print(f\"\\nFrequency Table for '{column}' by Cluster:\")\n print(freq_table)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.270571Z","iopub.execute_input":"2024-05-01T19:48:38.271334Z","iopub.status.idle":"2024-05-01T19:48:38.353305Z","shell.execute_reply.started":"2024-05-01T19:48:38.271307Z","shell.execute_reply":"2024-05-01T19:48:38.352040Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2015 🎉","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, for example, 2015\nyear_to_analyze = 2015\n\n# Filtering the data for the selected year\ndf_year_2015 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Creating a copy of the DataFrame to perform transformations\ndf_year_2015_transformed = df_year_2015.copy()\n\n# Aggregated characteristics of clusters for numerical variables\ncluster_characteristics_2015 = df_year_2015.groupby('cluster')[columns_to_cluster].mean()\nprint(f\"Cluster Characteristics for the year {year_to_analyze}:\")\nprint(cluster_characteristics_2015)\n\n# Frequency analysis for categorical variables\nadditional_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nfor column in additional_columns:\n freq_table = pd.crosstab(df_year_2015_transformed['cluster'], df_year_2015_transformed[column])\n print(f\"\\nFrequency Table for '{column}' by Cluster:\")\n print(freq_table)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.356485Z","iopub.execute_input":"2024-05-01T19:48:38.356747Z","iopub.status.idle":"2024-05-01T19:48:38.422173Z","shell.execute_reply.started":"2024-05-01T19:48:38.356725Z","shell.execute_reply":"2024-05-01T19:48:38.421179Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Cluster Characteristics Analysis by Year (2013, 2014, 2015)","metadata":{}},{"cell_type":"code","source":"years = [2013, 2014, 2015]\ndataframes = [df_year_2013_transformed, df_year_2014_transformed, df_year_2015_transformed] \ncolumns_to_cluster = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', 'amount_416A', 'totaldebt_9A']\n\n# Define colors for each cluster\ncolors = ['red', 'green', 'blue', 'orange', 'purple']\n\n# Width of the bars\nbar_width = 0.15\n\n# Loop over each column to be analyzed\nfor column in columns_to_cluster:\n plt.figure(figsize=(12, 6))\n\n # For each year, draw the grouped bars\n for i, (year, df) in enumerate(zip(years, dataframes)):\n # Calculate the mean for the current column\n means = df.groupby('cluster')[column].mean()\n # Positions of the bars for this year\n bar_positions = np.arange(len(means)) + i * bar_width\n\n plt.bar(bar_positions, means, width=bar_width, color=colors[i % len(colors)], edgecolor='black', label=f'Year {year}')\n\n plt.xlabel('Cluster')\n plt.ylabel('Average Value')\n plt.title(f'Cluster Characteristics for {column} (2013-2015)')\n \n # Dynamic adjustment of the x-ticks position based on means\n x_ticks_positions = np.arange(len(means)) + bar_width\n \n # Set the x-ticks and labels\n plt.xticks(x_ticks_positions, means.index)\n \n # Improve the placement of the legend\n plt.legend(loc='upper right', bbox_to_anchor=(1.1, 1.05), title=column) # Move legend out of the plot\n \n plt.tight_layout()\n plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:38.423432Z","iopub.execute_input":"2024-05-01T19:48:38.424126Z","iopub.status.idle":"2024-05-01T19:48:39.706657Z","shell.execute_reply.started":"2024-05-01T19:48:38.424090Z","shell.execute_reply":"2024-05-01T19:48:39.705835Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Education and Marital Status Frequency Analysis by Year","metadata":{}},{"cell_type":"code","source":"additional_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nyears = [2013, 2014, 2015]\ndataframes = [df_year_2013_transformed, df_year_2014_transformed, df_year_2015_transformed] # Replace with your actual DataFrame names\n\nfor year, df in zip(years, dataframes):\n for column in additional_columns:\n freq_table = pd.crosstab(df['cluster'], df[column])\n\n # Converting the frequency table to percentage\n freq_table_percentage = freq_table.div(freq_table.sum(axis=1), axis=0)\n\n # Plotting the stacked bar chart\n ax = freq_table_percentage.plot(kind='bar', stacked=True, figsize=(12, 6), colormap='viridis')\n plt.title(f'Frequency Percentage of {column} by Cluster in {year}')\n plt.xlabel('Cluster')\n plt.ylabel('Percentage')\n plt.legend(title=column, bbox_to_anchor=(1.05, 1), loc='upper left')\n plt.tight_layout()\n plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:39.707665Z","iopub.execute_input":"2024-05-01T19:48:39.707949Z","iopub.status.idle":"2024-05-01T19:48:41.515276Z","shell.execute_reply.started":"2024-05-01T19:48:39.707925Z","shell.execute_reply":"2024-05-01T19:48:41.514246Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Expanded Cluster Feature Analysis for 2013-2015:\n\n* In this phase, we explore additional variables such as 'education_1103M', 'maritalst_385M', etc., across the years 2013, 2014, and 2015, calculating averages for each cluster. The title highlights our focus on examining extra characteristics beyond the initial set of variables. 📊✨","metadata":{}},{"cell_type":"markdown","source":"# 2013 🎊","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, 2013\nyear_to_analyze = 2013\ndf_year_2013 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Categorical and numerical variables for analysis\ncategorical_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nnumerical_columns = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A']\n\n# Aggregating cluster characteristics for numerical variables\nfor column in numerical_columns:\n cluster_characteristics_numerical = df_year_2013.groupby('cluster')[column].mean()\n print(f\"\\nMean of {column} by Cluster:\")\n print(cluster_characteristics_numerical)\n\n# Analyzing mode for categorical variables\nfor column in categorical_columns:\n cluster_characteristics_categorical = df_year_2013.groupby('cluster')[column].agg(lambda x: x.mode().iat[0])\n print(f\"\\nMode of {column} by Cluster:\")\n print(cluster_characteristics_categorical)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.516379Z","iopub.execute_input":"2024-05-01T19:48:41.516668Z","iopub.status.idle":"2024-05-01T19:48:41.543221Z","shell.execute_reply.started":"2024-05-01T19:48:41.516643Z","shell.execute_reply":"2024-05-01T19:48:41.542309Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2014 🎊","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, 2014\nyear_to_analyze = 2014\ndf_year_2014 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Categorical and numerical variables for analysis\ncategorical_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nnumerical_columns = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A']\n\n# Aggregating cluster characteristics for numerical variables\nfor column in numerical_columns:\n cluster_characteristics_numerical = df_year_2014.groupby('cluster')[column].mean()\n print(f\"\\nMean of {column} by Cluster:\")\n print(cluster_characteristics_numerical)\n\n# Analyzing mode for categorical variables\nfor column in categorical_columns:\n cluster_characteristics_categorical = df_year_2014.groupby('cluster')[column].agg(lambda x: x.mode().iat[0])\n print(f\"\\nMode of {column} by Cluster:\")\n print(cluster_characteristics_categorical)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.544312Z","iopub.execute_input":"2024-05-01T19:48:41.544611Z","iopub.status.idle":"2024-05-01T19:48:41.584412Z","shell.execute_reply.started":"2024-05-01T19:48:41.544585Z","shell.execute_reply":"2024-05-01T19:48:41.583370Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2015 🎊","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, 2015\nyear_to_analyze = 2015\ndf_year_2015 = stability_analysis[stability_analysis['year'] == year_to_analyze]\n\n# Categorical and numerical variables for analysis\ncategorical_columns = ['education_1103M_freq', 'maritalst_385M_freq']\nnumerical_columns = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A']\n\n# Aggregating cluster characteristics for numerical variables\nfor column in numerical_columns:\n cluster_characteristics_numerical = df_year_2015.groupby('cluster')[column].mean()\n print(f\"\\nMean of {column} by Cluster:\")\n print(cluster_characteristics_numerical)\n\n# Analyzing mode for categorical variables\nfor column in categorical_columns:\n cluster_characteristics_categorical = df_year_2015.groupby('cluster')[column].agg(lambda x: x.mode().iat[0])\n print(f\"\\nMode of {column} by Cluster:\")\n print(cluster_characteristics_categorical)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.585932Z","iopub.execute_input":"2024-05-01T19:48:41.586495Z","iopub.status.idle":"2024-05-01T19:48:41.622070Z","shell.execute_reply.started":"2024-05-01T19:48:41.586459Z","shell.execute_reply":"2024-05-01T19:48:41.621125Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Conclusions and Implications:\n\n* **Temporal Consistency** 🕒: The characteristics of the clusters have shown consistency over the years, with Cluster 1 consistently exhibiting higher total debts.\n\n* **Patterns in Education and Marital Status** 📘💍: The patterns of education and marital status did not show significant variations among the clusters, suggesting that these factors may not be discriminatory for cluster classification.\n\n* **Implications for Risk Modeling** 📊: The analysis suggests that factors such as the amount of credit and the number of active credits are more relevant for differentiating clusters in terms of debt risk. The stability of these characteristics over time can be utilized for risk modeling and predicting defaults.","metadata":{}},{"cell_type":"markdown","source":"# Integrated Cluster Analysis for 2013-2015:\n\n* This phase merges the study of numeric and categorical variables, giving a complete view of cluster characteristics over these years. The title captures the essence of a thorough and integrated analysis approach. 📊✨","metadata":{}},{"cell_type":"markdown","source":"# 2013 🌟","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, 2013\nyear_to_analyze = 2013\ndf_year_2013 = combined_df[combined_df['year'] == year_to_analyze]\n\n# Selected variables for analysis\nselected_columns = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', \n 'totaldebt_9A', 'education_1103M_freq', 'maritalst_385M_freq', \n 'credit_duration', 'active_credits', 'currdebt_22A']\n\n# Separating numerical and categorical columns\nnumeric_cols = df_year_2013[selected_columns].select_dtypes(include='number').columns\ncategorical_cols = df_year_2013[selected_columns].select_dtypes(include='category').columns\n\n# Aggregating cluster characteristics for numerical variables\nnumeric_cluster_characteristics = df_year_2013.groupby('cluster')[numeric_cols].mean()\n\n# Calculating the mode for categorical variables\ncategorical_cluster_mode = df_year_2013.groupby('cluster')[categorical_cols].agg(lambda x: x.mode().iat[0])\n\n# Displaying the results\nprint(f\"Numerical Characteristics of Clusters for the year {year_to_analyze}:\")\nprint(numeric_cluster_characteristics)\nprint(f\"\\nMode of Categorical Characteristics of Clusters for the year {year_to_analyze}:\")\nprint(categorical_cluster_mode)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.623207Z","iopub.execute_input":"2024-05-01T19:48:41.623488Z","iopub.status.idle":"2024-05-01T19:48:41.662025Z","shell.execute_reply.started":"2024-05-01T19:48:41.623464Z","shell.execute_reply":"2024-05-01T19:48:41.661120Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2014 🌟","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, 2014\nyear_to_analyze = 2014\ndf_year_2014 = combined_df[combined_df['year'] == year_to_analyze]\n\n# Selected variables for analysis\nselected_columns = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', \n 'totaldebt_9A', 'education_1103M_freq', 'maritalst_385M_freq', \n 'credit_duration', 'active_credits', 'currdebt_22A']\n\n# Separating numerical and categorical columns\nnumeric_cols = df_year_2014[selected_columns].select_dtypes(include='number').columns\ncategorical_cols = df_year_2014[selected_columns].select_dtypes(include='category').columns\n\n# Aggregating cluster characteristics for numerical variables\nnumeric_cluster_characteristics = df_year_2014.groupby('cluster')[numeric_cols].mean()\n\n# Calculating the mode for categorical variables\ncategorical_cluster_mode = df_year_2014.groupby('cluster')[categorical_cols].agg(lambda x: x.mode().iat[0])\n\n# Displaying the results\nprint(f\"Numerical Characteristics of Clusters for the year {year_to_analyze}:\")\nprint(numeric_cluster_characteristics)\nprint(f\"\\nMode of Categorical Characteristics of Clusters for the year {year_to_analyze}:\")\nprint(categorical_cluster_mode)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.663224Z","iopub.execute_input":"2024-05-01T19:48:41.663561Z","iopub.status.idle":"2024-05-01T19:48:41.716283Z","shell.execute_reply.started":"2024-05-01T19:48:41.663529Z","shell.execute_reply":"2024-05-01T19:48:41.715348Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# 2015 🌟","metadata":{}},{"cell_type":"code","source":"# Choosing a year for analysis, 2015\nyear_to_analyze = 2015\ndf_year_2015 = combined_df[combined_df['year'] == year_to_analyze]\n\n# Selected variables for analysis\nselected_columns = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', \n 'totaldebt_9A', 'education_1103M_freq', 'maritalst_385M_freq', \n 'credit_duration', 'active_credits', 'currdebt_22A']\n\n# Separating numerical and categorical columns\nnumeric_cols = df_year_2015[selected_columns].select_dtypes(include='number').columns\ncategorical_cols = df_year_2015[selected_columns].select_dtypes(include='category').columns\n\n# Aggregating cluster characteristics for numerical variables\nnumeric_cluster_characteristics = df_year_2015.groupby('cluster')[numeric_cols].mean()\n\n# Calculating the mode for categorical variables\ncategorical_cluster_mode = df_year_2015.groupby('cluster')[categorical_cols].agg(lambda x: x.mode().iat[0])\n\n# Displaying the results\nprint(f\"Numerical Characteristics of Clusters for the year {year_to_analyze}:\")\nprint(numeric_cluster_characteristics)\nprint(f\"\\nMode of Categorical Characteristics of Clusters for the year {year_to_analyze}:\")\nprint(categorical_cluster_mode)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.717484Z","iopub.execute_input":"2024-05-01T19:48:41.717834Z","iopub.status.idle":"2024-05-01T19:48:41.770890Z","shell.execute_reply.started":"2024-05-01T19:48:41.717793Z","shell.execute_reply":"2024-05-01T19:48:41.769891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Integrated Numerical and Categorical Analysis","metadata":{}},{"cell_type":"code","source":"# Columns for comparison\ncolumns_to_compare = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A']\n\n# Graph settings\nbar_width = 0.2\nopacity = 0.8\nindex = np.arange(len(columns_to_compare))\n\n# Adjust the size of the graph\nplt.figure(figsize=(12, 6))\n\n# Create graphs for each year\nfor i, year in enumerate([2013, 2014, 2015]):\n # Select the DataFrame corresponding to the year\n if year == 2013:\n data = df_year_2013\n elif year == 2014:\n data = df_year_2014\n else:\n data = df_year_2015\n\n # Calculate means for each cluster\n means = [data[data['cluster'] == cluster][column].mean() for column in columns_to_compare for cluster in range(max(data['cluster']) + 1)]\n\n # Rearrange means for each cluster\n means_cluster = [means[i::max(data['cluster']) + 1] for i in range(max(data['cluster']) + 1)]\n\n # Plot bars for each cluster\n for cluster_id, means in enumerate(means_cluster):\n plt.bar(index + bar_width * cluster_id + bar_width * i, means, bar_width, alpha=opacity, label=f'Cluster {cluster_id} in {year}')\n\n# Add labels and title\nplt.xlabel('Characteristics')\nplt.ylabel('Average Values')\nplt.title('Comparison of cluster characteristics between 2013, 2014, and 2015')\nplt.xticks(index + bar_width, columns_to_compare)\nplt.legend()\n\n# Show graph\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:41.772338Z","iopub.execute_input":"2024-05-01T19:48:41.772728Z","iopub.status.idle":"2024-05-01T19:48:42.256721Z","shell.execute_reply.started":"2024-05-01T19:48:41.772693Z","shell.execute_reply":"2024-05-01T19:48:42.255825Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Advanced Trend Analysis and Cluster Comparison","metadata":{}},{"cell_type":"code","source":"warnings.filterwarnings('ignore', category=FutureWarning)\n\n# Create independent copies of the DataFrames\ndf_year_2013_copy = df_year_2013.copy()\ndf_year_2014_copy = df_year_2014.copy()\ndf_year_2015_copy = df_year_2015.copy()\n\n# Convert inf values to NaN\nfor df in [df_year_2013_copy, df_year_2014_copy, df_year_2015_copy]:\n df.replace([np.inf, -np.inf], np.nan, inplace=True)\n\n# Add the 'year' column to each DataFrame copy\ndf_year_2013_copy['year'] = 2013\ndf_year_2014_copy['year'] = 2014\ndf_year_2015_copy['year'] = 2015\n\n# Concatenate the DataFrames\ndf_all_years = pd.concat([df_year_2013_copy, df_year_2014_copy, df_year_2015_copy])\n\n# Aggregate the data\ndf_agg = df_all_years.groupby(['year', 'cluster'])['credamount_770A'].mean().reset_index()\n\n# Check the existence of clusters in each year\nprint(\"Clusters in 2013:\", df_year_2013['cluster'].unique())\nprint(\"Clusters in 2014:\", df_year_2014['cluster'].unique())\nprint(\"Clusters in 2015:\", df_year_2015['cluster'].unique())\n\n# Create a figure and a set of subplots with 2 axes\nfig, ax = plt.subplots(nrows=2, ncols=1, figsize=(12, 6), gridspec_kw={'height_ratios': [2, 1]})\n\n# Line plot for trends over the years\nsns.lineplot(ax=ax[0], data=df_agg, x='year', y='credamount_770A', hue='cluster', marker='o')\nax[0].set_title('Trend of Average Credit Amount per Cluster (2013-2015)')\nax[0].set_xlabel('Year')\nax[0].set_ylabel('Average Credit Amount')\nax[0].legend(title='Cluster')\n\n# Bar plot to compare average credit amounts between clusters in each year\nsns.barplot(ax=ax[1], data=df_agg, x='year', y='credamount_770A', hue='cluster')\nax[1].set_title('Comparison of Average Credit Amount per Cluster by Year')\nax[1].set_xlabel('Year')\nax[1].set_ylabel('Average Credit Amount')\nax[1].legend(title='Cluster')\n\n# Adjust plots for better visualization\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:42.258197Z","iopub.execute_input":"2024-05-01T19:48:42.258837Z","iopub.status.idle":"2024-05-01T19:48:43.065304Z","shell.execute_reply.started":"2024-05-01T19:48:42.258802Z","shell.execute_reply":"2024-05-01T19:48:43.064219Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"warnings.filterwarnings('ignore', category=FutureWarning)\n\n# Selecting the variables for comparison\nvariables_to_compare = ['credamount_770A', 'numactivecreds_622L', 'actualdpd_943P', 'totaldebt_9A']\n\n# Creating line plots for each variable\nfor var in variables_to_compare:\n plt.figure(figsize=(12, 6))\n \n # Plotting the data for each cluster and each year\n for cluster in [0, 1]:\n sns.lineplot(\n data=df_all_years[df_all_years['cluster'] == cluster],\n x='year', y=var,\n label=f'Cluster {cluster}',\n marker='o'\n )\n \n plt.title(f'Trend of {var} for Clusters 0 and 1 (2013-2015)')\n plt.xlabel('Year')\n plt.ylabel(var)\n plt.legend()\n plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:43.066775Z","iopub.execute_input":"2024-05-01T19:48:43.067171Z","iopub.status.idle":"2024-05-01T19:48:48.392037Z","shell.execute_reply.started":"2024-05-01T19:48:43.067138Z","shell.execute_reply":"2024-05-01T19:48:48.391091Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Conclusions and Implications:\n\n* **Significant Differences in Total Debt** 📈: The average total debt shows a striking difference between clusters over the years, which may be a critical indicator for credit risk analysis and customer segmentation.\n\n* **Stability in Categorical Features** 🔄: The similarity in the modes of categorical variables suggests that these features may not be decisive factors in differentiating risk among clusters.\n\n* **Utility for Risk Modeling** 🏦: This analysis provides valuable insights for financial entities when developing credit risk models, emphasizing the importance of considering both numerical and categorical characteristics in client assessments.","metadata":{}},{"cell_type":"markdown","source":"# Comparative Temporal Correlation Analysis","metadata":{}},{"cell_type":"code","source":"# List of years for analysis\nyears = [2013, 2014, 2015]\ndataframes = [df_year_2013, df_year_2014, df_year_2015]\nnumerical_columns = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A', 'active_credits', 'credit_debt_ratio'] # Example of numerical columns\n\n# Create subplots for each year\nfig, axes = plt.subplots(nrows=1, ncols=len(years), figsize=(18, 6))\n\n# Loop to generate heatmaps for each year\nfor i, (year, df) in enumerate(zip(years, dataframes)):\n # Calculate the correlation matrix\n correlation_matrix = df[numerical_columns].corr()\n\n # Generate heatmap on the corresponding subplot\n sns.heatmap(correlation_matrix, annot=True, ax=axes[i])\n axes[i].set_title(f'Correlation Matrix for {year}')\n axes[i].set_xticklabels(axes[i].get_xticklabels(), rotation=45, horizontalalignment='right')\n\n# Adjust layout for better visualization\nplt.tight_layout()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:48.393218Z","iopub.execute_input":"2024-05-01T19:48:48.393524Z","iopub.status.idle":"2024-05-01T19:48:49.528671Z","shell.execute_reply.started":"2024-05-01T19:48:48.393498Z","shell.execute_reply":"2024-05-01T19:48:49.527900Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Segmentation based on clusters","metadata":{}},{"cell_type":"code","source":"# First, create independent copies of the DataFrames to avoid the SettingWithCopyWarning\ndf_2013 = df_year_2013.copy()\ndf_2014 = df_year_2014.copy()\ndf_2015 = df_year_2015.copy()\n\n# Concatenate the DataFrames\ndf_all_years = pd.concat([df_2013, df_2014, df_2015])\n\n# Map clusters to segments\ndf_all_years['segment'] = df_all_years['cluster'].map({0: 'Segment A', 1: 'Segment B', 2: 'Segment C'})\n\n# Select the columns for analysis\ncolumns_for_analysis = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A', 'active_credits', 'credit_debt_ratio']\n\n# Group by year and segment, calculating the mean of the selected columns\nsegment_analysis = df_all_years.groupby(['year', 'segment'])[columns_for_analysis].mean().reset_index()\n\n# Display the results\nprint(segment_analysis)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:49.529830Z","iopub.execute_input":"2024-05-01T19:48:49.530139Z","iopub.status.idle":"2024-05-01T19:48:49.599170Z","shell.execute_reply.started":"2024-05-01T19:48:49.530114Z","shell.execute_reply":"2024-05-01T19:48:49.598150Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Assume 'columns_for_analysis' is the list of columns you want to analyze\ncolumns_for_analysis = ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A', 'active_credits', 'credit_debt_ratio']\n\n# Create a figure with subplots for each variable\nfig = make_subplots(rows=len(columns_for_analysis), cols=1, subplot_titles=columns_for_analysis)\n\nfor i, column in enumerate(columns_for_analysis, start=1):\n # Filtering the data for the current column\n df_filtered = segment_analysis[['year', 'segment', column]]\n\n # Creating a line plot for each segment\n for segment in df_filtered['segment'].unique():\n fig.add_trace(go.Scatter(\n x=df_filtered[df_filtered['segment'] == segment]['year'],\n y=df_filtered[df_filtered['segment'] == segment][column],\n mode='lines+markers',\n name=f'{segment} - {column}'),\n row=i, col=1\n )\n\n# Update subplot layout\nfig.update_layout(height=1000, showlegend=True, title_text='Trends Over Years by Segment')\nfig.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:49.600236Z","iopub.execute_input":"2024-05-01T19:48:49.600527Z","iopub.status.idle":"2024-05-01T19:48:49.663393Z","shell.execute_reply.started":"2024-05-01T19:48:49.600503Z","shell.execute_reply":"2024-05-01T19:48:49.662526Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Detailed Segment Analysis","metadata":{}},{"cell_type":"code","source":"# Checking for unique clusters in the original DataFrame\nprint(\"Unique clusters in df_all_years:\", df_all_years['cluster'].unique())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:49.664449Z","iopub.execute_input":"2024-05-01T19:48:49.664720Z","iopub.status.idle":"2024-05-01T19:48:49.669666Z","shell.execute_reply.started":"2024-05-01T19:48:49.664696Z","shell.execute_reply":"2024-05-01T19:48:49.668785Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"segment_analysis","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:49.670603Z","iopub.execute_input":"2024-05-01T19:48:49.670836Z","iopub.status.idle":"2024-05-01T19:48:49.689747Z","shell.execute_reply.started":"2024-05-01T19:48:49.670815Z","shell.execute_reply":"2024-05-01T19:48:49.688901Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"warnings.filterwarnings('ignore', category=FutureWarning)\n\n# Assuming df_all_years is your original dataframe\ndf_model = df_all_years.copy()\n\n# Replace infinity values with NaN\ndf_model.replace([np.inf, -np.inf], np.nan, inplace=True)\n\n# Mapping clusters to segments\ndf_model['segment'] = df_model['cluster'].map({0: 'Segment A', 1: 'Segment B'})\n\n# Converting the 'segment' column to dummy variables (one-hot encoding)\nsegment_dummies = pd.get_dummies(df_model['segment'])\ndf_model = pd.concat([df_model, segment_dummies], axis=1)\n\n# Selecting relevant columns\ncolumns_of_interest = ['year', 'numactivecreds_622L', 'credamount_770A', 'active_credits', 'credit_debt_ratio', 'totaldebt_9A', 'Segment A', 'Segment B']\n\n# Detailed Time Trend Analysis\nfor column in ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A', 'active_credits', 'credit_debt_ratio']:\n plt.figure(figsize=(12, 6))\n sns.lineplot(data=df_model, x='year', y=column, hue='segment', marker='o')\n plt.title(f'Trend of {column} by Segment Over Time')\n plt.xlabel('Year')\n plt.ylabel(column)\n plt.legend(title='Segment')\n plt.show()\n\n# Correlation Analysis for Each Segment\nfor segment in ['Segment A', 'Segment B']:\n plt.figure(figsize=(12, 6))\n corr_matrix = df_model[df_model[segment] == 1][columns_of_interest].corr()\n sns.heatmap(corr_matrix, annot=True, cmap='coolwarm')\n plt.title(f'Correlation Matrix for {segment}')\n plt.show()\n\n# Identifying Anomalies using Boxplots\nfor column in ['credamount_770A', 'numactivecreds_622L', 'totaldebt_9A', 'active_credits', 'credit_debt_ratio']:\n plt.figure(figsize=(12, 6))\n sns.boxplot(data=df_model, x='segment', y=column)\n plt.title(f'Distribution of {column} by Segment')\n plt.xlabel('Segment')\n plt.ylabel(column)\n plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:49.690879Z","iopub.execute_input":"2024-05-01T19:48:49.691149Z","iopub.status.idle":"2024-05-01T19:48:58.926063Z","shell.execute_reply.started":"2024-05-01T19:48:49.691127Z","shell.execute_reply":"2024-05-01T19:48:58.925106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Advanced Feature Engineering","metadata":{}},{"cell_type":"code","source":"# Combinar os DataFrames\ndf_year = pd.concat([df_year_2013, df_year_2014, df_year_2015])","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:58.927243Z","iopub.execute_input":"2024-05-01T19:48:58.927542Z","iopub.status.idle":"2024-05-01T19:48:58.943052Z","shell.execute_reply.started":"2024-05-01T19:48:58.927517Z","shell.execute_reply":"2024-05-01T19:48:58.941974Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_year.columns","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:58.944284Z","iopub.execute_input":"2024-05-01T19:48:58.944648Z","iopub.status.idle":"2024-05-01T19:48:58.951335Z","shell.execute_reply.started":"2024-05-01T19:48:58.944616Z","shell.execute_reply":"2024-05-01T19:48:58.950405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Generate dummy variables for the 'education_1103M_freq' column and prefix the new columns with 'education'\neducation_dummies = pd.get_dummies(df_year['education_1103M_freq'], prefix='education')\n\n# Print the names of the newly created columns to verify the dummy variables\nprint(education_dummies.columns)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:58.952714Z","iopub.execute_input":"2024-05-01T19:48:58.953076Z","iopub.status.idle":"2024-05-01T19:48:58.965245Z","shell.execute_reply.started":"2024-05-01T19:48:58.953051Z","shell.execute_reply":"2024-05-01T19:48:58.964302Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Suppress warnings for future versions to ensure compatibility\nwarnings.filterwarnings('ignore', category=FutureWarning)\n\n# Calculating various debt ratios to evaluate financial status\ndf_year['debt_credit_ratio'] = df_year['currdebt_22A'] / df_year['credit_usage_ratio']\ndf_year['debt_active_credits_ratio'] = df_year['currdebt_22A'] / df_year['active_credits']\ndf_year['debt_amount_ratio'] = df_year['currdebt_22A'] / df_year['credamount_770A']\n\n# Calculate percentage change in credit amount to identify trends\ndf_year['credamount_change_pct'] = df_year['credamount_770A'].pct_change(fill_method=None).fillna(0)\n\n# Encode categorical data in 'education_1103M_freq' to use in model training\neducation_dummies = pd.get_dummies(df_year['education_1103M_freq'], prefix='education')\n\n# Convert 'year' column to datetime format for time series analysis\ndf_year['year_datetime'] = pd.to_datetime(df_year['year'], format='%Y')\n\n# Group by year and calculate annual change percentage in credit usage\ndf_year['credit_usage_annual_change_pct'] = df_year.groupby('year_datetime')['credit_usage_ratio'].pct_change().fillna(0)\n\n# Append encoded education columns to the main DataFrame for more detailed analysis\ndf_year = pd.concat([df_year, education_dummies], axis=1)\n\n# Create interaction terms between education levels and credit amount for deeper analysis\nfor col in education_dummies.columns:\n df_year[f'interaction_{col}_credamount'] = df_year[col] * df_year['credamount_770A']\n\n# Additional financial ratios for comprehensive financial analysis\ndf_year['active_debt_credit_ratio'] = df_year['currdebt_22A'] / df_year['credamount_770A']\ndf_year['active_credits_credit_amount_ratio'] = df_year['active_credits'] / df_year['credamount_770A']\ndf_year['cluster_credit_usage_interaction'] = df_year['cluster'] * df_year['credit_usage_ratio']\n\n# Calculate annual change in active credits to monitor credit activity\ndf_year['annual_active_credits_change'] = df_year.groupby('year_datetime')['active_credits'].pct_change(fill_method=None).fillna(0)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:58.966433Z","iopub.execute_input":"2024-05-01T19:48:58.966692Z","iopub.status.idle":"2024-05-01T19:48:59.053497Z","shell.execute_reply.started":"2024-05-01T19:48:58.966671Z","shell.execute_reply":"2024-05-01T19:48:59.052663Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Assess the Importance of Features","metadata":{}},{"cell_type":"code","source":"df_year.info()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:59.054654Z","iopub.execute_input":"2024-05-01T19:48:59.054944Z","iopub.status.idle":"2024-05-01T19:48:59.108363Z","shell.execute_reply.started":"2024-05-01T19:48:59.054921Z","shell.execute_reply":"2024-05-01T19:48:59.107425Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Checking the percentage of NaNs per column\nnan_percentage = df_year.isna().mean() * 100\nprint(nan_percentage)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:59.109485Z","iopub.execute_input":"2024-05-01T19:48:59.109764Z","iopub.status.idle":"2024-05-01T19:48:59.157872Z","shell.execute_reply.started":"2024-05-01T19:48:59.109740Z","shell.execute_reply":"2024-05-01T19:48:59.156915Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Normalization of Column Names and Data","metadata":{}},{"cell_type":"code","source":"print(\"Oldest date:\", df_year['openingdate_313D'].min())\nprint(\"Most recent date:\", df_year['openingdate_313D'].max())\nprint(\"Oldest date:\", df_year['contractdate_551D'].min())\nprint(\"Most recent date:\", df_year['contractdate_551D'].max())\nprint(\"Oldest date:\", df_year['year_datetime'].min())\nprint(\"Most recent date:\", df_year['year_datetime'].max())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:59.159152Z","iopub.execute_input":"2024-05-01T19:48:59.159497Z","iopub.status.idle":"2024-05-01T19:48:59.169991Z","shell.execute_reply.started":"2024-05-01T19:48:59.159466Z","shell.execute_reply":"2024-05-01T19:48:59.168895Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\nimport tensorflow as tf\nfrom sklearn.impute import SimpleImputer\n\n# Replacing infinite values with NaN\ndf_year.replace([np.inf, -np.inf], np.nan, inplace=True)\n\n# Converting all column names to string\ndf_year.columns = df_year.columns.astype(str)\n\n# Check and transform 'openingdate_313D' column\nif 'openingdate_313D' in df_year.columns:\n df_year['openingdate_year'] = df_year['openingdate_313D'].dt.year\n df_year['openingdate_month'] = df_year['openingdate_313D'].dt.month\n df_year['openingdate_day'] = df_year['openingdate_313D'].dt.day\n\n# Check and transform 'contractdate_551D' column\nif 'contractdate_551D' in df_year.columns:\n df_year['contractdate_year'] = df_year['contractdate_551D'].dt.year\n df_year['contractdate_month'] = df_year['contractdate_551D'].dt.month\n df_year['contractdate_day'] = df_year['contractdate_551D'].dt.day\n\n# Check and transform 'year_datetime' column\nif 'year_datetime' in df_year.columns:\n df_year['year_datetime_year'] = df_year['year_datetime'].dt.year\n\n# Drop original date columns if they exist\ndate_columns_to_drop = ['openingdate_313D', 'contractdate_551D', 'year_datetime']\ndf_year.drop(columns=[col for col in date_columns_to_drop if col in df_year.columns], inplace=True)\n\n# Imputer for numeric values\nnum_imputer = SimpleImputer(strategy='median')\n\n# Preparing numeric columns for imputation\nnum_cols = df_year.select_dtypes(include=['float64', 'int32', 'float32']).columns\ndf_year[num_cols] = num_imputer.fit_transform(df_year[num_cols])\n\n# Identify boolean columns\nbool_cols = df_year.select_dtypes(include=['bool']).columns\n\n# Convert boolean columns to numeric values (0 and 1)\ndf_year[bool_cols] = df_year[bool_cols].astype(int)\n\n# Remaining categorical columns\ncat_cols = df_year.select_dtypes(include=['object', 'category']).columns\n\n# Handle categorical columns\nfor col in cat_cols:\n if df_year[col].dtype.name == 'category':\n # Add 'None' as a category if not already present\n if 'None' not in df_year[col].cat.categories:\n df_year[col] = df_year[col].cat.add_categories('None')\n df_year[col] = df_year[col].fillna('None')\n else:\n # Handle as object if not of type 'category'\n df_year[col] = df_year[col].fillna('None')\n\n# Imputation for categorical and boolean columns\ncat_imputer = SimpleImputer(strategy='most_frequent')\ndf_year[cat_cols.union(bool_cols)] = cat_imputer.fit_transform(df_year[cat_cols.union(bool_cols)])\n\n# Check if there are still missing values\nprint(df_year.isna().sum())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:48:59.171284Z","iopub.execute_input":"2024-05-01T19:48:59.171591Z","iopub.status.idle":"2024-05-01T19:49:00.526504Z","shell.execute_reply.started":"2024-05-01T19:48:59.171565Z","shell.execute_reply":"2024-05-01T19:49:00.525412Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(df_year.describe())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:49:00.527716Z","iopub.execute_input":"2024-05-01T19:49:00.528015Z","iopub.status.idle":"2024-05-01T19:49:00.764776Z","shell.execute_reply.started":"2024-05-01T19:49:00.527991Z","shell.execute_reply":"2024-05-01T19:49:00.763834Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for col in cat_cols:\n print(df_year[col].value_counts())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:49:00.766023Z","iopub.execute_input":"2024-05-01T19:49:00.766352Z","iopub.status.idle":"2024-05-01T19:49:00.864743Z","shell.execute_reply.started":"2024-05-01T19:49:00.766325Z","shell.execute_reply":"2024-05-01T19:49:00.863889Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Importance Analysis of Variables","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport tensorflow as tf\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nfrom sklearn.impute import SimpleImputer\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.model_selection import train_test_split\n\n# Copy of the original DataFrame for feature evaluation\ndf_year_copy = df_year.copy()\n\n# Specify columns for analysis\ncolumns_extended_copy = [\n 'credamount_770A', 'numactivecreds_622L', 'actualdpd_943P',\n 'contractenddate_991D', 'totaldebt_9A', 'currdebt_22A',\n 'contractenddate_year', 'contractenddate_month', 'contractenddate_day',\n 'contractenddate_weekday', 'education_1103M_freq',\n 'maritalst_385M_freq', 'is_outlier_credamount_770A', '0', '1', '2', '3',\n '4', '5', '6', '7', '8', '9', 'combined_feature', 'credit_usage_ratio',\n 'credamount_change', 'dpd_history', 'credit_duration', 'active_credits',\n 'credit_debt_ratio', 'cluster', 'year', 'debt_credit_ratio',\n 'debt_active_credits_ratio', 'debt_amount_ratio',\n 'credamount_change_pct', 'credit_usage_annual_change_pct',\n 'education_0.03141669710145314', 'education_0.3015369789320189',\n 'education_0.09019937673111733', 'education_0.5731261279753891',\n 'education_0.0037208192600214863',\n 'interaction_education_0.03141669710145314_credamount',\n 'interaction_education_0.3015369789320189_credamount',\n 'interaction_education_0.09019937673111733_credamount',\n 'interaction_education_0.5731261279753891_credamount',\n 'interaction_education_0.0037208192600214863_credamount',\n 'active_debt_credit_ratio', 'active_credits_credit_amount_ratio',\n 'cluster_credit_usage_interaction', 'annual_active_credits_change',\n 'openingdate_year', 'openingdate_month', 'openingdate_day',\n 'contractdate_year', 'contractdate_month', 'contractdate_day',\n 'year_datetime_year'\n]\n\n# Imputation and One-Hot Encoding in the copy DataFrame\nnum_cols_copy = df_year_copy[columns_extended_copy].select_dtypes(include=['int64', 'float64']).columns\ncat_cols_copy = df_year_copy[columns_extended_copy].select_dtypes(include=['object', 'category']).columns\n\nimputer_num_copy = SimpleImputer(strategy='mean')\ndf_year_copy[num_cols_copy] = imputer_num_copy.fit_transform(df_year_copy[num_cols_copy])\n\nimputer_cat_copy = SimpleImputer(strategy='most_frequent')\ndf_year_copy[cat_cols_copy] = imputer_cat_copy.fit_transform(df_year_copy[cat_cols_copy])\n\ndf_model_copy = pd.get_dummies(df_year_copy[columns_extended_copy], columns=cat_cols_copy)\nupdated_columns_copy = df_model_copy.columns\n\n# Split the copy data into training and test sets\nX_copy = df_model_copy.values\ny_copy = df_year_copy['amount_416A'].values\nX_train_year_copy, X_test_year_copy, y_train_year_copy, y_test_year_copy = train_test_split(X_copy, y_copy, test_size=0.2, random_state=42)\n\n# Normalize the copy data\nscaler_copy = StandardScaler()\nX_train_scaled_copy = scaler_copy.fit_transform(X_train_year_copy)\nX_test_scaled_copy = scaler_copy.transform(X_test_year_copy)\n\n# Convert the normalized data to tf.data.Dataset format\ntrain_dataset_copy = tf.data.Dataset.from_tensor_slices((X_train_scaled_copy, y_train_year_copy))\ntest_dataset_copy = tf.data.Dataset.from_tensor_slices((X_test_scaled_copy, y_test_year_copy))\n\n# Set up batch size and cache for the copy (increased to 32)\nbatch_size_copy = 32\ntrain_dataset_copy = train_dataset_copy.batch(batch_size_copy).cache()\ntest_dataset_copy = test_dataset_copy.batch(batch_size_copy).cache()\n\n# Create the model for the copy with the new learning rate and simplified architecture\ndef create_model_copy(input_shape):\n model_copy = tf.keras.Sequential([\n tf.keras.layers.Dense(32, activation='relu', input_shape=[input_shape]),\n tf.keras.layers.Dense(1)\n ])\n model_copy.compile(optimizer=tf.keras.optimizers.Adam(learning_rate=0.001),\n loss='mean_squared_error',\n metrics=[tf.keras.metrics.RootMeanSquaredError()])\n return model_copy\n\n# Detect and use the GPU for the copy\nif tf.test.is_gpu_available():\n print(\"Using the available GPU for training the copy.\")\n strategy_copy = tf.distribute.MirroredStrategy(devices=[\"/gpu:0\"])\nelse:\n print(\"GPU not available. Using CPU for the copy.\")\n strategy_copy = tf.distribute.get_strategy()\n\n# Create the model copy within the strategy scope\nwith strategy_copy.scope():\n model_copy = create_model_copy(input_shape=X_train_scaled_copy.shape[1])\n\n# Train the model copy (reduced to 50 epochs)\nhistory_copy = model_copy.fit(train_dataset_copy, epochs=50, validation_data=test_dataset_copy)\n\n# Evaluate the model copy on the test set\nloss_copy, rmse_copy = model_copy.evaluate(test_dataset_copy)\nprint(f'Test Loss (Copy): {loss_copy:.4f}, Test RMSE (Copy): {rmse_copy:.4f}')","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:49:00.866228Z","iopub.execute_input":"2024-05-01T19:49:00.866645Z","iopub.status.idle":"2024-05-01T19:59:44.995900Z","shell.execute_reply.started":"2024-05-01T19:49:00.866609Z","shell.execute_reply":"2024-05-01T19:59:44.994931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Obtain the weights from the first dense layer of the copy\nweights_first_layer_copy = model_copy.layers[0].get_weights()[0]\n\n# Calculate the mean of the absolute weights for each feature of the copy\nimportances_copy = np.mean(np.abs(weights_first_layer_copy), axis=1)\n\n# Number of importances and features of the copy\nnum_importances_copy = len(importances_copy)\nnum_features_copy = len(updated_columns_copy)\n\nprint(\"Number of importances (Copy):\", num_importances_copy)\nprint(\"Number of features (Copy):\", num_features_copy)\n\n# Check if the length of features and importances are equal for the copy\nif num_importances_copy == num_features_copy:\n # Adjust the features DataFrame for the copy\n features_df_copy = pd.DataFrame({\n 'Feature': updated_columns_copy,\n 'Importance': importances_copy\n }).sort_values(by='Importance', ascending=False)\n\n # Visualization of the top 20 most important features for the copy\n top_features_df_copy = features_df_copy.head(20)\n plt.figure(figsize=(20, 10))\n sns.barplot(data=top_features_df_copy, x='Importance', y='Feature')\n plt.title('Top 20 Feature Importances - Model Copy')\n plt.xlabel('Importance')\n plt.ylabel('Feature')\n plt.show()\nelse:\n print(\"Error: The number of importances and features are not equal for the copy.\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:44.997120Z","iopub.execute_input":"2024-05-01T19:59:44.997450Z","iopub.status.idle":"2024-05-01T19:59:45.456470Z","shell.execute_reply.started":"2024-05-01T19:59:44.997422Z","shell.execute_reply":"2024-05-01T19:59:45.455618Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Obtain the weights from the first dense layer\nweights_first_layer_copy = model_copy.layers[0].get_weights()[0]\n\n# Calculate the mean of the absolute weights for each feature\nimportances_copy = np.mean(np.abs(weights_first_layer_copy), axis=1)\n\n# Create a DataFrame with the feature importances\nfeatures_importance_copy = pd.DataFrame({\n 'Feature': updated_columns_copy, # Use the correct list of column names\n 'Importance': importances_copy # Use the correct importance values\n})\n\n# Sort the DataFrame by importance and select the top 20 most important features\ntop_20_features_copy = features_importance_copy.sort_values(by='Importance', ascending=False).head(20)\n\n# Print the top 20 most important features and their importances\nprint(top_20_features_copy)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:45.457768Z","iopub.execute_input":"2024-05-01T19:59:45.458068Z","iopub.status.idle":"2024-05-01T19:59:45.470338Z","shell.execute_reply.started":"2024-05-01T19:59:45.458043Z","shell.execute_reply":"2024-05-01T19:59:45.469410Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import shap\n\n# Choose a smaller subset of the training data for explanation\nX_sample = X_train_scaled_copy[:50] # Reduce the subset size\n\n# Create an 'explainer' object using the model\nexplainer = shap.DeepExplainer(model_copy, X_sample)\n\n# Calculate SHAP values with check_additivity turned off\nshap_values = explainer.shap_values(X_sample, check_additivity=False)\n\n# Feature names after normalization and One-Hot Encoding\nfeature_names_shap = updated_columns_copy\n\n# Visualize the feature importance for a specific instance\nshap.initjs()\ninstance_index = 0 # Choose the instance index for visualization\nshap.force_plot(explainer.expected_value[0], shap_values[0][instance_index], X_sample[instance_index], feature_names=feature_names_shap)\n\n# To visualize the average importance of features for the subset\nshap.summary_plot(shap_values, X_sample, feature_names=feature_names_shap)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:45.471629Z","iopub.execute_input":"2024-05-01T19:59:45.471966Z","iopub.status.idle":"2024-05-01T19:59:49.160815Z","shell.execute_reply.started":"2024-05-01T19:59:45.471939Z","shell.execute_reply":"2024-05-01T19:59:49.159871Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Calculate the average of SHAP values for each feature\n# Ensure 'shap_values[0]' is appropriate for your case\nshap_means_copy = np.abs(shap_values[0]).mean(axis=0)\n\n# Create a DataFrame with the importances\nfeatures_importance_shap = pd.DataFrame({\n 'Feature': updated_columns_copy, # Use the correct list of column names\n 'Mean SHAP Value': shap_means_copy\n})\n\n# Sort the DataFrame by importance and select the top 20 most important features\ntop_20_features_shap = features_importance_shap.sort_values(by='Mean SHAP Value', ascending=False).head(20)\n\n# Print the top 20 most important features and their importances\nprint(top_20_features_shap)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:49.162185Z","iopub.execute_input":"2024-05-01T19:59:49.162581Z","iopub.status.idle":"2024-05-01T19:59:49.173955Z","shell.execute_reply.started":"2024-05-01T19:59:49.162546Z","shell.execute_reply":"2024-05-01T19:59:49.172992Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Define a threshold for classifying payment history\ndelay_limit_days = 30 # Example: 30 days delay\n\n# Create a new column categorizing payment history based on delay\ndf_year['payment_category'] = df_year['actualdpd_943P'].apply(lambda x: 'Good' if x < delay_limit_days else 'Bad')\n\n# Visualize the relationship between the absence of contract end date and payment history\nsns.countplot(x='contractenddate_year', hue='payment_category', data=df_year)\nplt.title(\"Relationship Between Absence of Contract End Date and Payment History\")\nplt.xlabel(\"Absence of Contract End Date\")\nplt.ylabel(\"Number of Customers\")\nplt.show()\n\n# Detailed analysis using groupby\ngrouped_data = df_year.groupby(['contractenddate_year', 'payment_category']).size().reset_index(name='counts')\nprint(grouped_data)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:49.175471Z","iopub.execute_input":"2024-05-01T19:59:49.175797Z","iopub.status.idle":"2024-05-01T19:59:49.710897Z","shell.execute_reply.started":"2024-05-01T19:59:49.175771Z","shell.execute_reply":"2024-05-01T19:59:49.709919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Integrated Modeling and Predictive Optimization Process","metadata":{}},{"cell_type":"code","source":"# Create new columns based on existing columns\ndf_year['contractenddate_year_None'] = df_year['contractenddate_year']\ndf_year['contractenddate_month_None'] = df_year['contractenddate_month']\ndf_year['contractenddate_weekday_None'] = df_year['contractenddate_weekday']\n\n# Check the first rows to confirm correct creation of columns\nprint(df_year[['contractenddate_year_None', 'contractenddate_month_None', 'contractenddate_weekday_None']].head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:49.712368Z","iopub.execute_input":"2024-05-01T19:59:49.712820Z","iopub.status.idle":"2024-05-01T19:59:49.730473Z","shell.execute_reply.started":"2024-05-01T19:59:49.712772Z","shell.execute_reply":"2024-05-01T19:59:49.729457Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Regression Modeling and Validation for Time Series with TensorFlow","metadata":{}},{"cell_type":"code","source":"import tensorflow as tf\nfrom sklearn.model_selection import TimeSeriesSplit\nfrom sklearn.impute import SimpleImputer\nfrom sklearn.metrics import mean_squared_error, r2_score\nfrom tensorflow.keras.callbacks import EarlyStopping\nimport numpy as np\nimport pandas as pd\n\n# Columns for analysis\ncolumns_extended = [\n 'credamount_770A', 'totaldebt_9A', 'currdebt_22A', \n 'contractenddate_year_None', 'contractenddate_weekday_None',\n 'contractenddate_month_None', 'actualdpd_943P', 'numactivecreds_622L'\n]\n\ndf_sample = df_year.sample(frac=0.1, random_state=42)\n\n# Data Preparation and Imputation\ndf_model = df_year[columns_extended].copy()\n\n# Identifying numeric and categorical columns\nnum_cols = df_model.select_dtypes(include=['int64', 'float64', 'float32']).columns\ncat_cols = df_model.select_dtypes(include=['object', 'category']).columns\n\n# Imputation for numeric columns\nimputer_num = SimpleImputer(strategy='median')\ndf_model[num_cols] = imputer_num.fit_transform(df_model[num_cols])\n\n# Imputation for categorical columns\nif len(cat_cols) > 0:\n imputer_cat = SimpleImputer(strategy='most_frequent')\n df_model[cat_cols] = imputer_cat.fit_transform(df_model[cat_cols])\n\n# Apply One-Hot Encoding\nif len(cat_cols) > 0:\n df_model = pd.get_dummies(df_model, columns=cat_cols)\n\n# Data Split\nX = df_model.values\ny = df_year['amount_416A'].values\n\n# Sorting data by time\ndf_year.sort_values(['openingdate_year', 'openingdate_month', 'openingdate_day'], inplace=True)\n\n# Save pre-processed data in HDF5 format\ndf_model.to_hdf('df_model_copy_1.h5', key='df', mode='w')\n\n# Function to read from HDF5 in batches\ndef read_from_hdf5(file_path, batch_size):\n with pd.HDFStore(file_path, mode='r') as store:\n nrows = store.get_storer('df').nrows\n if nrows is None:\n nrows = 0\n for i in range(0, nrows, batch_size):\n df = store.select('df', start=i, stop=i + batch_size)\n yield df.values.astype(np.float32)\n\n# Batch Size\nbatch_size = 256\n\n# Defining the TimeSeriesSplit\ntscv = TimeSeriesSplit(n_splits=5)\n\n# Function to create the model\ndef create_model(input_shape):\n model = tf.keras.Sequential([\n tf.keras.layers.Dense(64, activation='relu', input_shape=[input_shape]),\n tf.keras.layers.Dense(32, activation='relu'),\n tf.keras.layers.Dense(1)\n ])\n model.compile(optimizer=tf.keras.optimizers.Adam(0.01),\n loss='mean_squared_error',\n metrics=[tf.keras.metrics.RootMeanSquaredError()])\n return model\n\n# GPU Configuration\nif tf.test.is_gpu_available():\n print(\"Using available GPU for training.\")\n strategy = tf.distribute.MirroredStrategy(devices=[\"/gpu:0\"])\nelse:\n print(\"GPU not available. Using CPU.\")\n strategy = tf.distribute.get_strategy()\n\n# Create the model\nwith strategy.scope():\n model = create_model(input_shape=X.shape[1])\n\n# Early Stopping\nearly_stopping = EarlyStopping(monitor='val_loss', patience=10, restore_best_weights=True)\n\n# Metrics for evaluation\ndef evaluate_model(model, dataset):\n y_true = []\n y_pred = []\n for X_batch, y_batch in dataset:\n y_true.extend(y_batch.numpy())\n y_pred.extend(model.predict(X_batch).flatten())\n mse = mean_squared_error(y_true, y_pred)\n r2 = r2_score(y_true, y_pred)\n return mse, r2\n\n# Loop for cross-validation with Early Stopping and Model Evaluation\nmse_scores = []\nr2_scores = []\ny_true_total = []\ny_pred_total = []\nfor train_index, test_index in tscv.split(X):\n X_train, X_test = X[train_index], X[test_index]\n y_train, y_test = y[train_index], y[test_index]\n \n # Preparing Datasets directly from arrays\n train_dataset = tf.data.Dataset.from_tensor_slices((X_train.astype(np.float32), y_train.astype(np.float32))).batch(batch_size)\n test_dataset = tf.data.Dataset.from_tensor_slices((X_test.astype(np.float32), y_test.astype(np.float32))).batch(batch_size)\n\n # Training and Evaluating the Model\n model.fit(train_dataset, epochs=100, validation_data=test_dataset, callbacks=[early_stopping])\n mse, r2 = evaluate_model(model, test_dataset)\n mse_scores.append(mse)\n r2_scores.append(r2)\n\n # Storing true values and predictions for residual analysis\n y_true_total.extend(y_test)\n y_pred_total.extend(model.predict(X_test.astype(np.float32)).flatten())\n\n# Cross-Validation Results\nprint(f\"Average MSE: {np.mean(mse_scores):.4f}, Standard Deviation: {np.std(mse_scores):.4f}\")\nprint(f\"Average R²: {np.mean(r2_scores):.4f}, Standard Deviation: {np.std(r2_scores):.4f}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T19:59:49.731758Z","iopub.execute_input":"2024-05-01T19:59:49.732543Z","iopub.status.idle":"2024-05-01T20:03:42.837821Z","shell.execute_reply.started":"2024-05-01T19:59:49.732508Z","shell.execute_reply":"2024-05-01T20:03:42.836715Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(model.summary())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:42.839018Z","iopub.execute_input":"2024-05-01T20:03:42.839315Z","iopub.status.idle":"2024-05-01T20:03:42.858203Z","shell.execute_reply.started":"2024-05-01T20:03:42.839288Z","shell.execute_reply":"2024-05-01T20:03:42.857163Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import tensorflow as tf\nfrom sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport numpy as np\n\n# Ensure that y_true_total and y_pred_total are as NumPy arrays\ny_true_total = np.array(y_true_total)\ny_pred_total = np.array(y_pred_total)\n\n# Residual Analysis\nresiduals = y_true_total - y_pred_total\nplt.figure(figsize=(10, 6))\nplt.scatter(y_pred_total, residuals)\nplt.title('Residual Analysis')\nplt.xlabel('Predictions')\nplt.ylabel('Residuals')\nplt.axhline(y=0, color='r', linestyle='-')\nplt.show()\n\n# Histogram of Predictions vs Actual Values\nplt.figure(figsize=(10, 6))\nsns.histplot(y_pred_total, bins=30, kde=True, color=\"skyblue\", label=\"Predictions\")\nsns.histplot(y_true_total, bins=30, kde=True, color=\"red\", label=\"Actual Values\")\nplt.title('Histogram of Predictions vs Actual Values')\nplt.xlabel('Values')\nplt.ylabel('Frequency')\nplt.legend()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:42.859847Z","iopub.execute_input":"2024-05-01T20:03:42.860185Z","iopub.status.idle":"2024-05-01T20:03:44.851635Z","shell.execute_reply.started":"2024-05-01T20:03:42.860158Z","shell.execute_reply":"2024-05-01T20:03:44.850657Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Scatter Plot to Compare Predictions and Actual Values","metadata":{}},{"cell_type":"code","source":"# Ensuring that X_test is a NumPy array with the correct data type\nX_test = np.asarray(X_test).astype(np.float32)\n\n# Making predictions with the model\ny_pred = model.predict(X_test)\n\n# Flatten y_pred, if necessary\ny_pred = y_pred.flatten() if y_pred.ndim > 1 else y_pred\n\n# Plot\nplt.scatter(y_test, y_pred, alpha=0.5, label='Model Predictions')\nplt.plot(y_test, y_test, color='red', label='Ideal Line')\nplt.xlabel('Actual Values')\nplt.ylabel('Model Predictions')\nplt.title('Comparison between Predictions and Actual Values')\nplt.legend()\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:44.852895Z","iopub.execute_input":"2024-05-01T20:03:44.853185Z","iopub.status.idle":"2024-05-01T20:03:46.818559Z","shell.execute_reply.started":"2024-05-01T20:03:44.853161Z","shell.execute_reply":"2024-05-01T20:03:46.817573Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Calculating the correlation matrix","metadata":{}},{"cell_type":"code","source":"# Calculating the correlation matrix\ncorrelation_matrix = df_model.corr()\n\n# Applying the threshold for correlations above 0.7 (in absolute value)\n# and removing correlations of a variable with itself\nhigh_corr = correlation_matrix[abs(correlation_matrix) > 0.7]\nnp.fill_diagonal(high_corr.values, np.nan)\n\n# Visualizing with a heatmap\nplt.figure(figsize=(10, 5))\nsns.heatmap(high_corr, annot=True, cmap='coolwarm', mask=high_corr.isnull())\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:46.819964Z","iopub.execute_input":"2024-05-01T20:03:46.820357Z","iopub.status.idle":"2024-05-01T20:03:47.770155Z","shell.execute_reply.started":"2024-05-01T20:03:46.820322Z","shell.execute_reply":"2024-05-01T20:03:47.769143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Residue Chart","metadata":{}},{"cell_type":"code","source":"residuals = y_test - y_pred.flatten() # Make sure to flatten y_pred if necessary\nplt.scatter(y_pred, residuals)\nplt.axhline(y=0, color='red', linestyle='--')\nplt.title('Residual Plot')\nplt.xlabel('Predicted Values')\nplt.ylabel('Residuals')\nplt.show()\n\n# Displaying the first 10 residuals\nprint(\"First 10 residuals:\", residuals[:10]) # Print the first 10 residuals from the test dataset","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:47.771174Z","iopub.execute_input":"2024-05-01T20:03:47.771448Z","iopub.status.idle":"2024-05-01T20:03:48.016027Z","shell.execute_reply.started":"2024-05-01T20:03:47.771425Z","shell.execute_reply":"2024-05-01T20:03:48.015050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Residual Analysis for Deep Learning Models","metadata":{}},{"cell_type":"code","source":"import tensorflow as tf\nimport numpy as np\nimport matplotlib.pyplot as plt\n\n# Mapping column names to indices\ncolumn_to_index = {col: i for i, col in enumerate(df_model.columns)}\n\n# Updated list of columns after One-Hot Encoding\ncolumns_extended_updated = [\n 'credamount_770A', 'totaldebt_9A', 'currdebt_22A', 'actualdpd_943P', 'numactivecreds_622L'\n] + [col for col in df_model.columns if 'contractenddate_year_None' in col] \\\n + [col for col in df_model.columns if 'contractenddate_weekday_None' in col] \\\n + [col for col in df_model.columns if 'contractenddate_month_None' in col]\n\n# Indices of selected columns\nselected_indices = [column_to_index[col] for col in columns_extended_updated]\n\n# Function to select columns in a numpy array\ndef select_columns_numpy(X):\n return X[:, selected_indices]\n\n# Apply column selection to the test set\nX_test_selected = select_columns_numpy(X_test)\n\n# Convert to tf.data.Dataset and apply batch\nbatch_size = 128\nX_test_selected_dataset = tf.data.Dataset.from_tensor_slices(X_test_selected)\nX_test_selected_dataset = X_test_selected_dataset.batch(batch_size)\n\n# Function to plot residual analysis\ndef plot_residuals(model, X, y, model_name):\n predictions = model.predict(X)\n residuals = y - predictions.flatten()\n\n plt.figure(figsize=(10, 6))\n plt.scatter(predictions, residuals)\n plt.title(f'Residual Analysis for {model_name}')\n plt.xlabel('Predictions')\n plt.ylabel('Residuals')\n plt.axhline(y=0, color='r', linestyle='-')\n plt.show()\n\n# Performing residual analysis for the model\nplot_residuals(model, X_test_selected_dataset, y_test, \"Model Name\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:48.017304Z","iopub.execute_input":"2024-05-01T20:03:48.017562Z","iopub.status.idle":"2024-05-01T20:03:48.812018Z","shell.execute_reply.started":"2024-05-01T20:03:48.017540Z","shell.execute_reply":"2024-05-01T20:03:48.810994Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Align the columns (features) of the test set with the training set.","metadata":{}},{"cell_type":"code","source":"print(\"Shape of X_copy:\", X_copy.shape) # For the first block of code\nprint(\"Shape of X:\", X.shape) # For the second block of code","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:48.813155Z","iopub.execute_input":"2024-05-01T20:03:48.813471Z","iopub.status.idle":"2024-05-01T20:03:48.818751Z","shell.execute_reply.started":"2024-05-01T20:03:48.813444Z","shell.execute_reply":"2024-05-01T20:03:48.817792Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print the final shapes of X_train and X_test\nprint(\"Final shape of X_train:\", X_train.shape)\nprint(\"Final shape of X_test:\", X_test.shape)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:48.819909Z","iopub.execute_input":"2024-05-01T20:03:48.820217Z","iopub.status.idle":"2024-05-01T20:03:48.830192Z","shell.execute_reply.started":"2024-05-01T20:03:48.820193Z","shell.execute_reply":"2024-05-01T20:03:48.829341Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print the shape of X_train and the input shape expected by the model\nprint(\"Shape of X_train:\", X_train.shape)\n\n# Assuming 'create_model' is the function that creates your model\ninput_shape = X_train.shape[1] # This sets the input shape based on X_train\nmodel = create_model(input_shape)\n\n# Now, you can access the 'input_shape' property\nprint(\"Expected input shape by the model:\", model.input_shape)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:48.831144Z","iopub.execute_input":"2024-05-01T20:03:48.831394Z","iopub.status.idle":"2024-05-01T20:03:48.890491Z","shell.execute_reply.started":"2024-05-01T20:03:48.831373Z","shell.execute_reply":"2024-05-01T20:03:48.889588Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Validation and Testing","metadata":{}},{"cell_type":"code","source":"from sklearn.impute import SimpleImputer\nfrom sklearn.preprocessing import StandardScaler\nfrom scipy import stats\nimport pandas as pd\nimport numpy as np\nimport os\nimport gc\n\n# Path to the 'train' directory\ndata_path = '/kaggle/input/home-credit-credit-risk-model-stability/'\n\n# Selected variables based on missing data analysis and relevance\nselected_variables = [\n 'credamount_770A', 'numactivecreds_622L', 'education_1103M', 'maritalst_385M',\n 'actualdpd_943P', 'contractenddate_991D', 'amount_416A', 'openingdate_313D', 'totaldebt_9A',\n 'contractdate_551D', 'currdebt_22A'\n]\n\n# Updating the dictionary to store file paths only for selected variables\nfiles_by_variable_depth = {var: {'train_depth1': [], 'test_depth1': [], 'train_depth2': [], 'test_depth2': []} for var in selected_variables}\n\n# Traversing through CSV files and storing paths for selected variables\nfor dirname, _, filenames in os.walk(data_path):\n for filename in filenames:\n if filename.endswith('.csv'):\n file_path = os.path.join(dirname, filename)\n depth = 'depth2' if '_2' in filename else 'depth1'\n file_type = 'train' if 'train' in file_path else 'test'\n df_sample = pd.read_csv(file_path, nrows=1)\n for var in selected_variables:\n if var in df_sample.columns:\n key = f\"{file_type}_{depth}\"\n files_by_variable_depth[var][key].append(file_path)\n del df_sample # Discard df_sample after use\n gc.collect() # Garbage collection to free up memory\n\ndef load_and_concatenate(files, variable, dtype, batch_size=50000):\n if not files:\n return pd.DataFrame() # Returns an empty DataFrame if no files are found\n df_list = []\n for file in files:\n for chunk in pd.read_csv(file, usecols=[variable], chunksize=batch_size):\n if dtype == 'categorical':\n chunk[variable] = chunk[variable].astype('category')\n elif dtype == 'date':\n chunk[variable] = pd.to_datetime(chunk[variable])\n else:\n chunk[variable] = chunk[variable].fillna(0).astype(dtype)\n df_list.append(chunk)\n gc.collect() # Garbage collection after each chunk\n return pd.concat(df_list, ignore_index=True)\n\n# Defining extended columns for analysis\ncolumns_extended = [\n \"credit_usage_ratio\", \"credit_duration\", \"credamount_change_pct\", \n \"interaction_education_0.3015369789320189_credamount\", \"totaldebt_9A\", \n \"credamount_change\", \"credamount_770A\", \"contractdate_day\", \"combined_feature\",\n \"currdebt_22A\", \"interaction_education_0.5731261279753891_credamount\"\n]\n\ndef apply_transformations(df):\n # Replacing NaN with the median for numerical columns\n for col in df.select_dtypes(include=['int64', 'float64', 'float32']).columns:\n df[col].fillna(df[col].median(), inplace=True)\n \n # Replacing NaN with the most frequent category for categorical columns\n for col in df.select_dtypes(include=['object', 'category']).columns:\n df[col].fillna(df[col].mode()[0], inplace=True)\n\n # Applying specific transformations\n if 'credamount_770A' in df.columns:\n df['credamount_770A'] = df['credamount_770A'].apply(lambda x: max(x, 0))\n df['credamount_770A'], _ = stats.yeojohnson(df['credamount_770A'])\n\n # Converting column names to string\n df.columns = df.columns.astype(str)\n\n # Normalizing numerical columns\n numeric_cols = df.select_dtypes(include=['int64', 'float64', 'float32']).columns\n scaler = StandardScaler()\n df[numeric_cols] = scaler.fit_transform(df[numeric_cols])\n\n return df\n\n# Initialize 'train_columns' outside of the function to store training columns\ntrain_columns = None\n\ndef process_data(df, mode='train'):\n global train_columns\n\n df_copy = df.copy()\n \n # Convert date columns to datetime type\n date_columns = ['openingdate_313D', 'contractdate_551D']\n for col in date_columns:\n if col in df_copy.columns:\n df_copy[col] = pd.to_datetime(df_copy[col])\n \n # Apply general transformations (imputation and normalization)\n df_copy = apply_transformations(df_copy)\n\n # Apply specific transformations for the test set\n if mode == 'test':\n # Calculates 'credit_usage_ratio' and handles NaNs\n if 'amount_416A' in df_copy.columns and 'totaldebt_9A' in df_copy.columns:\n mask = (df_copy['totaldebt_9A'] != 0) # Check if 'totaldebt_9A' is not zero\n df_copy.loc[mask, 'credit_usage_ratio'] = df_copy.loc[mask, 'amount_416A'] / df_copy.loc[mask, 'totaldebt_9A']\n df_copy['credit_usage_ratio'].replace([np.inf, -np.inf], np.nan, inplace=True)\n df_copy['credit_usage_ratio'].fillna(0, inplace=True) # Fill missing values with 0\n else:\n df_copy['credit_usage_ratio'] = 0 # or another default value\n\n # Calculates 'credamount_change_pct' and handles NaNs\n if 'credamount_770A' in df_copy.columns and 'contractdate_551D' in df_copy.columns:\n df_copy.sort_values(['contractdate_551D', 'openingdate_313D'], inplace=True)\n df_copy['credamount_change_pct'] = df_copy.groupby('contractdate_551D')['credamount_770A'].pct_change().fillna(0)\n df_copy['credamount_change_pct'].replace([np.inf, -np.inf], np.nan, inplace=True)\n df_copy['credamount_change_pct'].fillna(df_copy['credamount_change_pct'].median(), inplace=True)\n else:\n df_copy['credamount_change_pct'] = 0 # or another default value\n\n # Calculates 'credit_duration' and handles NaNs\n if 'openingdate_313D' in df_copy.columns and 'contractdate_551D' in df_copy.columns:\n df_copy['credit_duration'] = df_copy.groupby('contractdate_551D')['openingdate_313D'].transform(lambda x: (x.max() - x.min()).days)\n df_copy['credit_duration'].fillna(0, inplace=True) # Fill missing values with 0\n else:\n df_copy['credit_duration'] = 0 # or another default value \n\n # Imputation and transformations\n df_processed = apply_transformations(df_copy)\n \n # Imputation for numerical columns\n numeric_cols = df_copy.select_dtypes(include=['int64', 'float64', 'float32']).columns\n for col in numeric_cols:\n df_copy[col].fillna(df_copy[col].median(), inplace=True)\n\n # Imputation for categorical columns\n cat_cols = df_copy.select_dtypes(include=['object', 'category']).columns\n for col in cat_cols:\n df_copy[col].fillna(df_copy[col].mode()[0], inplace=True)\n\n # Normalization of numerical columns\n scaler = StandardScaler()\n df_copy[numeric_cols] = scaler.fit_transform(df_copy[numeric_cols])\n\n # Select only the columns that exist in 'columns_extended'\n existing_columns = [col for col in columns_extended if col in df_copy.columns]\n df_copy = df_copy[existing_columns]\n\n # Apply One-Hot Encoding\n if mode == 'train':\n df_processed = pd.get_dummies(df_copy)\n train_columns = df_processed.columns # Set train_columns here\n else:\n if train_columns is None:\n raise ValueError(\"train_columns is not set. Run process_data in 'train' mode first.\")\n df_processed = pd.get_dummies(df_copy)\n missing_cols = set(train_columns) - set(df_processed.columns)\n for col in missing_cols:\n df_processed[col] = 0\n df_processed = df_processed[train_columns]\n\n return df_processed\n\n# Load training data from DataFrame combined_df\ndf_train = combined_df.copy()\n\n# Process the training data\ndf_train_transformed = process_data(df_train, mode='train')\n\n# Load test data\ntest_files = []\nfor var in selected_variables:\n test_files.extend(files_by_variable_depth[var]['test_depth1'])\n test_files.extend(files_by_variable_depth[var]['test_depth2'])\ntest_files = list(set(test_files)) # Remove duplicate file paths\n\ndf_test = pd.DataFrame()\nfor file in test_files:\n df_file = pd.read_csv(file)\n available_columns = [col for col in selected_variables if col in df_file.columns]\n df_file = df_file[available_columns]\n df_test = pd.concat([df_test, df_file], ignore_index=True)\n\n# Process the test data\ndf_test_transformed = process_data(df_test, mode='test')\n\n# Verify if all training columns are present in the test\nmissing_cols = set(train_columns) - set(df_test_transformed.columns)\nfor col in missing_cols:\n df_test_transformed[col] = 0\n\n# Align the column order with the training\ndf_test_transformed = df_test_transformed[train_columns]\n\n# Re-check for remaining NaNs\nif df_test_transformed.isnull().any().any():\n print(\"Columns with remaining NaNs:\", df_test_transformed.columns[df_test_transformed.isnull().any()].tolist())\n raise ValueError(\"The dataset still contains NaN values after transformation.\")\n\n# Prepare X_train and X_test from the transformed DataFrames\nX_train = df_train_transformed.values\nX_test = df_test_transformed.values\n\n# Check the first lines of the transformed DataFrames\nprint(\"First lines of the transformed training DataFrame:\")\nprint(df_train_transformed.head())\nprint(\"\\nFirst lines of the transformed test DataFrame:\")\nprint(df_test_transformed.head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:03:48.891923Z","iopub.execute_input":"2024-05-01T20:03:48.892202Z","iopub.status.idle":"2024-05-01T20:05:12.079434Z","shell.execute_reply.started":"2024-05-01T20:03:48.892178Z","shell.execute_reply":"2024-05-01T20:05:12.078313Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# After processing both datasets\nprint(\"Training columns:\", df_train_transformed.columns)\nprint(\"Test columns:\", df_test_transformed.columns)\n\n# Check for differences in the columns\nprint(\"Columns missing in test that are present in training:\", set(df_train_transformed.columns) - set(df_test_transformed.columns))\nprint(\"Columns missing in training that are present in test:\", set(df_test_transformed.columns) - set(df_train_transformed.columns))\n\n# Check the number of columns\nprint(\"Number of columns in training:\", len(df_train_transformed.columns))\nprint(\"Number of columns in test:\", len(df_test_transformed.columns))","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.080666Z","iopub.execute_input":"2024-05-01T20:05:12.080964Z","iopub.status.idle":"2024-05-01T20:05:12.091325Z","shell.execute_reply.started":"2024-05-01T20:05:12.080938Z","shell.execute_reply":"2024-05-01T20:05:12.089740Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Find missing columns in the test set\nmissing_cols = set(df_train_transformed.columns) - set(df_test_transformed.columns)\nif missing_cols:\n print(\"\\nMissing columns in the test set:\")\n print(list(missing_cols))\nelse:\n print(\"\\nAll training set columns are present in the test set.\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.092535Z","iopub.execute_input":"2024-05-01T20:05:12.093142Z","iopub.status.idle":"2024-05-01T20:05:12.112668Z","shell.execute_reply.started":"2024-05-01T20:05:12.093106Z","shell.execute_reply":"2024-05-01T20:05:12.111424Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check for missing values in the transformed test set\nif df_test_transformed.isnull().any().any():\n print(\"Columns with remaining missing values in the test set:\")\n print(df_test_transformed.columns[df_test_transformed.isnull().any()].tolist())\nelse:\n print(\"There are no missing values in the transformed test set.\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.114294Z","iopub.execute_input":"2024-05-01T20:05:12.114606Z","iopub.status.idle":"2024-05-01T20:05:12.125349Z","shell.execute_reply.started":"2024-05-01T20:05:12.114578Z","shell.execute_reply":"2024-05-01T20:05:12.124365Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Submission File Preparation","metadata":{}},{"cell_type":"code","source":"# Saving the trained neural network model\nmodel_filename = 'neural_network_model.keras' # .keras extension for native Keras format\nmodel.save(model_filename)\n\nprint(f\"Neural network model saved as '{model_filename}'\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.126466Z","iopub.execute_input":"2024-05-01T20:05:12.127132Z","iopub.status.idle":"2024-05-01T20:05:12.187067Z","shell.execute_reply.started":"2024-05-01T20:05:12.127098Z","shell.execute_reply":"2024-05-01T20:05:12.186102Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Re-load your test dataset to verify\ntest_csv_path = '/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_deposit_1.csv'\ndf_test_csv = pd.read_csv(test_csv_path)\nprint(\"Number of rows in re-loaded df_test_csv:\", len(df_test_csv))","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.188677Z","iopub.execute_input":"2024-05-01T20:05:12.189057Z","iopub.status.idle":"2024-05-01T20:05:12.197376Z","shell.execute_reply.started":"2024-05-01T20:05:12.189022Z","shell.execute_reply":"2024-05-01T20:05:12.196345Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check the expected input shape of the model\nprint(\"Expected input shape:\", model.input_shape)\n\n# Check the columns used during training\nprint(\"Train columns:\", train_columns)\n\n# Create an empty DataFrame with the same columns and order as the training DataFrame\ndf_test_aligned = pd.DataFrame(columns=df_train_transformed.columns)\n\n# Fill the DataFrame with available test data\nfor col in df_test_transformed.columns:\n if col in df_test_aligned.columns:\n df_test_aligned[col] = df_test_transformed[col]\n\n# Fill missing columns with zeros\ndf_test_aligned.fillna(0, inplace=True)\n\n# Check if the aligned test DataFrame has the same columns and order as the training DataFrame\nassert df_test_aligned.columns.tolist() == df_train_transformed.columns.tolist(), \"Test columns do not match training columns\"\n\n# Convert the aligned test DataFrame to a numpy array\nX_test = df_test_aligned.values\n\n# Resize X_test to match the expected shape of the model\nX_test = X_test.reshape((-1,) + model.input_shape[1:])\n\n# Check the dimensions of X_test to confirm it is correct\nprint(\"Shape of X_test:\", X_test.shape)\n\n# Try predicting again\npredictions = model.predict(X_test)\n\n# Create a DataFrame with the predictions\npredictions_df = pd.DataFrame({'score': predictions.flatten()})\n\n# Add the 'case_id' column to the predictions DataFrame based on the index of df_test_csv\npredictions_df['case_id'] = df_test_csv['case_id']\n\n# Merge the df_test_csv DataFrame with the predictions DataFrame based on the 'case_id' column\nsubmission_df = pd.merge(df_test_csv[['case_id']], predictions_df, on='case_id', how='left')\n\n# Remove potential duplicates keeping the first entry\nsubmission_df.drop_duplicates(subset='case_id', keep='first', inplace=True)\n\n# Check for missing values\nif submission_df['score'].isnull().any():\n submission_df['score'].fillna(submission_df['score'].mean(), inplace=True) # Fill missing values with mean score\n\n# Ensure scores are within valid range (0 to 1)\nsubmission_df['score'] = submission_df['score'].clip(0, 1)\n\n# Save the submission DataFrame\nsubmission_df.to_csv('submission.csv', index=False)\n\nprint(\"Submission file 'submission.csv' created\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.199174Z","iopub.execute_input":"2024-05-01T20:05:12.199572Z","iopub.status.idle":"2024-05-01T20:05:12.367507Z","shell.execute_reply.started":"2024-05-01T20:05:12.199536Z","shell.execute_reply":"2024-05-01T20:05:12.366536Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Display the first rows of the simple submission DataFrame\nprint(\"First rows of the simple submission:\")\nprint(submission_df.head())","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.368711Z","iopub.execute_input":"2024-05-01T20:05:12.369022Z","iopub.status.idle":"2024-05-01T20:05:12.375623Z","shell.execute_reply.started":"2024-05-01T20:05:12.368996Z","shell.execute_reply":"2024-05-01T20:05:12.374662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Check for the presence of all required 'case_id's\ntest_case_ids = df_test_csv['case_id'].unique()\nsubmission_case_ids = submission_df['case_id'].unique()\n\nmissing_case_ids = set(test_case_ids) - set(submission_case_ids)\nif missing_case_ids:\n print(f\"Warning: {len(missing_case_ids)} 'case_id' from the test set are missing in the submission file.\")\n print(\"Missing 'case_id's:\", missing_case_ids)\nelse:\n print(\"All 'case_id's from the test set are present in the submission file.\")\n\n# Verify if 'score' values are within the valid range\nif submission_df['score'].between(0, 1).all():\n print(\"All 'score' values are within the valid range (0 to 1).\")\nelse:\n print(\"Warning: There are 'score' values outside the valid range (0 to 1).\")\n invalid_scores = submission_df[~submission_df['score'].between(0, 1)]\n print(\"Invalid 'score' values:\")\n print(invalid_scores)","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:05:12.377111Z","iopub.execute_input":"2024-05-01T20:05:12.377398Z","iopub.status.idle":"2024-05-01T20:05:12.391966Z","shell.execute_reply.started":"2024-05-01T20:05:12.377374Z","shell.execute_reply":"2024-05-01T20:05:12.391006Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"missing_case_ids = set(df_test_csv['case_id']) - set(submission_df['case_id'])\nif missing_case_ids:\n print(f\"Warning: {len(missing_case_ids)} 'case_id' from the test set are missing in the submission file.\")\n print(\"Missing 'case_id's:\", missing_case_ids)\nelse:\n print(\"All 'case_id's from the test set are present in the submission file.\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:19:12.289049Z","iopub.execute_input":"2024-05-01T20:19:12.290202Z","iopub.status.idle":"2024-05-01T20:19:12.296757Z","shell.execute_reply.started":"2024-05-01T20:19:12.290152Z","shell.execute_reply":"2024-05-01T20:19:12.295803Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"duplicate_case_ids = submission_df[submission_df.duplicated(subset=['case_id'])]['case_id'].tolist()\nif duplicate_case_ids:\n print(f\"Warning: {len(duplicate_case_ids)} duplicate 'case_id' found in the submission file.\")\n print(\"Duplicate 'case_id's:\", duplicate_case_ids)\nelse:\n print(\"No duplicate 'case_id's found in the submission file.\")","metadata":{"execution":{"iopub.status.busy":"2024-05-01T20:19:29.187312Z","iopub.execute_input":"2024-05-01T20:19:29.187699Z","iopub.status.idle":"2024-05-01T20:19:29.195543Z","shell.execute_reply.started":"2024-05-01T20:19:29.187669Z","shell.execute_reply":"2024-05-01T20:19:29.194438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}