loan_defaults_001_data_clean_prep.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyNr9+UWbBv9ZE24QtPDGYH0",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/micah-shull/loan_defaults/blob/main/loan_defaults_001_data_clean_prep.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Data Description\n",
        "\n",
        "This dataset contains information on clients' credit card behavior, provided by a financial institution in Taiwan. The target variable is `default_payment_next_month`, which indicates whether the client defaulted on their credit card payment the next month.\n",
        "\n",
        "url = https://archive.ics.uci.edu/dataset/350/default+of+credit+card+clients\n",
        "\n",
        "#### Variables:\n",
        "\n",
        "- **ID**: ID of each client.\n",
        "- **LIMIT_BAL**: Amount of given credit in NT dollars (includes individual and family/supplementary credit).\n",
        "- **SEX**: Gender (1 = male, 2 = female).\n",
        "- **EDUCATION**: Education level (1 = graduate school, 2 = university, 3 = high school, 4 = others, 5 = unknown, 6 = unknown).\n",
        "- **MARRIAGE**: Marital status (1 = married, 2 = single, 3 = others).\n",
        "- **AGE**: Age in years.\n",
        "\n",
        "#### Payment History (PAY_X):\n",
        "- **PAY_0**: Repayment status in September 2005 (-1 = pay duly, 1 = payment delay for one month, 2 = payment delay for two months, ... 8 = payment delay for eight months, 9 = payment delay for nine months and above).\n",
        "- **PAY_2**: Repayment status in August 2005.\n",
        "- **PAY_3**: Repayment status in July 2005.\n",
        "- **PAY_4**: Repayment status in June 2005.\n",
        "- **PAY_5**: Repayment status in May 2005.\n",
        "- **PAY_6**: Repayment status in April 2005.\n",
        "\n",
        "#### Bill Statement Amount (BILL_AMT_X):\n",
        "- **BILL_AMT1**: Amount of bill statement in September 2005 (NT dollars).\n",
        "- **BILL_AMT2**: Amount of bill statement in August 2005 (NT dollars).\n",
        "- **BILL_AMT3**: Amount of bill statement in July 2005 (NT dollars).\n",
        "- **BILL_AMT4**: Amount of bill statement in June 2005 (NT dollars).\n",
        "- **BILL_AMT5**: Amount of bill statement in May 2005 (NT dollars).\n",
        "- **BILL_AMT6**: Amount of bill statement in April 2005 (NT dollars).\n",
        "\n",
        "#### Previous Payment Amount (PAY_AMT_X):\n",
        "- **PAY_AMT1**: Amount of previous payment in September 2005 (NT dollars).\n",
        "- **PAY_AMT2**: Amount of previous payment in August 2005 (NT dollars).\n",
        "- **PAY_AMT3**: Amount of previous payment in July 2005 (NT dollars).\n",
        "- **PAY_AMT4**: Amount of previous payment in June 2005 (NT dollars).\n",
        "- **PAY_AMT5**: Amount of previous payment in May 2005 (NT dollars).\n",
        "- **PAY_AMT6**: Amount of previous payment in April 2005 (NT dollars).\n",
        "\n",
        "- **default_payment_next_month**: Default payment indicator (1 = yes, 0 = no).\n",
        "\n",
        "#### Pay as Category\n",
        "\n",
        "-2: No consumption; -1: Paid in full; 0: The use of revolving credit; 1 = payment delay for one month; 2 = payment delay for two months; . . .; 8 = payment delay for eight months; 9 = payment delay for nine months and above.\n",
        "\n",
        "#### Explanation for Feature Reordering:\n",
        "The bill statement and payment amounts are listed in reverse chronological order in the dataset. To ensure that the feature names match the actual sequence of events, we reverse the column names for `BILL_AMT` and `PAY_AMT` features so that they correctly represent the time sequence from April 2005 to September 2005.\n",
        "\n"
      ],
      "metadata": {
        "id": "NjJGNa0zVRt4"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Data Utils Script Overview\n",
        "\n",
        "This script is designed to perform thorough preprocessing of a dataset used for credit risk modeling, focusing on handling categorical features, maintaining chronological consistency, and ensuring data integrity. The preprocessing steps are organized to address common issues in real-world datasets, such as inconsistent feature names, unhandled categorical variables, and missing or redundant data.\n",
        "\n",
        "### Key Steps and Their Importance\n",
        "\n",
        "1. **Data Loading and Column Cleaning**:\n",
        "   - **Data Loading**: The dataset is loaded from a specified URL using `pd.read_excel()`. Loading the data this way ensures it is read into a pandas DataFrame.\n",
        "   - **Column Name Cleaning**: The script standardizes column names by converting them to lowercase and replacing spaces with underscores.\n",
        "\n",
        "2. **Removing Redundant Columns**:\n",
        "   - **Removing the ID Column**: The script removes the `id` column, which is purely an identifier and holds no predictive value.\n",
        "\n",
        "3. **Renaming and Reordering Columns**:\n",
        "   - **Renaming Payment and Bill Columns**: Payment and billing columns are renamed to include both the month number and name (e.g., `bill_amt_9_september`). This renaming aligns feature names with their chronological sequence, making the dataset more intuitive and easier to interpret.\n",
        "   - **Reordering Columns by Calendar Month**: The columns are reordered to follow a logical progression from April to September. Maintaining this order is critical for time-based features, as it ensures that temporal relationships (e.g., trends over time) are preserved and clearly represented in the dataset.\n",
        "\n",
        "4. **Handling Categorical Variables**:\n",
        "   - **Converting `sex`, `marriage`, and `education` Columns**: These categorical variables are converted to human-readable text labels (e.g., `Male`, `Female`, `Married`, `Single`) and stored as categorical data types. The `education` column is also treated as an ordinal variable, where the levels represent an ordered hierarchy (e.g., from high school to graduate school).\n",
        "   - **Grouping Rare Categories**: In the `education` and `marriage` columns, rare categories (like unknown or others) are grouped into broader categories. This approach helps avoid sparse categories that could negatively impact model performance, especially in tree-based models.\n",
        "\n",
        "5. **Labeling and Ordering Payment Delay Columns**:\n",
        "   - **Mapping Payment Delay Values**: The payment delay columns (e.g., `pay_delay_9_september`) are mapped to descriptive text labels (e.g., `Paid in full`, `1 month delay`). This improves readability and interpretability, making it easier to understand the severity of delays in repayment.\n",
        "   - **Converting to Ordinal Categories**: Since payment delays are inherently ordinal (e.g., a 2-month delay is worse than a 1-month delay), the script converts these columns to ordinal categories with a predefined order. Handling these as ordinal variables ensures that models can leverage the ordering information effectively.\n",
        "\n",
        "6. **Ensuring Data Integrity and Validation**:\n",
        "   - **Column Integrity Check**: The script validates that all expected columns are present and properly named. This prevents issues related to missing or incorrectly named columns, which could cause errors or inconsistencies during further analysis.\n",
        "   - **Data Type Validation**: The script checks that columns intended to be categorical or ordinal are correctly set as such. This validation ensures that downstream operations (like feature engineering or model training) function as intended without unexpected data type errors.\n",
        "\n",
        "7. **Error Handling and Logging**:\n",
        "   - The script uses comprehensive error handling and logging throughout each preprocessing step. Logging provides real-time feedback on the preprocessing pipeline, allowing for easy troubleshooting if issues arise. Error handling ensures that the script gracefully recovers from unexpected problems without crashing, preserving data integrity and avoiding incomplete outputs.\n",
        "\n",
        "### Why These Steps Are Important\n",
        "\n",
        "1. **Consistency and Clarity**: Standardizing column names and ensuring a logical order for time-based features (like billing amounts and payment delays) are vital for maintaining consistency. Clear and consistent feature names make the dataset easier to work with, reducing errors during analysis.\n",
        "\n",
        "2. **Handling Real-World Data Issues**: Grouping rare categories, converting categorical variables to meaningful labels, and dealing with ordinal data correctly address common challenges in real-world datasets. These steps are necessary for models to perform well, as they prevent issues like sparse categories or misinterpreted relationships between variables.\n",
        "\n",
        "3. **Model Readiness**: By converting relevant columns to categorical and ordinal types, the script ensures the dataset is optimized for machine learning algorithms, which often require specific data types to function correctly. For example, tree-based models like decision trees and random forests benefit from properly ordered ordinal variables.\n",
        "\n",
        "4. **Error Prevention and Robustness**: The validation checks and logging mechanisms provide safety nets, ensuring that the preprocessing pipeline catches and reports issues before they can cause problems downstream. This level of robustness is critical for production-level pipelines where data consistency and reliability are paramount.\n",
        "\n"
      ],
      "metadata": {
        "id": "yiFvXx3_XFeU"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "script_content=r'''\n",
        "import pandas as pd\n",
        "import seaborn as sns\n",
        "import matplotlib.pyplot as plt\n",
        "import logging\n",
        "from sklearn.metrics import classification_report\n",
        "\n",
        "# Configure logging\n",
        "logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')\n",
        "\n",
        "# Define primary, bill, and pay columns\n",
        "primary_columns = ['limit_bal', 'sex', 'education', 'marriage', 'age']\n",
        "bill_columns = ['bill_amt_4_april', 'bill_amt_5_may', 'bill_amt_6_june', 'bill_amt_7_july', 'bill_amt_8_august', 'bill_amt_9_september']\n",
        "pay_columns = ['pay_amt_4_april', 'pay_amt_5_may', 'pay_amt_6_june', 'pay_amt_7_july', 'pay_amt_8_august', 'pay_amt_9_september']\n",
        "ordinal_columns = ['education', 'pay_delay_4_april',  'pay_delay_5_may', 'pay_delay_6_june', 'pay_delay_7_july', 'pay_delay_8_august', 'pay_delay_9_september']\n",
        "\n",
        "def load_data_from_url(url):\n",
        "    try:\n",
        "        df = pd.read_excel(url, header=1)\n",
        "        logging.info(\"Data loaded successfully from URL.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error loading data from URL: {e}\")\n",
        "        return None\n",
        "    return df\n",
        "\n",
        "def split_features_target(df, target):\n",
        "    try:\n",
        "        X = df.drop(columns=[target])\n",
        "        y = df[target]\n",
        "        logging.info(\"Features and target split successfully.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error splitting features and target: {e}\")\n",
        "        return None, None\n",
        "    return X, y\n",
        "\n",
        "\n",
        "def clean_column_names(df):\n",
        "    try:\n",
        "        df.columns = [col.lower().replace(' ', '_') for col in df.columns]\n",
        "        logging.info(\"Column names cleaned successfully.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error cleaning column names: {e}\")\n",
        "    return df\n",
        "\n",
        "def remove_id_column(df):\n",
        "    if 'id' in df.columns:\n",
        "        df = df.drop(columns=['id'])\n",
        "        logging.info(\"ID column removed.\")\n",
        "    return df\n",
        "\n",
        "def process_sex_column(df):\n",
        "    try:\n",
        "        if 'sex' in df.columns:\n",
        "            df['sex'] = df['sex'].replace({1: 'Male', 2: 'Female'})\n",
        "            df['sex'] = df['sex'].astype('category')\n",
        "            logging.info(\"Sex column processed and converted to categorical successfully.\")\n",
        "        else:\n",
        "            logging.warning(\"Sex column not found in DataFrame.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error processing sex column: {e}\")\n",
        "    return df\n",
        "\n",
        "def process_marriage_column(df):\n",
        "    try:\n",
        "        if 'marriage' in df.columns:\n",
        "            df['marriage'] = df['marriage'].replace({0: 'Unknown/Others', 3: 'Unknown/Others'})\n",
        "            df['marriage'] = df['marriage'].replace({1: 'Married', 2: 'Single'})\n",
        "            df['marriage'] = pd.Categorical(df['marriage'], categories=['Married', 'Single', 'Unknown/Others'], ordered=False)\n",
        "            logging.info(\"Marriage column processed and converted to categorical successfully.\")\n",
        "        else:\n",
        "            logging.warning(\"Marriage column not found in DataFrame.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error processing marriage column: {e}\")\n",
        "    return df\n",
        "\n",
        "\n",
        "def rename_pay_columns(df):\n",
        "    try:\n",
        "        # Only rename if the original columns exist\n",
        "        if all(col in df.columns for col in ['pay_0', 'pay_2', 'pay_3', 'pay_4', 'pay_5', 'pay_6']):\n",
        "            pay_columns_new_names = {\n",
        "                'pay_0': 'pay_delay_9_september',\n",
        "                'pay_2': 'pay_delay_8_august',\n",
        "                'pay_3': 'pay_delay_7_july',\n",
        "                'pay_4': 'pay_delay_6_june',\n",
        "                'pay_5': 'pay_delay_5_may',\n",
        "                'pay_6': 'pay_delay_4_april'\n",
        "            }\n",
        "            df = df.rename(columns=pay_columns_new_names)\n",
        "            logging.info(\"Pay delay columns renamed successfully.\")\n",
        "        else:\n",
        "            logging.warning(\"Some or all of the expected pay columns are missing. Renaming skipped.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error renaming pay delay columns: {e}\")\n",
        "    return df\n",
        "\n",
        "def rename_bill_and_payment_columns(df):\n",
        "    try:\n",
        "        # Correct renaming of bill_amt and pay_amt columns\n",
        "        bill_amt_new_names = {\n",
        "            'bill_amt1': 'bill_amt_9_september',\n",
        "            'bill_amt2': 'bill_amt_8_august',\n",
        "            'bill_amt3': 'bill_amt_7_july',\n",
        "            'bill_amt4': 'bill_amt_6_june',\n",
        "            'bill_amt5': 'bill_amt_5_may',\n",
        "            'bill_amt6': 'bill_amt_4_april'\n",
        "        }\n",
        "\n",
        "        pay_amt_new_names = {\n",
        "            'pay_amt1': 'pay_amt_9_september',\n",
        "            'pay_amt2': 'pay_amt_8_august',\n",
        "            'pay_amt3': 'pay_amt_7_july',\n",
        "            'pay_amt4': 'pay_amt_6_june',\n",
        "            'pay_amt5': 'pay_amt_5_may',\n",
        "            'pay_amt6': 'pay_amt_4_april'\n",
        "        }\n",
        "\n",
        "        df = df.rename(columns={**bill_amt_new_names, **pay_amt_new_names})\n",
        "        logging.info(\"Bill and payment columns renamed successfully.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error renaming bill and payment columns: {e}\")\n",
        "    return df\n",
        "\n",
        "\n",
        "def label_pay_columns(df, pay_columns):\n",
        "    try:\n",
        "        # Only label the pay_delay_* columns, not pay_amt_*\n",
        "        pay_labels = {\n",
        "            -2: \"No consumption\",\n",
        "            -1: \"Paid in full\",\n",
        "            0: \"Revolving credit\",\n",
        "            1: \"1 month delay\",\n",
        "            2: \"2 months delay\",\n",
        "            3: \"3 months delay\",\n",
        "            4: \"4 months delay\",\n",
        "            5: \"5 months delay\",\n",
        "            6: \"6 months delay\",\n",
        "            7: \"7 months delay\",\n",
        "            8: \"8 months delay\",\n",
        "            9: \"9+ months delay\"\n",
        "        }\n",
        "\n",
        "        for col in pay_columns:\n",
        "            # Ensure we're only mapping the delay columns and not payment amount columns\n",
        "            if 'pay_delay' in col and col in df.columns:\n",
        "                df[col] = df[col].map(pay_labels)\n",
        "                logging.info(f\"Column {col} labeled successfully.\")\n",
        "            else:\n",
        "                logging.error(f\"Column {col} not found in DataFrame.\")\n",
        "\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error labeling pay columns: {e}\")\n",
        "    return df\n",
        "\n",
        "def convert_pay_columns_to_ordinal(df, pay_columns):\n",
        "    try:\n",
        "        pay_order = [\n",
        "            \"No consumption\",  # -2\n",
        "            \"Paid in full\",    # -1\n",
        "            \"Revolving credit\", # 0\n",
        "            \"1 month delay\",   # 1\n",
        "            \"2 months delay\",  # 2\n",
        "            \"3 months delay\",  # 3\n",
        "            \"4 months delay\",  # 4\n",
        "            \"5 months delay\",  # 5\n",
        "            \"6 months delay\",  # 6\n",
        "            \"7 months delay\",  # 7\n",
        "            \"8 months delay\",  # 8\n",
        "            \"9+ months delay\"  # 9\n",
        "        ]\n",
        "\n",
        "        for col in pay_columns:\n",
        "            # Ensure only delay columns are converted to categorical\n",
        "            if 'pay_delay' in col and col in df.columns:\n",
        "                df[col] = pd.Categorical(df[col], categories=pay_order, ordered=True)\n",
        "                logging.info(f\"Column {col} converted to ordinal successfully.\")\n",
        "            else:\n",
        "                logging.error(f\"Column {col} not found in DataFrame.\")\n",
        "\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error converting pay columns to ordinal categories: {e}\")\n",
        "    return df\n",
        "\n",
        "\n",
        "def convert_ordinal_to_category(df, ordinal_columns):\n",
        "    try:\n",
        "        for col in ordinal_columns:\n",
        "            if col in df.columns:\n",
        "                df[col] = df[col].astype('category')\n",
        "                logging.info(f\"Column {col} converted to category successfully.\")\n",
        "            else:\n",
        "                logging.error(f\"Column {col} not found in DataFrame.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error converting ordinal columns to category: {e}\")\n",
        "    return df\n",
        "\n",
        "def convert_education_to_ordinal(df):\n",
        "    try:\n",
        "        # Explicitly map all values to handle known categories\n",
        "        education_mapping = {\n",
        "            1: 'Graduate School',\n",
        "            2: 'University',\n",
        "            3: 'High School',\n",
        "            4: 'Other/Unknown',\n",
        "            5: 'Other/Unknown',\n",
        "            6: 'Other/Unknown',\n",
        "            0: 'Other/Unknown'  # Handle the 0 value as well\n",
        "        }\n",
        "\n",
        "        df['education'] = df['education'].replace(education_mapping)\n",
        "\n",
        "        # Define the order of education categories\n",
        "        education_order = [\n",
        "            \"Other/Unknown\",    # Grouped 0, 4, 5, 6 together\n",
        "            \"High School\",      # 3\n",
        "            \"University\",       # 2\n",
        "            \"Graduate School\"   # 1\n",
        "        ]\n",
        "\n",
        "        if 'education' in df.columns:\n",
        "            # Convert the education column to a categorical type with the specified order\n",
        "            df['education'] = pd.Categorical(df['education'], categories=education_order, ordered=True)\n",
        "            logging.info(\"Education column converted to ordinal categories successfully.\")\n",
        "        else:\n",
        "            logging.error(\"Education column not found in DataFrame.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error converting education column to ordinal categories: {e}\")\n",
        "    return df\n",
        "\n",
        "\n",
        "# Utility and validation functions first\n",
        "def check_column_integrity(df, expected_columns):\n",
        "    missing_columns = [col for col in expected_columns if col not in df.columns]\n",
        "    if missing_columns:\n",
        "        logging.error(f\"Missing columns: {missing_columns}\")\n",
        "    else:\n",
        "        logging.info(\"All expected columns are present.\")\n",
        "\n",
        "def validate_data_types(df, ordinal_columns):\n",
        "    for col in ordinal_columns:\n",
        "        if col in df.columns:\n",
        "            if not pd.api.types.is_categorical_dtype(df[col]):\n",
        "                logging.warning(f\"Column {col} is not correctly set as categorical.\")\n",
        "            else:\n",
        "                logging.info(f\"Column {col} is correctly set as categorical with the following categories: {df[col].cat.categories}\")\n",
        "        else:\n",
        "            logging.error(f\"Column {col} not found in DataFrame.\")\n",
        "    logging.info(\"Data type validation complete.\")\n",
        "\n",
        "def reorder_columns(df):\n",
        "    try:\n",
        "        # Define the order of the pay_delay columns in chronological order\n",
        "        pay_delay_order = [\n",
        "            'pay_delay_4_april',\n",
        "            'pay_delay_5_may',\n",
        "            'pay_delay_6_june',\n",
        "            'pay_delay_7_july',\n",
        "            'pay_delay_8_august',\n",
        "            'pay_delay_9_september'\n",
        "        ]\n",
        "\n",
        "        # Combine primary, bill, pay, and pay_delay columns with other columns\n",
        "        other_columns = [col for col in df.columns if col not in primary_columns + bill_columns + pay_columns + pay_delay_order]\n",
        "        df = df[primary_columns + bill_columns + pay_columns + pay_delay_order + other_columns]\n",
        "        logging.info(\"Columns reordered successfully.\")\n",
        "    except Exception as e:\n",
        "        logging.error(f\"Error reordering columns: {e}\")\n",
        "    return df\n",
        "\n",
        "\n",
        "\n",
        "def load_and_preprocess_data(url, categorical_columns, target):\n",
        "    df = load_data_from_url(url)\n",
        "    if df is not None:\n",
        "        try:\n",
        "            df = clean_column_names(df)\n",
        "            df = remove_id_column(df)\n",
        "            df = rename_pay_columns(df)\n",
        "            df = rename_bill_and_payment_columns(df)\n",
        "            df = label_pay_columns(df, [col for col in df.columns if 'pay_delay' in col])\n",
        "            df = convert_pay_columns_to_ordinal(df, [col for col in df.columns if 'pay_delay' in col])\n",
        "            df = reorder_columns(df)\n",
        "            df = convert_education_to_ordinal(df)\n",
        "            df = process_sex_column(df)  # Process the sex column separately\n",
        "            df = process_marriage_column(df)  # Process the marriage column separately\n",
        "            df = convert_ordinal_to_category(df, ordinal_columns)\n",
        "\n",
        "            # Run validation checks\n",
        "            check_column_integrity(df, primary_columns + bill_columns + pay_columns + ordinal_columns)\n",
        "            validate_data_types(df, ordinal_columns)\n",
        "\n",
        "            X, y = split_features_target(df, target)\n",
        "            logging.info(\"Data loaded and preprocessed successfully.\")\n",
        "            return X, y\n",
        "        except Exception as e:\n",
        "            logging.error(f\"Error in data preprocessing: {e}\")\n",
        "            return None, None\n",
        "    return None, None\n",
        "\n",
        "\n",
        "def check_categorical_order(df, columns):\n",
        "    for col in columns:\n",
        "        if pd.api.types.is_categorical_dtype(df[col]):\n",
        "            print(f\"Column: {col}\")\n",
        "            print(f\"Categories: {df[col].cat.categories}\")\n",
        "            print(f\"Ordered: {df[col].cat.ordered}\\n\")\n",
        "        else:\n",
        "            print(f\"Column: {col} is not categorical.\\n\")\n",
        "\n",
        "\n",
        "'''\n",
        "\n",
        "# Write the script to a file\n",
        "with open(\"loan_data_utils.py\", \"w\") as file:\n",
        "    file.write(script_content)\n",
        "\n",
        "print(\"Script successfully written to loan_data_utils.py\")\n",
        "# Reload script to make functions available for use\n",
        "import importlib\n",
        "import loan_data_utils\n",
        "importlib.reload(loan_data_utils)\n",
        "\n",
        "from loan_data_utils import *\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "I0n51ttV4A8y",
        "outputId": "6dc25356-7f21-44bd-fc9c-257e7f0a0201"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Script successfully written to loan_data_utils.py\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Load Clean & Preprocess Data"
      ],
      "metadata": {
        "id": "iq5wllY6e3eM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns\n",
        "from loan_data_utils import load_and_preprocess_data, primary_columns, bill_columns, pay_columns, ordinal_columns, check_categorical_order\n",
        "from eda_utils import data_overview, plot_univariate_distributions, plot_class_distribution\n",
        "import logging\n",
        "\n",
        "# Define your URL, categorical columns, and target\n",
        "url = \"https://archive.ics.uci.edu/ml/machine-learning-databases/00350/default%20of%20credit%20card%20clients.xls\"\n",
        "categorical_columns = ['education', 'sex', 'marriage']\n",
        "target = 'default_payment_next_month'\n",
        "\n",
        "# Load and preprocess data\n",
        "X, y = load_and_preprocess_data(url, categorical_columns, target)\n",
        "\n",
        "# print data overview\n",
        "data_overview(X)\n",
        "\n",
        "# Drop duplicate rows\n",
        "X = X.drop_duplicates()\n",
        "\n",
        "# Confirm the duplicates are removed\n",
        "print(f\"Number of Duplicate Rows After Dropping: {X.duplicated().sum()}\")\n",
        "\n",
        "# List of columns to check\n",
        "pay_cols = [col for col in X.columns if col.startswith('pay_delay')]\n",
        "# Check the order of categorical columns\n",
        "check_categorical_order(X, categorical_columns + pay_cols)"
      ],
      "metadata": {
        "id": "6HxbQMjvTcYL",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "28f49022-a962-44a7-a475-a03edad6ae45"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Missing Values: 0\n",
            "Missing Percentage: 0.0\n",
            "Number of Duplicate Rows: 56\n",
            "\n",
            "<class 'pandas.core.frame.DataFrame'>\n",
            "RangeIndex: 30000 entries, 0 to 29999\n",
            "Data columns (total 23 columns):\n",
            " #   Column                 Non-Null Count  Dtype   \n",
            "---  ------                 --------------  -----   \n",
            " 0   limit_bal              30000 non-null  int64   \n",
            " 1   sex                    30000 non-null  category\n",
            " 2   education              30000 non-null  category\n",
            " 3   marriage               30000 non-null  category\n",
            " 4   age                    30000 non-null  int64   \n",
            " 5   bill_amt_4_april       30000 non-null  int64   \n",
            " 6   bill_amt_5_may         30000 non-null  int64   \n",
            " 7   bill_amt_6_june        30000 non-null  int64   \n",
            " 8   bill_amt_7_july        30000 non-null  int64   \n",
            " 9   bill_amt_8_august      30000 non-null  int64   \n",
            " 10  bill_amt_9_september   30000 non-null  int64   \n",
            " 11  pay_amt_4_april        30000 non-null  int64   \n",
            " 12  pay_amt_5_may          30000 non-null  int64   \n",
            " 13  pay_amt_6_june         30000 non-null  int64   \n",
            " 14  pay_amt_7_july         30000 non-null  int64   \n",
            " 15  pay_amt_8_august       30000 non-null  int64   \n",
            " 16  pay_amt_9_september    30000 non-null  int64   \n",
            " 17  pay_delay_4_april      30000 non-null  category\n",
            " 18  pay_delay_5_may        30000 non-null  category\n",
            " 19  pay_delay_6_june       30000 non-null  category\n",
            " 20  pay_delay_7_july       30000 non-null  category\n",
            " 21  pay_delay_8_august     30000 non-null  category\n",
            " 22  pay_delay_9_september  30000 non-null  category\n",
            "dtypes: category(9), int64(14)\n",
            "memory usage: 3.5 MB\n",
            "Number of Duplicate Rows After Dropping: 0\n",
            "Column: education\n",
            "Categories: Index(['Other/Unknown', 'High School', 'University', 'Graduate School'], dtype='object')\n",
            "Ordered: True\n",
            "\n",
            "Column: sex\n",
            "Categories: Index(['Female', 'Male'], dtype='object')\n",
            "Ordered: False\n",
            "\n",
            "Column: marriage\n",
            "Categories: Index(['Married', 'Single', 'Unknown/Others'], dtype='object')\n",
            "Ordered: False\n",
            "\n",
            "Column: pay_delay_4_april\n",
            "Categories: Index(['No consumption', 'Paid in full', 'Revolving credit', '1 month delay',\n",
            "       '2 months delay', '3 months delay', '4 months delay', '5 months delay',\n",
            "       '6 months delay', '7 months delay', '8 months delay',\n",
            "       '9+ months delay'],\n",
            "      dtype='object')\n",
            "Ordered: True\n",
            "\n",
            "Column: pay_delay_5_may\n",
            "Categories: Index(['No consumption', 'Paid in full', 'Revolving credit', '1 month delay',\n",
            "       '2 months delay', '3 months delay', '4 months delay', '5 months delay',\n",
            "       '6 months delay', '7 months delay', '8 months delay',\n",
            "       '9+ months delay'],\n",
            "      dtype='object')\n",
            "Ordered: True\n",
            "\n",
            "Column: pay_delay_6_june\n",
            "Categories: Index(['No consumption', 'Paid in full', 'Revolving credit', '1 month delay',\n",
            "       '2 months delay', '3 months delay', '4 months delay', '5 months delay',\n",
            "       '6 months delay', '7 months delay', '8 months delay',\n",
            "       '9+ months delay'],\n",
            "      dtype='object')\n",
            "Ordered: True\n",
            "\n",
            "Column: pay_delay_7_july\n",
            "Categories: Index(['No consumption', 'Paid in full', 'Revolving credit', '1 month delay',\n",
            "       '2 months delay', '3 months delay', '4 months delay', '5 months delay',\n",
            "       '6 months delay', '7 months delay', '8 months delay',\n",
            "       '9+ months delay'],\n",
            "      dtype='object')\n",
            "Ordered: True\n",
            "\n",
            "Column: pay_delay_8_august\n",
            "Categories: Index(['No consumption', 'Paid in full', 'Revolving credit', '1 month delay',\n",
            "       '2 months delay', '3 months delay', '4 months delay', '5 months delay',\n",
            "       '6 months delay', '7 months delay', '8 months delay',\n",
            "       '9+ months delay'],\n",
            "      dtype='object')\n",
            "Ordered: True\n",
            "\n",
            "Column: pay_delay_9_september\n",
            "Categories: Index(['No consumption', 'Paid in full', 'Revolving credit', '1 month delay',\n",
            "       '2 months delay', '3 months delay', '4 months delay', '5 months delay',\n",
            "       '6 months delay', '7 months delay', '8 months delay',\n",
            "       '9+ months delay'],\n",
            "      dtype='object')\n",
            "Ordered: True\n",
            "\n"
          ]
        }
      ]
    }
  ]
}