data_cleaning.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATA CLEANING"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mô Tả"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Làm sạch và xét tính hợp lý để lọc dữ liệu."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Script"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "CN_INC = 'Income'  # income column name\n",
    "CN_AGE = 'Age'  # age column name\n",
    "CN_EXP = 'Experience'  # experience column name\n",
    "CN_MS = 'Married/Single'  # married or single column name\n",
    "CN_HO = 'House_Ownership'  # house ownership column name\n",
    "CN_CO = 'Car_Ownership'  # car ownership column name\n",
    "CN_PRO = 'Profession'  # profession column name\n",
    "CN_STA = 'STATE'  # state column name\n",
    "CN_RF = 'Risk_Flag'  # risk flag column name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "MS_VLD_VAS = ['married', 'single']  # married or single valid values\n",
    "HO_VLD_VAS = ['rented', 'norent noown', 'owned']  # house ownership valid values\n",
    "CO_VLD_VAS = ['no', 'yes']  # car ownership valid values\n",
    "STA_VLD_VAS = [\n",
    "    'Andhra Pradesh',\n",
    "    'Arunachal Pradesh',\n",
    "    'Assam',\n",
    "    'Bihar',\n",
    "    'Chhattisgarh',\n",
    "    'Goa',\n",
    "    'Gujarat',\n",
    "    'Haryana',\n",
    "    'Himachal Pradesh',\n",
    "    'Jharkhand',\n",
    "    'Karnataka',\n",
    "    'Kerala',\n",
    "    'Madhya Pradesh',\n",
    "    'Maharashtra',\n",
    "    'Manipur',\n",
    "    'Meghalaya',\n",
    "    'Mizoram',\n",
    "    'Nagaland',\n",
    "    'Odisha',\n",
    "    'Punjab',\n",
    "    'Rajasthan',\n",
    "    'Sikkim',\n",
    "    'Tamil Nadu',\n",
    "    'Telangana',\n",
    "    'Tripura',\n",
    "    'Uttar Pradesh',\n",
    "    'Uttarakhand',\n",
    "    'West Bengal',\n",
    "    'Andaman and Nicobar',\n",
    "    'Chandigarh',\n",
    "    'Dadra and Nagar Haveli and Daman and Diu',\n",
    "    'Delhi',\n",
    "    'Jammu and Kashmir',\n",
    "    'Lakshadweep',\n",
    "    'Ladakh',\n",
    "    'Puducherry'\n",
    "]  # state valid values\n",
    "RF_VLD_VAS = [1, 0]  # risk flag valid values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Common"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Refactor\n",
    "def refactor(s):\n",
    "    return s.str.strip().str.replace('_', ' ').str.split().str.join(' ').str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Try parse to int\n",
    "def try_prs_int(s):\n",
    "    return pd.to_numeric(s, errors='coerce').astype('Int64')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('India_bank_loan_risk.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "income = try_prs_int(df[CN_INC])\n",
    "age = try_prs_int(df[CN_AGE])\n",
    "experience = try_prs_int(df[CN_EXP])\n",
    "marr_sngl = refactor(df[CN_MS])\n",
    "hse_or = refactor(df[CN_HO])\n",
    "car_or = refactor(df[CN_CO])\n",
    "profession = refactor(df[CN_PRO]).str.capitalize()\n",
    "state = refactor(df[CN_STA]).str.title().str.replace(' And ', ' and ')\n",
    "risk_flag = try_prs_int(df[CN_RF])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "valids = (income.notna()\n",
    "          & age.notna()\n",
    "          & experience.notna()\n",
    "          & marr_sngl.notna()\n",
    "          & hse_or.notna()\n",
    "          & car_or.notna()\n",
    "          & profession.notna()\n",
    "          & state.notna()\n",
    "          & risk_flag.notna()\n",
    "          & ~marr_sngl.eq('')\n",
    "          & ~hse_or.eq('')\n",
    "          & ~car_or.eq('')\n",
    "          & ~profession.eq('')\n",
    "          & ~state.eq('')\n",
    "          & income.ge(0)\n",
    "          & age.ge(18)\n",
    "          & experience.ge(0)\n",
    "          & ((age - experience).ge(18))\n",
    "          & marr_sngl.isin(MS_VLD_VAS)\n",
    "          & hse_or.isin(HO_VLD_VAS)\n",
    "          & car_or.isin(CO_VLD_VAS)\n",
    "          & state.isin(STA_VLD_VAS)\n",
    "          & risk_flag.isin(RF_VLD_VAS))\n",
    "invalids = ~valids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "income = income.loc[valids]\n",
    "age = age.loc[valids]\n",
    "experience = experience.loc[valids]\n",
    "marr_sngl = marr_sngl.loc[valids]\n",
    "hse_or = hse_or.loc[valids]\n",
    "car_or = car_or.loc[valids]\n",
    "profession = profession.loc[valids]\n",
    "state = state.loc[valids]\n",
    "risk_flag = risk_flag.loc[valids]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame({\n",
    "    'Income': income,\n",
    "    'Age': age,\n",
    "    'Experience': experience,\n",
    "    'Married/Single': marr_sngl,\n",
    "    'House Ownership': hse_or,\n",
    "    'Car Ownership': car_or,\n",
    "    'Profession': profession,\n",
    "    'State': state,\n",
    "    'Risk Flag': risk_flag\n",
    "}).to_csv('data_valid.csv')\n",
    "df.loc[invalids].to_csv('data_invalid.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}