Projeto_Inadiplencia.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b7182ad2-0510-43f0-b7ee-24608fce266e",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Análise do risco de inadimplência dos mutuários\n",
    "\n",
    "Seu projeto é preparar um relatório para a divisão de empréstimos de um banco. Você precisará descobrir se o estado civil de um cliente e o número de filhos têm impacto sobre se ele deixará de pagar um empréstimo. O banco já tem alguns dados sobre a capacidade de crédito dos clientes.\n",
    "\n",
    "Seu relatório será considerado ao criar uma **pontuação de crédito** de um cliente em potencial. A **contagem de crédito** é usada para avaliar a capacidade de um devedor em potencial de pagar seu empréstimo.\n",
    "\n",
    "Primeiramente, os dados serão avaliados a procurá de dados ausentes, problemas nas colunas, dados com problemas, dados com o tipo errado, dados duplicados e ausentes. Depois de identificados os problemas, eles serão corrigidos para que a as hipóteses sejam testadas.\n",
    "\n",
    "Com a tabela pronta para a ánalise serão feitas as perguntas:\n",
    "- Existe alguma relação entre ter filhos e pagar um empréstimo em dia?\n",
    "- Existe alguma relação entre o estado civil e o pagamento de um empréstimo no prazo estipulado?\n",
    "- Existe uma relação entre o nível de renda e o pagamento de um empréstimo no prazo?\n",
    "- Como as diferentes finalidades do empréstimo afetam o pagamento pontual do empréstimo?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ffd25e40-1c94-4a70-8dec-79e86c6589ca",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Abra o arquivo de dados e veja as informações gerais.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a00bab76-3c9f-45eb-9556-c1862f8440b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "try:\n",
    "    df = pd.read_csv('credit_scoring_eng.csv')\n",
    "    \n",
    "except:\n",
    "    df = pd.read_csv('/datasets/credit_scoring_eng.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed6c9e4f-7b37-46d7-abb5-a17b6a30d5de",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Exploração de dados"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4020db80-4178-4609-96e1-4bc2fb033f49",
   "metadata": {},
   "source": [
    "**Descrição dos dados**\n",
    "- `children` - o número de crianças na família\n",
    "- `days_employed` - experiência de trabalho em dias\n",
    "- `dob_years` - idade do cliente em anos\n",
    "- `education` - educação do cliente\n",
    "- `education_id` - identificador de educação\n",
    "- `family_status` - estado civil do cliente\n",
    "- `family_status_id` - identificador de estado civil\n",
    "- `gender` - gênero do cliente\n",
    "- `income_type` - tipo de emprego\n",
    "- `debt` - havia alguma dívida no pagamento do empréstimo\n",
    "- `total_income` - renda mensal\n",
    "- `purpose` - o objetivo de obter um empréstimo\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "442fb97a-69a6-4dad-8bf1-ea594cae55e5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21525 entries, 0 to 21524\n",
      "Data columns (total 12 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   children          21525 non-null  int64  \n",
      " 1   days_employed     19351 non-null  float64\n",
      " 2   dob_years         21525 non-null  int64  \n",
      " 3   education         21525 non-null  object \n",
      " 4   education_id      21525 non-null  int64  \n",
      " 5   family_status     21525 non-null  object \n",
      " 6   family_status_id  21525 non-null  int64  \n",
      " 7   gender            21525 non-null  object \n",
      " 8   income_type       21525 non-null  object \n",
      " 9   debt              21525 non-null  int64  \n",
      " 10  total_income      19351 non-null  float64\n",
      " 11  purpose           21525 non-null  object \n",
      "dtypes: float64(2), int64(5), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "# Vamos ver quantas linhas e colunas nosso conjunto de dados tem\n",
    "df.info()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "60406db1-3608-4f20-ab65-a06e7e4e9f8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['children', 'days_employed', 'dob_years', 'education', 'education_id',\n",
       "       'family_status', 'family_status_id', 'gender', 'income_type', 'debt',\n",
       "       'total_income', 'purpose'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ad84e217-a18c-4c7a-a74a-c0410795a025",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>children</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>dob_years</th>\n",
       "      <th>education</th>\n",
       "      <th>education_id</th>\n",
       "      <th>family_status</th>\n",
       "      <th>family_status_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>income_type</th>\n",
       "      <th>debt</th>\n",
       "      <th>total_income</th>\n",
       "      <th>purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>-8437.673028</td>\n",
       "      <td>42</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>0</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>40620.102</td>\n",
       "      <td>purchase of the house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>-4024.803754</td>\n",
       "      <td>36</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>17932.802</td>\n",
       "      <td>car purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>-5623.422610</td>\n",
       "      <td>33</td>\n",
       "      <td>Secondary Education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>23341.752</td>\n",
       "      <td>purchase of the house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>-4124.747207</td>\n",
       "      <td>32</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>42820.568</td>\n",
       "      <td>supplementary education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>340266.072047</td>\n",
       "      <td>53</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>retiree</td>\n",
       "      <td>0</td>\n",
       "      <td>25378.572</td>\n",
       "      <td>to have a wedding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>-926.185831</td>\n",
       "      <td>27</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>0</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>40922.170</td>\n",
       "      <td>purchase of the house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>-2879.202052</td>\n",
       "      <td>43</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>0</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>38484.156</td>\n",
       "      <td>housing transactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>-152.779569</td>\n",
       "      <td>50</td>\n",
       "      <td>SECONDARY EDUCATION</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>21731.829</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>-6929.865299</td>\n",
       "      <td>35</td>\n",
       "      <td>BACHELOR'S DEGREE</td>\n",
       "      <td>0</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>15337.093</td>\n",
       "      <td>having a wedding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>-2188.756445</td>\n",
       "      <td>41</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>23108.150</td>\n",
       "      <td>purchase of the house for my family</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   children  days_employed  dob_years            education  education_id  \\\n",
       "0         1   -8437.673028         42    bachelor's degree             0   \n",
       "1         1   -4024.803754         36  secondary education             1   \n",
       "2         0   -5623.422610         33  Secondary Education             1   \n",
       "3         3   -4124.747207         32  secondary education             1   \n",
       "4         0  340266.072047         53  secondary education             1   \n",
       "5         0    -926.185831         27    bachelor's degree             0   \n",
       "6         0   -2879.202052         43    bachelor's degree             0   \n",
       "7         0    -152.779569         50  SECONDARY EDUCATION             1   \n",
       "8         2   -6929.865299         35    BACHELOR'S DEGREE             0   \n",
       "9         0   -2188.756445         41  secondary education             1   \n",
       "\n",
       "       family_status  family_status_id gender income_type  debt  total_income  \\\n",
       "0            married                 0      F    employee     0     40620.102   \n",
       "1            married                 0      F    employee     0     17932.802   \n",
       "2            married                 0      M    employee     0     23341.752   \n",
       "3            married                 0      M    employee     0     42820.568   \n",
       "4  civil partnership                 1      F     retiree     0     25378.572   \n",
       "5  civil partnership                 1      M    business     0     40922.170   \n",
       "6            married                 0      F    business     0     38484.156   \n",
       "7            married                 0      M    employee     0     21731.829   \n",
       "8  civil partnership                 1      F    employee     0     15337.093   \n",
       "9            married                 0      M    employee     0     23108.150   \n",
       "\n",
       "                               purpose  \n",
       "0                purchase of the house  \n",
       "1                         car purchase  \n",
       "2                purchase of the house  \n",
       "3              supplementary education  \n",
       "4                    to have a wedding  \n",
       "5                purchase of the house  \n",
       "6                 housing transactions  \n",
       "7                            education  \n",
       "8                     having a wedding  \n",
       "9  purchase of the house for my family  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# vamos exibir as primeiras 10 linhas\n",
    "df.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "604e52f9-f89b-4828-b934-dd820246e800",
   "metadata": {},
   "source": [
    "A partir da primeira visualização foram encontrados alguns problemas nos dados de certas colunas: \n",
    "- A coluna **`days_employed`** possui números negativos e alguns valores em quantidade incorreta, também possui valores ausentes. \n",
    "- A coluna **`education`**  possui diferentes nomes escritos de formas diferentes.\n",
    "- A coluna **`total_income`** possui valores ausentes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1ea64cb4-3d1d-4ea6-af8e-62aca615249a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>children</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>dob_years</th>\n",
       "      <th>education_id</th>\n",
       "      <th>family_status_id</th>\n",
       "      <th>debt</th>\n",
       "      <th>total_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>21525.000000</td>\n",
       "      <td>19351.000000</td>\n",
       "      <td>21525.000000</td>\n",
       "      <td>21525.000000</td>\n",
       "      <td>21525.000000</td>\n",
       "      <td>21525.000000</td>\n",
       "      <td>19351.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.538908</td>\n",
       "      <td>63046.497661</td>\n",
       "      <td>43.293380</td>\n",
       "      <td>0.817236</td>\n",
       "      <td>0.972544</td>\n",
       "      <td>0.080883</td>\n",
       "      <td>26787.568355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.381587</td>\n",
       "      <td>140827.311974</td>\n",
       "      <td>12.574584</td>\n",
       "      <td>0.548138</td>\n",
       "      <td>1.420324</td>\n",
       "      <td>0.272661</td>\n",
       "      <td>16475.450632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-18388.949901</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3306.762000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2747.423625</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>16488.504500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1203.369529</td>\n",
       "      <td>42.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>23202.870000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>-291.095954</td>\n",
       "      <td>53.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>32549.611000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>20.000000</td>\n",
       "      <td>401755.400475</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>362496.645000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           children  days_employed     dob_years  education_id  \\\n",
       "count  21525.000000   19351.000000  21525.000000  21525.000000   \n",
       "mean       0.538908   63046.497661     43.293380      0.817236   \n",
       "std        1.381587  140827.311974     12.574584      0.548138   \n",
       "min       -1.000000  -18388.949901      0.000000      0.000000   \n",
       "25%        0.000000   -2747.423625     33.000000      1.000000   \n",
       "50%        0.000000   -1203.369529     42.000000      1.000000   \n",
       "75%        1.000000    -291.095954     53.000000      1.000000   \n",
       "max       20.000000  401755.400475     75.000000      4.000000   \n",
       "\n",
       "       family_status_id          debt   total_income  \n",
       "count      21525.000000  21525.000000   19351.000000  \n",
       "mean           0.972544      0.080883   26787.568355  \n",
       "std            1.420324      0.272661   16475.450632  \n",
       "min            0.000000      0.000000    3306.762000  \n",
       "25%            0.000000      0.000000   16488.504500  \n",
       "50%            0.000000      0.000000   23202.870000  \n",
       "75%            1.000000      0.000000   32549.611000  \n",
       "max            4.000000      1.000000  362496.645000  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Obter informações sobre dados\n",
    "df.describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8d1a39e1-34e8-4103-84e8-ad6537d327f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coluna</th>\n",
       "      <th>quantidade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>days_employed</td>\n",
       "      <td>2174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>total_income</td>\n",
       "      <td>2174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>children</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dob_years</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>education</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>education_id</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>family_status</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>family_status_id</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>gender</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>income_type</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>debt</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>purpose</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              coluna  quantidade\n",
       "0      days_employed        2174\n",
       "1       total_income        2174\n",
       "2           children           0\n",
       "3          dob_years           0\n",
       "4          education           0\n",
       "5       education_id           0\n",
       "6      family_status           0\n",
       "7   family_status_id           0\n",
       "8             gender           0\n",
       "9        income_type           0\n",
       "10              debt           0\n",
       "11           purpose           0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()\\\n",
    ".sort_values(ascending=False)\\\n",
    ".reset_index()\\\n",
    ".rename(columns={'index':'coluna', 0:'quantidade'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "501fc546-2154-4c85-9480-63748175d64c",
   "metadata": {},
   "source": [
    "As colunas **`days_employed`** e **`total_income`** possuem ambas 2174 valores ausentes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d6efc2ec-d039-4127-8c5e-513e7b8f5377",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>children</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>dob_years</th>\n",
       "      <th>education</th>\n",
       "      <th>education_id</th>\n",
       "      <th>family_status</th>\n",
       "      <th>family_status_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>income_type</th>\n",
       "      <th>debt</th>\n",
       "      <th>total_income</th>\n",
       "      <th>purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>M</td>\n",
       "      <td>retiree</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>to have a wedding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>civil servant</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>unmarried</td>\n",
       "      <td>4</td>\n",
       "      <td>F</td>\n",
       "      <td>retiree</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>building a real estate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>civil servant</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>second-hand car purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>retiree</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>to have a wedding</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    children  days_employed  dob_years            education  education_id  \\\n",
       "12         0            NaN         65  secondary education             1   \n",
       "26         0            NaN         41  secondary education             1   \n",
       "29         0            NaN         63  secondary education             1   \n",
       "41         0            NaN         50  secondary education             1   \n",
       "55         0            NaN         54  secondary education             1   \n",
       "\n",
       "        family_status  family_status_id gender    income_type  debt  \\\n",
       "12  civil partnership                 1      M        retiree     0   \n",
       "26            married                 0      M  civil servant     0   \n",
       "29          unmarried                 4      F        retiree     0   \n",
       "41            married                 0      F  civil servant     0   \n",
       "55  civil partnership                 1      F        retiree     1   \n",
       "\n",
       "    total_income                   purpose  \n",
       "12           NaN         to have a wedding  \n",
       "26           NaN                 education  \n",
       "29           NaN    building a real estate  \n",
       "41           NaN  second-hand car purchase  \n",
       "55           NaN         to have a wedding  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vejamos a tabela filtrada com valores ausentes na primeira coluna com dados ausentes\n",
    "df[df['days_employed'].isna()].head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "652bbbd8-1961-4b9f-94ac-1fd0e9ff6e2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>coluna</th>\n",
       "      <th>porcentagem</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>days_employed</td>\n",
       "      <td>0.100999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>total_income</td>\n",
       "      <td>0.100999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>children</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dob_years</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>education</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>education_id</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>family_status</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>family_status_id</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>gender</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>income_type</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>debt</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>purpose</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              coluna  porcentagem\n",
       "0      days_employed     0.100999\n",
       "1       total_income     0.100999\n",
       "2           children     0.000000\n",
       "3          dob_years     0.000000\n",
       "4          education     0.000000\n",
       "5       education_id     0.000000\n",
       "6      family_status     0.000000\n",
       "7   family_status_id     0.000000\n",
       "8             gender     0.000000\n",
       "9        income_type     0.000000\n",
       "10              debt     0.000000\n",
       "11           purpose     0.000000"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificar a distribuição dos valores ausentes em days_employed e total_income em relação ao total.\n",
    "df.isna().mean().sort_values(ascending=False)\\\n",
    ".reset_index()\\\n",
    ".rename(columns={'index':'coluna', 0:'porcentagem'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a28197c-8029-4528-95e7-65ee03568424",
   "metadata": {},
   "source": [
    "**Conclusão intermediária**\n",
    "\n",
    "Os valores nas linhas com valores ausentes parecem simétricos. Sendo a primeira vista, os valores ausentes em **`days_employed`** ocorrem nas mesmas linhas que os valores ausentes em **`total_income`**. As linhas com valores ausentes representam 2174 linhas, 10% das linhas possuem valores ausentes.\n",
    "\n",
    "Agora será feita uma comparação entre a distribuição dos valores das colunas, entre a tabela **`df`**(possuí os valores originais) e a tabela **`df_nan`**(possuí os valores filtrados das linhas com valores ausentes). Essa comparação tem o objetivo de identificar possíveis características dos cliente que causem a ausência dos valores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f9ed6a3b-ecf6-4881-88d7-9d25dd0f29e6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Comparação da coluna children entre a tabela df e df_nan:\n",
      " 0     0.657329\n",
      " 1     0.223833\n",
      " 2     0.095470\n",
      " 3     0.015331\n",
      " 20    0.003531\n",
      "-1     0.002184\n",
      " 4     0.001905\n",
      " 5     0.000418\n",
      "Name: children, dtype: float64\n",
      " 0     0.661914\n",
      " 1     0.218491\n",
      " 2     0.093836\n",
      " 3     0.016559\n",
      " 20    0.004140\n",
      " 4     0.003220\n",
      "-1     0.001380\n",
      " 5     0.000460\n",
      "Name: children, dtype: float64\n",
      "\n",
      "Comparação da coluna education_id entre a tabela df e df_nan:\n",
      "1    0.707689\n",
      "0    0.244367\n",
      "2    0.034564\n",
      "3    0.013101\n",
      "4    0.000279\n",
      "Name: education_id, dtype: float64\n",
      "1    0.708372\n",
      "0    0.250230\n",
      "2    0.031739\n",
      "3    0.009660\n",
      "Name: education_id, dtype: float64\n",
      "\n",
      "Comparação da coluna family_status_id entre a tabela df e df_nan:\n",
      "0    0.575145\n",
      "1    0.194053\n",
      "4    0.130685\n",
      "3    0.055517\n",
      "2    0.044599\n",
      "Name: family_status_id, dtype: float64\n",
      "0    0.568997\n",
      "1    0.203312\n",
      "4    0.132475\n",
      "3    0.051518\n",
      "2    0.043698\n",
      "Name: family_status_id, dtype: float64\n",
      "\n",
      "Comparação da coluna gender entre a tabela df e df_nan:\n",
      "F      0.661370\n",
      "M      0.338583\n",
      "XNA    0.000046\n",
      "Name: gender, dtype: float64\n",
      "F    0.682613\n",
      "M    0.317387\n",
      "Name: gender, dtype: float64\n",
      "\n",
      "Comparação da coluna income_type entre a tabela df e df_nan:\n",
      "employee                       0.516562\n",
      "business                       0.236237\n",
      "retiree                        0.179141\n",
      "civil servant                  0.067782\n",
      "unemployed                     0.000093\n",
      "entrepreneur                   0.000093\n",
      "student                        0.000046\n",
      "paternity / maternity leave    0.000046\n",
      "Name: income_type, dtype: float64\n",
      "employee         0.508280\n",
      "business         0.233671\n",
      "retiree          0.189972\n",
      "civil servant    0.067617\n",
      "entrepreneur     0.000460\n",
      "Name: income_type, dtype: float64\n",
      "\n",
      "Comparação da coluna debt entre a tabela df e df_nan:\n",
      "0    0.919117\n",
      "1    0.080883\n",
      "Name: debt, dtype: float64\n",
      "0    0.921803\n",
      "1    0.078197\n",
      "Name: debt, dtype: float64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Vamos investigar clientes que não possuem dados sobre as características identificadas e a coluna com os valores ausentes\n",
    "df_nan= df[df['days_employed'].isna()]\n",
    "\n",
    "# children \n",
    "print('Comparação da coluna children entre a tabela df e df_nan:')\n",
    "print(df['children'].value_counts(dropna=True,normalize=True))\n",
    "print(df_nan['children'].value_counts(normalize=True))\n",
    "print()\n",
    "\n",
    "# education_id days_employed \tdob_years \teducation \teducation_id \tfamily_status \tfamily_status_id \tgender \tincome_type \tdebt \ttotal_income \tpurpose\n",
    "print('Comparação da coluna education_id entre a tabela df e df_nan:')\n",
    "print(df['education_id'].value_counts(dropna=True,normalize=True))\n",
    "print(df_nan['education_id'].value_counts(normalize=True))\n",
    "print()\n",
    "\n",
    "# family_status_id\n",
    "print('Comparação da coluna family_status_id entre a tabela df e df_nan:')\n",
    "print(df['family_status_id'].value_counts(dropna=True,normalize=True))\n",
    "print(df_nan['family_status_id'].value_counts(normalize=True))\n",
    "print()\n",
    " \n",
    "# gender\n",
    "print('Comparação da coluna gender entre a tabela df e df_nan:')\n",
    "print(df['gender'].value_counts(dropna=True,normalize=True))\n",
    "print(df_nan['gender'].value_counts(normalize=True))\n",
    "print()\n",
    "\n",
    "# income_type\n",
    "print('Comparação da coluna income_type entre a tabela df e df_nan:')\n",
    "print(df['income_type'].value_counts(dropna=True,normalize=True))\n",
    "print(df_nan['income_type'].value_counts(normalize=True))\n",
    "print()\n",
    "\n",
    "#debt\n",
    "print('Comparação da coluna debt entre a tabela df e df_nan:')\n",
    "print(df['debt'].value_counts(dropna=True,normalize=True))\n",
    "print(df_nan['debt'].value_counts(normalize=True))\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca2f9818-f3f4-442d-b28d-3f72d48297b8",
   "metadata": {},
   "source": [
    "**Conclusão intermediária**\n",
    "\n",
    "Não foi encontrado nenhuma variação relevante em relação a proporção dos valores entre a tabela **`df`** e a tabela **`df_nan`**. Sendo assim, não é possível afirmar a existência de uma característica específica do cliente como motivo para os valores ausentes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b1c590a-96e0-4586-b9ea-3e72f25fe78a",
   "metadata": {},
   "source": [
    " **Conclusões**\n",
    "\n",
    "Não foi encontrdo padrão para para os valores ausentes e essas linhas representam 10% da tabela, sendo assim, foi decidido a subistituição dos valores ausentes. Será utilizada a média para substituir os valores das colunas **`days_employed`** e **`total_income`**, após essas colunas serem tratadas.\n",
    "\n",
    "Dessa forma, as colunas serão tratadas para a identificação e correção de duplicatas, registros diferentes, artefatos incorretos e valores ausentes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97a7745f-0a14-4905-9221-a2d37226a7a2",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Transformação de dados\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0fcc0db-11ec-4cbc-8e6f-924957d4abc1",
   "metadata": {},
   "source": [
    "Verificação da coluna **`education`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fad8f256-f3a6-48ae-9ac8-a7f8f4663bd3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\"bachelor's degree\" 'secondary education' 'Secondary Education'\n",
      " 'SECONDARY EDUCATION' \"BACHELOR'S DEGREE\" 'some college'\n",
      " 'primary education' \"Bachelor's Degree\" 'SOME COLLEGE' 'Some College'\n",
      " 'PRIMARY EDUCATION' 'Primary Education' 'Graduate Degree'\n",
      " 'GRADUATE DEGREE' 'graduate degree']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "secondary education    13750\n",
       "bachelor's degree       4718\n",
       "SECONDARY EDUCATION      772\n",
       "Secondary Education      711\n",
       "some college             668\n",
       "BACHELOR'S DEGREE        274\n",
       "Bachelor's Degree        268\n",
       "primary education        250\n",
       "Some College              47\n",
       "SOME COLLEGE              29\n",
       "PRIMARY EDUCATION         17\n",
       "Primary Education         15\n",
       "graduate degree            4\n",
       "Graduate Degree            1\n",
       "GRADUATE DEGREE            1\n",
       "Name: education, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vamos ver todos os valores na coluna de educação para verificar se e quais grafias precisarão ser corrigidas\n",
    "print(df['education'].unique())\n",
    "\n",
    "df['education'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d07a71fa-0040-422d-a31b-a038090aff6e",
   "metadata": {},
   "source": [
    "A coluna **`education`** possui valores iguais inseridos de forma diferente, o tamanho da fonte será diminuído para acabar com as duplicatas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a0f64445-f1ea-4cdb-86d6-2b10f7073be9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['education']= df['education'].str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5214791c-a590-4f28-bd36-db99a044ecc1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\"bachelor's degree\" 'secondary education' 'some college'\n",
      " 'primary education' 'graduate degree']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "secondary education    15233\n",
       "bachelor's degree       5260\n",
       "some college             744\n",
       "primary education        282\n",
       "graduate degree            6\n",
       "Name: education, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificando todos os valores na coluna para ter certeza de que os corrigimos\n",
    "print(df['education'].unique())\n",
    "\n",
    "df['education'].value_counts()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de47d2a-2e29-44bc-b650-63a7020c4704",
   "metadata": {},
   "source": [
    "Duplicatas da coluna **`education`** foram resolvidas."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c6dc30e-c1dc-4687-a3a2-5da2df8fcbf6",
   "metadata": {},
   "source": [
    "Verificação da coluna **`children`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "69b1920d-ed6b-4f66-babe-d0bceded6f8f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 1  0  3  2 -1  4 20  5]\n",
      " 0     14149\n",
      " 1      4818\n",
      " 2      2055\n",
      " 3       330\n",
      " 20       76\n",
      "-1        47\n",
      " 4        41\n",
      " 5         9\n",
      "Name: children, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       " 0     0.657329\n",
       " 1     0.223833\n",
       " 2     0.095470\n",
       " 3     0.015331\n",
       " 20    0.003531\n",
       "-1     0.002184\n",
       " 4     0.001905\n",
       " 5     0.000418\n",
       "Name: children, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df['children'].unique())\n",
    "\n",
    "print(df['children'].value_counts())\n",
    "\n",
    "df['children'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd5fbd0a-19c1-4367-8775-597ea230bf47",
   "metadata": {},
   "source": [
    "A coluna **`children`** possui valores claramente incorretos. A quantidade \"-1\" de filhos foi provavlemente um erro de digitação, deve representar \"1\". O valor \"20\" também parece estar incorreto, pois o valor abaixo é \"5\", sendo provável foi erro de digitação e representa o valor \"2\". Dessa forma, os valores \"-1\" e \"20\" serão substituídos por \"1\" e \"2\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "89d309c7-850b-4243-ba11-9e73359a6a17",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['children'] = df['children'].replace([-1,20],[1,2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e4d2803e-142d-48f5-964e-fe11d9099af5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    14149\n",
       "1     4865\n",
       "2     2131\n",
       "3      330\n",
       "4       41\n",
       "5        9\n",
       "Name: children, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificar a coluna `children` novamente para ter certeza de que está tudo corrigido\n",
    "df['children'].value_counts()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "541a8ee5-9a99-4aea-8037-413354890e09",
   "metadata": {},
   "source": [
    "Valores incorretos da coluna **`children`** foram substituidos."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd5bf2a3-5946-455f-b92c-fc996801f4ab",
   "metadata": {},
   "source": [
    "Verificação da coluna **`days_employed`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2a4cc4d7-80df-47b9-8ea5-a36f69667a81",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count     19351.000000\n",
      "mean      63046.497661\n",
      "std      140827.311974\n",
      "min      -18388.949901\n",
      "25%       -2747.423625\n",
      "50%       -1203.369529\n",
      "75%        -291.095954\n",
      "max      401755.400475\n",
      "Name: days_employed, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "-1203.369528770489"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df['days_employed'].describe())\n",
    "df['days_employed'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "425515c3-061a-430c-86d4-24f2566f2184",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8990011614401858"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df.query('days_employed < 0 or days_employed > 30000')) / len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2a43d86-65ba-426a-80cb-2a37d8a700a5",
   "metadata": {},
   "source": [
    "Na coluna **`days_employed`** estão valores negativos e alguns valores muito grandes para para a variável. Esses valores representam 90% das linhas. Os valores negativos serão positivados. Os valores mais altos que 30000 serão divididos por 100, pois,  deve ter havido erro de digitação. Com o valor mais alto real sendo 18388.949901 ou 50 anos, acima desse valor há um salto para um tempo irreal de trabalho."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "da935100-a1f8-4c42-af5a-939e83fe4e0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Positivar os valores negativos\n",
    "df['days_employed'] = abs(df['days_employed'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72bb4348-8db8-4599-82ad-5b9a3692df8d",
   "metadata": {},
   "source": [
    "Conferindo os percentis da coluna."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "262c0e2e-3ca0-4c4f-8e8c-331f54fc49db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.250       927.009265\n",
      "0.500      2194.220567\n",
      "0.750      5537.882441\n",
      "0.800      8795.549101\n",
      "0.825    329983.991540\n",
      "0.850    340088.707327\n",
      "1.000    401755.400475\n",
      "Name: days_employed, dtype: float64\n",
      "\n",
      "Mediana:  2194.220566878695\n"
     ]
    }
   ],
   "source": [
    "print(df['days_employed'].quantile([0.25,0.5,0.75,0.8,0.825,0.85,1]))\n",
    "\n",
    "print()\n",
    "print('Mediana: ',df['days_employed'].median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3351f984-6472-4d35-815d-e0dd9d1ae5ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>children</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>dob_years</th>\n",
       "      <th>education</th>\n",
       "      <th>education_id</th>\n",
       "      <th>family_status</th>\n",
       "      <th>family_status_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>income_type</th>\n",
       "      <th>debt</th>\n",
       "      <th>total_income</th>\n",
       "      <th>purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16335</th>\n",
       "      <td>1</td>\n",
       "      <td>18388.949901</td>\n",
       "      <td>61</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>29788.629</td>\n",
       "      <td>real estate transactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4299</th>\n",
       "      <td>0</td>\n",
       "      <td>17615.563266</td>\n",
       "      <td>61</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>business</td>\n",
       "      <td>0</td>\n",
       "      <td>19609.719</td>\n",
       "      <td>purchase of the house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7329</th>\n",
       "      <td>0</td>\n",
       "      <td>16593.472817</td>\n",
       "      <td>60</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>0</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>19951.655</td>\n",
       "      <td>going to university</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17838</th>\n",
       "      <td>0</td>\n",
       "      <td>16264.699501</td>\n",
       "      <td>59</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>8198.235</td>\n",
       "      <td>to buy a car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16825</th>\n",
       "      <td>0</td>\n",
       "      <td>16119.687737</td>\n",
       "      <td>64</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>14644.430</td>\n",
       "      <td>buy residential real estate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       children  days_employed  dob_years            education  education_id  \\\n",
       "16335         1   18388.949901         61  secondary education             1   \n",
       "4299          0   17615.563266         61  secondary education             1   \n",
       "7329          0   16593.472817         60    bachelor's degree             0   \n",
       "17838         0   16264.699501         59  secondary education             1   \n",
       "16825         0   16119.687737         64  secondary education             1   \n",
       "\n",
       "      family_status  family_status_id gender income_type  debt  total_income  \\\n",
       "16335       married                 0      F    employee     0     29788.629   \n",
       "4299        married                 0      F    business     0     19609.719   \n",
       "7329        married                 0      F    employee     0     19951.655   \n",
       "17838       married                 0      F    employee     0      8198.235   \n",
       "16825       married                 0      F    employee     0     14644.430   \n",
       "\n",
       "                           purpose  \n",
       "16335     real estate transactions  \n",
       "4299         purchase of the house  \n",
       "7329           going to university  \n",
       "17838                 to buy a car  \n",
       "16825  buy residential real estate  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['days_employed']<30000].sort_values(by='days_employed',ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a66d8e98-ce5d-488e-b451-3637dbb90b5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['days_employed']=df['days_employed'].mask(df['days_employed']>30000.000, df['days_employed'] /100 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "8224ec5f-a395-4274-8584-16ced639b419",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    19351.000000\n",
       "mean      2583.921756\n",
       "std       2149.032624\n",
       "min         24.141633\n",
       "25%        927.009265\n",
       "50%       2194.220567\n",
       "75%       3658.750303\n",
       "max      18388.949901\n",
       "Name: days_employed, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verifique o resultado - certifique-se de que está corrigido\n",
    "\n",
    "df['days_employed'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2fa41d9-9b23-4e27-b092-f03f7e30f6d2",
   "metadata": {},
   "source": [
    "Os valores negativos foram positivados e os valores incorretamente grandes tiveram seu valor dividido por 100. A valicição mostrou que os dados foram corrigidos."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5270fd0-4c23-4303-a680-a175985b79b5",
   "metadata": {},
   "source": [
    "Verificação da coluna **`dob_years`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8c3520f7-3ba1-4108-80cf-b98ee0f6d8d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    21525.000000\n",
      "mean        43.293380\n",
      "std         12.574584\n",
      "min          0.000000\n",
      "25%         33.000000\n",
      "50%         42.000000\n",
      "75%         53.000000\n",
      "max         75.000000\n",
      "Name: dob_years, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "## Verifique o `dob_years` para valores suspeitos\n",
    "print(df['dob_years'].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "e4b49495-9658-4a47-995d-14ac568d9fb2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "101"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "0.004692218350754936"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(len(df[df['dob_years']==0]['dob_years']))\n",
    "\n",
    "len(df[df['dob_years']==0]['dob_years']) / len(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be9b4a3a-0f95-4b49-9296-4fe3f206d233",
   "metadata": {},
   "source": [
    " A coluna **`dob_years`** possui 101 valores 0. Um valor incorreto para a idade, sendo assim, serão substituídos pela mediana."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "6ec2f32e-1d95-46ee-ab16-ae456cf55e9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Resolução dos problemas na coluna `dob_years`\n",
    "df['dob_years'].mask(df['dob_years']==0, df['dob_years'].median(),inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f270d121-dee3-4f15-9b33-728746aba5bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    21525.000000\n",
      "mean        43.490453\n",
      "std         12.218595\n",
      "min         19.000000\n",
      "25%         34.000000\n",
      "50%         42.000000\n",
      "75%         53.000000\n",
      "max         75.000000\n",
      "Name: dob_years, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificação do resultado\n",
    "print(df['dob_years'].describe())\n",
    "\n",
    "df[df['dob_years']==0]['dob_years'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0174842c-4dc3-46af-a228-da496a9044ec",
   "metadata": {},
   "source": [
    "As idades com 0 anos foram substituídas pela mediana. Todos os problemas da coluna **`dob_years`** foram corrigidos."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34a47036-27db-4796-b767-1ecc8c1250c7",
   "metadata": {},
   "source": [
    "Verificação da coluna **`family_status`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "62a02098-9d73-4cd0-9bc9-9172e4d7cf71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['married' 'civil partnership' 'widow / widower' 'divorced' 'unmarried']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "married              12380\n",
       "civil partnership     4177\n",
       "unmarried             2813\n",
       "divorced              1195\n",
       "widow / widower        960\n",
       "Name: family_status, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vamos ver os valores da coluna family_status\n",
    "print(df['family_status'].unique())\n",
    "\n",
    "df['family_status'].value_counts()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01bb277a-f53a-44b2-bafc-e554ec25d435",
   "metadata": {},
   "source": [
    "A coluna **`family_status`** não possui nenhum problema."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96c13b08-8f82-4cb4-b66d-f62bc933fa69",
   "metadata": {},
   "source": [
    "Verificação da coluna **`gender`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "1dd3c1f3-74e3-455c-b7ba-dd78818ba0f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['F' 'M' 'XNA']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "F      14236\n",
       "M       7288\n",
       "XNA        1\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vamos ver os valores na coluna\n",
    "print(df['gender'].unique())\n",
    "\n",
    "df['gender'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f938311-ee60-471a-948c-4975763a29cb",
   "metadata": {},
   "source": [
    "O valor \"XNA\" da coluna **`gender`**, por possuir apenas um valor na coluna inteira, foi decidico que a linha será apagada da tabela."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "d1d5fa7c-143b-41cb-9402-b06057d160c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(df[df['gender'] == 'XNA'].index).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d9ff9b9-5f21-48c9-a767-9bac33ef4a3b",
   "metadata": {},
   "source": [
    "Valor \"XNA\" deletado da tabela."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "ab5cec7d-a5be-48c5-b0b2-03070d146a90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "F    14236\n",
       "M     7288\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verifique o resultado - certifique-se de que está corrigido\n",
    "df['gender'].value_counts()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f72698f-c298-4c17-8c26-21701adff4a1",
   "metadata": {},
   "source": [
    "Verificação da coluna **`income_type`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "4d2b2c18-c48b-4e43-b5dc-38bd8835079d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['employee' 'retiree' 'business' 'civil servant' 'unemployed'\n",
      " 'entrepreneur' 'student' 'paternity / maternity leave']\n",
      "employee                       11119\n",
      "business                        5084\n",
      "retiree                         3856\n",
      "civil servant                   1459\n",
      "unemployed                         2\n",
      "entrepreneur                       2\n",
      "student                            1\n",
      "paternity / maternity leave        1\n",
      "Name: income_type, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "employee                       0.516586\n",
       "business                       0.236201\n",
       "retiree                        0.179149\n",
       "civil servant                  0.067785\n",
       "unemployed                     0.000093\n",
       "entrepreneur                   0.000093\n",
       "student                        0.000046\n",
       "paternity / maternity leave    0.000046\n",
       "Name: income_type, dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Vamos ver os valores na coluna\n",
    "print(df['income_type'].unique())\n",
    "\n",
    "print(df['income_type'].value_counts())\n",
    "\n",
    "df['income_type'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af5557a4-5b07-4833-8f1b-ec87fb307727",
   "metadata": {},
   "source": [
    "A coluna **`income_type`** não possui valores problemáticos."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27950876-6357-4346-a383-5ccfb87d0350",
   "metadata": {},
   "source": [
    "Verificação de valores **duplicados**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "42d96010-0ca3-432c-b8c2-f61923ef9061",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificar duplicatas\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "76a4bbde-10ec-42be-a43b-c12b21bfe7dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aborde as duplicatas, se existirem\n",
    "df=df.drop_duplicates().reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "bfa80015-c103-4230-9fcd-cf928071f4f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Última verificação se temos duplicatas\n",
    "df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "5d952858-ad6a-4b66-b5e6-9648e45c78a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21452 entries, 0 to 21451\n",
      "Data columns (total 12 columns):\n",
      " #   Column            Non-Null Count  Dtype  \n",
      "---  ------            --------------  -----  \n",
      " 0   children          21452 non-null  int64  \n",
      " 1   days_employed     19350 non-null  float64\n",
      " 2   dob_years         21452 non-null  int64  \n",
      " 3   education         21452 non-null  object \n",
      " 4   education_id      21452 non-null  int64  \n",
      " 5   family_status     21452 non-null  object \n",
      " 6   family_status_id  21452 non-null  int64  \n",
      " 7   gender            21452 non-null  object \n",
      " 8   income_type       21452 non-null  object \n",
      " 9   debt              21452 non-null  int64  \n",
      " 10  total_income      19350 non-null  float64\n",
      " 11  purpose           21452 non-null  object \n",
      "dtypes: float64(2), int64(5), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "#Verifique o tamanho do conjunto de dados que você tem agora após suas primeiras manipulações com ele\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3a800e2b-8eb4-4464-b48e-1b3cb557adec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.003391405342624855"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "73 / 21525 "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b1c28c7-5040-4f7e-96b0-b7dedc4c4b4d",
   "metadata": {},
   "source": [
    "O dataframe foi reduzido de 21525 para 21452, foram retiradas 73 linhas com dados duplicados ou incorretos, uma variação de 0,3%. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3234768-eb37-4253-939d-496a8124530c",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Trabalhando com valores ausentes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cdbbb07-1d7a-430f-bbf9-8f0c13996a34",
   "metadata": {},
   "source": [
    "As colunas **`total_income`**, **`days_employed`** e **`year`** possuem valores ausentes. Sendo assim, esse dados serão substituídos pelas médias ou medianas, dependendo. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "f115966d-d4b8-4702-a269-f157197cdb1b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>children</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>dob_years</th>\n",
       "      <th>education</th>\n",
       "      <th>education_id</th>\n",
       "      <th>family_status</th>\n",
       "      <th>family_status_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>income_type</th>\n",
       "      <th>debt</th>\n",
       "      <th>total_income</th>\n",
       "      <th>purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>8437.673028</td>\n",
       "      <td>42</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>0</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>40620.102</td>\n",
       "      <td>purchase of the house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4024.803754</td>\n",
       "      <td>36</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>17932.802</td>\n",
       "      <td>car purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>5623.422610</td>\n",
       "      <td>33</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>23341.752</td>\n",
       "      <td>purchase of the house</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4124.747207</td>\n",
       "      <td>32</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>42820.568</td>\n",
       "      <td>supplementary education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3402.660720</td>\n",
       "      <td>53</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>retiree</td>\n",
       "      <td>0</td>\n",
       "      <td>25378.572</td>\n",
       "      <td>to have a wedding</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   children  days_employed  dob_years            education  education_id  \\\n",
       "0         1    8437.673028         42    bachelor's degree             0   \n",
       "1         1    4024.803754         36  secondary education             1   \n",
       "2         0    5623.422610         33  secondary education             1   \n",
       "3         3    4124.747207         32  secondary education             1   \n",
       "4         0    3402.660720         53  secondary education             1   \n",
       "\n",
       "       family_status  family_status_id gender income_type  debt  total_income  \\\n",
       "0            married                 0      F    employee     0     40620.102   \n",
       "1            married                 0      F    employee     0     17932.802   \n",
       "2            married                 0      M    employee     0     23341.752   \n",
       "3            married                 0      M    employee     0     42820.568   \n",
       "4  civil partnership                 1      F     retiree     0     25378.572   \n",
       "\n",
       "                   purpose  \n",
       "0    purchase of the house  \n",
       "1             car purchase  \n",
       "2    purchase of the house  \n",
       "3  supplementary education  \n",
       "4        to have a wedding  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Encontre os dicionários\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "610bf3e6-8c48-4a9a-92e2-d8607c4e2acb",
   "metadata": {},
   "source": [
    "### Restaurar valores ausentes em `total_income`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0815d27e-10cf-4469-900d-2a95871d74df",
   "metadata": {},
   "source": [
    "Os valores ausentes da coluna **`total_income`** serão abordados primeiro. Os valores ausentes serão relacionados as categorias das colunas **`education`**, **`income_type`** e **`dob_years`**. Será visto a quantidades e proporção de valores ausentes em relação as categorias dessas colunas. O objetivo é procurar qual é a melhor coluna para auxiliar na substituição dos valores ausentes.\n",
    "\n",
    "A coluna **`dob_years`** é numérica, sendo assim, foi decidido pela criação de uma nova coluna **`age_index`** , armazenando as faixas de idade em categorias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "d72e5a9c-06a7-4f11-866e-915a320766c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     19350.000000\n",
       "mean      26787.266688\n",
       "std       16475.822926\n",
       "min        3306.762000\n",
       "25%       16486.515250\n",
       "50%       23201.873500\n",
       "75%       32547.910750\n",
       "max      362496.645000\n",
       "Name: total_income, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['total_income'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "303df04f-d9c0-411b-81a7-93e62b379344",
   "metadata": {},
   "source": [
    "Relação entre **`total_income`** e **`education`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "cb75370f-684f-41eb-a8b1-ffc440187b4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "secondary education    1478\n",
       "bachelor's degree       534\n",
       "some college             69\n",
       "primary education        21\n",
       "Name: education, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "bachelor's degree      0.101714\n",
       "graduate degree             NaN\n",
       "primary education      0.074468\n",
       "secondary education    0.097423\n",
       "some college           0.092867\n",
       "Name: education, dtype: float64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df[df['total_income'].isna()]['education'].value_counts())\n",
    "df[df['total_income'].isna()]['education'].value_counts() / df['education'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0946b1f-d512-495f-bf2e-b49e31624665",
   "metadata": {},
   "source": [
    "Relação entre **`total_income`** e **`income_type`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "14774e4b-06b9-459f-bdf2-5866222d5220",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "employee         1069\n",
       "business          501\n",
       "retiree           386\n",
       "civil servant     145\n",
       "entrepreneur        1\n",
       "Name: income_type, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "business                       0.098680\n",
       "civil servant                  0.099520\n",
       "employee                       0.096454\n",
       "entrepreneur                   0.500000\n",
       "paternity / maternity leave         NaN\n",
       "retiree                        0.100810\n",
       "student                             NaN\n",
       "unemployed                          NaN\n",
       "Name: income_type, dtype: float64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df[df['total_income'].isna()]['income_type'].value_counts())\n",
    "\n",
    "df[df['total_income'].isna()]['income_type'].value_counts() / df['income_type'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b8bc9f7-ed74-4acb-8981-da4fbb14f87c",
   "metadata": {},
   "source": [
    "###  Criação da coluna **`age_index`** :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "17774e10-7da6-4d28-a9e2-e2e1090c7ef5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    21452.000000\n",
       "mean        43.469933\n",
       "std         12.213723\n",
       "min         19.000000\n",
       "25%         33.000000\n",
       "50%         42.000000\n",
       "75%         53.000000\n",
       "max         75.000000\n",
       "Name: dob_years, dtype: float64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['dob_years'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "2fdc725b-a49a-44b0-a021-172f8ed1ce4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def creat_column_age(df):\n",
    "    cut_index = pd.cut(df , bins=[18, 29, 39, 49, 59, 69, 76],   labels= ['18-29', '30-39', '40-49','50-59','60-69','70-75'])\n",
    "    return cut_index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c45cb5c2-8c75-49d1-9c45-1fafd22a9e91",
   "metadata": {},
   "source": [
    "A função para categorizar os valores da coluna **`dob_years`** foi criada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "12a5e576-b090-4b54-8b62-437bd1dd4b0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30-39    5662\n",
       "40-49    5454\n",
       "50-59    4657\n",
       "18-29    3179\n",
       "60-69    2331\n",
       "70-75     169\n",
       "Name: dob_years, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Teste se a função funciona\n",
    "test = creat_column_age(df['dob_years'])\n",
    "test.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f15b26e6-1a09-40cb-a2ea-26225ff5e99a",
   "metadata": {},
   "source": [
    "O teste da função *creat_column_age* foi bem sucedido. Agora será aplicada a nova coluna **`age_index`**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d43e6a19-98ad-4378-915a-147b77b9f2fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Criar coluna nova com base na função\n",
    "df['age_index'] = creat_column_age(df['dob_years'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "b103a06b-6c04-4a02-b48f-dddbabf6d7f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30-39    5662\n",
       "40-49    5454\n",
       "50-59    4657\n",
       "18-29    3179\n",
       "60-69    2331\n",
       "70-75     169\n",
       "Name: age_index, dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['age_index'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "2f0d1dd6-f8da-4530-8799-b95605ffb0c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dob_years</th>\n",
       "      <th>age_index</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>42</td>\n",
       "      <td>40-49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>36</td>\n",
       "      <td>30-39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>33</td>\n",
       "      <td>30-39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>30-39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53</td>\n",
       "      <td>50-59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   dob_years age_index\n",
       "0         42     40-49\n",
       "1         36     30-39\n",
       "2         33     30-39\n",
       "3         32     30-39\n",
       "4         53     50-59"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verificar como os valores na nova coluna\n",
    "df[['dob_years','age_index']].head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a9552a4-44f4-4f27-81d0-c6ec68918dde",
   "metadata": {},
   "source": [
    "Coluna **`age_index`** criada."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1fcad89-7dec-4b2d-884d-6b55f992b2c5",
   "metadata": {},
   "source": [
    "Relação entre **`total_income`** e **`age_index`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "893a9e3c-4b16-4c80-9392-223e6c97b23f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30-39    553\n",
       "40-49    529\n",
       "50-59    479\n",
       "18-29    296\n",
       "60-69    236\n",
       "70-75      9\n",
       "Name: age_index, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "30-39    0.097669\n",
       "40-49    0.096993\n",
       "50-59    0.102856\n",
       "18-29    0.093111\n",
       "60-69    0.101244\n",
       "70-75    0.053254\n",
       "Name: age_index, dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df[df['total_income'].isna()]['age_index'].value_counts())\n",
    "\n",
    "df[df['total_income'].isna()]['age_index'].value_counts() / df['age_index'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ad4ea2f-00f9-46d8-a942-9293284ab8cf",
   "metadata": {},
   "source": [
    "Essa relação possivel a melhor correlação e distribuição de valores ausentes entre as categorias."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a48a1816-1b27-42ac-8d11-2dfc64277545",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>median</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age_index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18-29</th>\n",
       "      <td>22735.9110</td>\n",
       "      <td>25531.501098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30-39</th>\n",
       "      <td>24667.5280</td>\n",
       "      <td>28312.479963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40-49</th>\n",
       "      <td>24755.6960</td>\n",
       "      <td>28491.929026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50-59</th>\n",
       "      <td>22203.0745</td>\n",
       "      <td>25811.700327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60-69</th>\n",
       "      <td>19817.4400</td>\n",
       "      <td>23242.812818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70-75</th>\n",
       "      <td>18751.3240</td>\n",
       "      <td>20125.658331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               median          mean\n",
       "                                   \n",
       "age_index                          \n",
       "18-29      22735.9110  25531.501098\n",
       "30-39      24667.5280  28312.479963\n",
       "40-49      24755.6960  28491.929026\n",
       "50-59      22203.0745  25811.700327\n",
       "60-69      19817.4400  23242.812818\n",
       "70-75      18751.3240  20125.658331"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_age_orig = df.pivot_table(index='age_index',\\\n",
    "                values='total_income',\n",
    "                aggfunc=['median','mean'])\\\n",
    "                .rename(columns={'total_income':''})\n",
    "\n",
    "df_age_orig"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ce8f683-69a2-4922-9c58-aa09d5d718ad",
   "metadata": {},
   "source": [
    "Para substituir os valores ausentes da coluna **`total_income`** serão utilizado  as medianas dos valores da coluna **`age_index`**, pois ela tem a relação mais uniformes de valores faltantes, e existe logica em quanto mais velho maior o salario. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef28bd6f-513c-415d-8629-6839ca5b0225",
   "metadata": {},
   "source": [
    "Criação da função para substituir os valores ausentes pela mediana:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "577bcf9f-f21f-485c-8170-ea4bca4b2a3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_by_median(df, groupby_col, fillna_col):\n",
    "    value = df.groupby(groupby_col)[fillna_col].transform('median')\n",
    "    df[fillna_col] = df[fillna_col].fillna(value)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "1869ac76-331e-4fde-a1db-4176f866f81c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = fill_by_median(df, 'age_index', 'total_income')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06e6b55f-5bbf-458f-966c-fb21ddf9e22f",
   "metadata": {},
   "source": [
    "Funcão aplicada a coluna **`total_income`**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "e9358c57-d0cf-46a6-9462-fcfe4be4d80b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    21452\n",
       "Name: total_income, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "count     21452.000000\n",
       "mean      26444.214836\n",
       "std       15691.083429\n",
       "min        3306.762000\n",
       "25%       17217.441750\n",
       "50%       23238.964500\n",
       "75%       31328.693750\n",
       "max      362496.645000\n",
       "Name: total_income, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Verifique se temos algum erro\n",
    "display(df['total_income'].isna().value_counts())\n",
    "display(df['total_income'].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acaa9029-adf6-40e6-ac8d-7a7438483828",
   "metadata": {},
   "source": [
    "Os valores ausentes da coluna **`total_income`** foram substítuidos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "da86de49-29c5-4682-923b-3a2d73c1dd40",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21452 entries, 0 to 21451\n",
      "Data columns (total 13 columns):\n",
      " #   Column            Non-Null Count  Dtype   \n",
      "---  ------            --------------  -----   \n",
      " 0   children          21452 non-null  int64   \n",
      " 1   days_employed     19350 non-null  float64 \n",
      " 2   dob_years         21452 non-null  int64   \n",
      " 3   education         21452 non-null  object  \n",
      " 4   education_id      21452 non-null  int64   \n",
      " 5   family_status     21452 non-null  object  \n",
      " 6   family_status_id  21452 non-null  int64   \n",
      " 7   gender            21452 non-null  object  \n",
      " 8   income_type       21452 non-null  object  \n",
      " 9   debt              21452 non-null  int64   \n",
      " 10  total_income      21452 non-null  float64 \n",
      " 11  purpose           21452 non-null  object  \n",
      " 12  age_index         21452 non-null  category\n",
      "dtypes: category(1), float64(2), int64(5), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "# Verificar o número de entradas nas colunas\n",
    "df.info()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ff21b28-8a89-4f5a-9f18-afec4c24f7ca",
   "metadata": {},
   "source": [
    "###  Restaurar valores em `days_employed`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45d386dd-dcc1-49f5-945e-0f5c52bceeb4",
   "metadata": {},
   "source": [
    "Agora os valores ausentes da coluna **`days_employed`** serão abordados. Os valores ausentes serão relacionados as categorias das colunas **`education`**, **`family_status`** e **`income_type`**. Será visto a quantidades e proporção de valores ausentes em relação as categorias dessas colunas. O objetivo é procurar qual é a melhor coluna para auxiliar na substituição dos valores ausentes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "90564ee9-fa14-4c8c-b6e7-b25df0d80411",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    19350.000000\n",
       "mean      2583.933400\n",
       "std       2149.087546\n",
       "min         24.141633\n",
       "25%        926.990457\n",
       "50%       2194.218768\n",
       "75%       3658.788094\n",
       "max      18388.949901\n",
       "Name: days_employed, dtype: float64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['days_employed'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0da4035e-29f4-44a5-9270-44ffdd49e6af",
   "metadata": {},
   "source": [
    "Relação entre **`days_employed`** e **`education`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "8b34d832-a165-42e8-b889-72f39a55e6bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "secondary education    1478\n",
       "bachelor's degree       534\n",
       "some college             69\n",
       "primary education        21\n",
       "Name: education, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "bachelor's degree      0.101714\n",
       "graduate degree             NaN\n",
       "primary education      0.074468\n",
       "secondary education    0.097423\n",
       "some college           0.092867\n",
       "Name: education, dtype: float64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df[df['days_employed'].isna()]['education'].value_counts())\n",
    "\n",
    "df[df['days_employed'].isna()]['education'].value_counts() / df['education'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35bbd8cd-7109-4a51-a34c-168596cb8bfe",
   "metadata": {},
   "source": [
    "Relação entre **`days_employed`** e **`family_status`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "174fc45c-f168-4783-ba0f-da298730a353",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "married              1196\n",
       "civil partnership     415\n",
       "unmarried             285\n",
       "divorced              112\n",
       "widow / widower        94\n",
       "Name: family_status, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "married              0.096928\n",
       "civil partnership    0.100024\n",
       "unmarried            0.101423\n",
       "divorced             0.093724\n",
       "widow / widower      0.098019\n",
       "Name: family_status, dtype: float64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df[df['days_employed'].isna()]['family_status'].value_counts())\n",
    "\n",
    "df[df['days_employed'].isna()]['family_status'].value_counts() / df['family_status'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f505fb3-01c2-4ab7-aede-df5ddcc7a43d",
   "metadata": {},
   "source": [
    "Relação entre **`days_employed`** e **`income_type`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "de8cf48b-7744-4a91-98b6-5c8169200ecd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "employee         1069\n",
       "business          501\n",
       "retiree           386\n",
       "civil servant     145\n",
       "entrepreneur        1\n",
       "Name: income_type, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "business                       0.098680\n",
       "civil servant                  0.099520\n",
       "employee                       0.096454\n",
       "entrepreneur                   0.500000\n",
       "paternity / maternity leave         NaN\n",
       "retiree                        0.100810\n",
       "student                             NaN\n",
       "unemployed                          NaN\n",
       "Name: income_type, dtype: float64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(df[df['days_employed'].isna()]['income_type'].value_counts())\n",
    "\n",
    "df[df['days_employed'].isna()]['income_type'].value_counts() / df['income_type'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82305371-bf0b-449f-85b4-05a0cac39cda",
   "metadata": {},
   "source": [
    "Média e mediana de **`days_employed`** por **`income_type`**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "5a39015f-4af5-45d8-9189-1adff974fb50",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>days_employed</th>\n",
       "      <th>days_employed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>income_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>business</th>\n",
       "      <td>2111.470404</td>\n",
       "      <td>1546.333214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>civil servant</th>\n",
       "      <td>3399.896902</td>\n",
       "      <td>2689.368353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <td>2326.499216</td>\n",
       "      <td>1574.202821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>entrepreneur</th>\n",
       "      <td>520.848083</td>\n",
       "      <td>520.848083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>paternity / maternity leave</th>\n",
       "      <td>3296.759962</td>\n",
       "      <td>3296.759962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>retiree</th>\n",
       "      <td>3650.034912</td>\n",
       "      <td>3652.133063</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>student</th>\n",
       "      <td>578.751554</td>\n",
       "      <td>578.751554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unemployed</th>\n",
       "      <td>3664.136527</td>\n",
       "      <td>3664.136527</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     mean        median\n",
       "                            days_employed days_employed\n",
       "income_type                                            \n",
       "business                      2111.470404   1546.333214\n",
       "civil servant                 3399.896902   2689.368353\n",
       "employee                      2326.499216   1574.202821\n",
       "entrepreneur                   520.848083    520.848083\n",
       "paternity / maternity leave   3296.759962   3296.759962\n",
       "retiree                       3650.034912   3652.133063\n",
       "student                        578.751554    578.751554\n",
       "unemployed                    3664.136527   3664.136527"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df.pivot_table(index ='income_type', values= 'days_employed', aggfunc=['mean','median']))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8595669a-519e-4c80-b1c0-436163763379",
   "metadata": {},
   "source": [
    "O parâmetro para substituir os valores ausentes na coluna **`days_employed`** será o **`income_type`**. Por possuir uma distribuição significativa em relação aos dados faltantes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58746b15-c008-42d2-a3a5-4774d8d5f96c",
   "metadata": {},
   "source": [
    "A mediana será usada por possuir menor variância em relação a *business*, *civil servant*, *employee* da coluna **`income_type`**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "a9285767-b375-4b4c-b7d9-242f680019c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Vamos escrever uma função que calcule médias ou medianas (dependendo da sua decisão) com base no seu parâmetro identificado\n",
    "\n",
    "df =  fill_by_median(df, 'income_type', 'days_employed')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "38061dde-0ff3-405d-9e9e-c439057a92c3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    21452\n",
       "Name: days_employed, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "count    21452.000000\n",
       "mean      2529.221036\n",
       "std       2063.920041\n",
       "min         24.141633\n",
       "25%       1023.617476\n",
       "50%       1996.223132\n",
       "75%       3633.622079\n",
       "max      18388.949901\n",
       "Name: days_employed, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Verifique se a função funciona\n",
    "display(df['days_employed'].isna().value_counts())\n",
    "display(df['days_employed'].describe())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "38bac27b-dcac-439c-9ff2-8996ed73ca84",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 21452 entries, 0 to 21451\n",
      "Data columns (total 13 columns):\n",
      " #   Column            Non-Null Count  Dtype   \n",
      "---  ------            --------------  -----   \n",
      " 0   children          21452 non-null  int64   \n",
      " 1   days_employed     21452 non-null  float64 \n",
      " 2   dob_years         21452 non-null  int64   \n",
      " 3   education         21452 non-null  object  \n",
      " 4   education_id      21452 non-null  int64   \n",
      " 5   family_status     21452 non-null  object  \n",
      " 6   family_status_id  21452 non-null  int64   \n",
      " 7   gender            21452 non-null  object  \n",
      " 8   income_type       21452 non-null  object  \n",
      " 9   debt              21452 non-null  int64   \n",
      " 10  total_income      21452 non-null  float64 \n",
      " 11  purpose           21452 non-null  object  \n",
      " 12  age_index         21452 non-null  category\n",
      "dtypes: category(1), float64(2), int64(5), object(5)\n",
      "memory usage: 2.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9923a7d7-0bff-41ef-aa14-fd003426989a",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## Categorização de dados"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8720bd93-b29b-4ef8-bf2d-002049fbf21c",
   "metadata": {},
   "source": [
    "Para responder as hipóteses é necessário agrupar os valores das colunas **`purpose`** e **`total_income`** usando a função *.pivot_table()*. Para agrupar e retirar informações relevantes é necessário categorizar os valores dessas colunas.\n",
    "\n",
    "- Os valores da coluna **`purpose`** serão categorizados pelo tipo de motivo para o empréstimo.\n",
    "- Os valores da coluna **`total_income`** serão categorizados por nível de renda."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "5176e16a-48ea-4d02-b825-646b1f8d9f0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "wedding ceremony                            791\n",
       "having a wedding                            767\n",
       "to have a wedding                           765\n",
       "real estate transactions                    675\n",
       "buy commercial real estate                  661\n",
       "housing transactions                        652\n",
       "buying property for renting out             651\n",
       "transactions with commercial real estate    650\n",
       "purchase of the house                       646\n",
       "housing                                     646\n",
       "purchase of the house for my family         638\n",
       "construction of own property                635\n",
       "property                                    633\n",
       "transactions with my real estate            627\n",
       "building a real estate                      624\n",
       "buy real estate                             620\n",
       "purchase of my own house                    620\n",
       "building a property                         619\n",
       "housing renovation                          607\n",
       "buy residential real estate                 606\n",
       "buying my own car                           505\n",
       "going to university                         496\n",
       "car                                         494\n",
       "second-hand car purchase                    486\n",
       "to own a car                                478\n",
       "buying a second-hand car                    478\n",
       "cars                                        478\n",
       "to buy a car                                471\n",
       "car purchase                                461\n",
       "supplementary education                     460\n",
       "purchase of a car                           455\n",
       "university education                        452\n",
       "education                                   447\n",
       "to get a supplementary education            446\n",
       "getting an education                        442\n",
       "profile education                           436\n",
       "getting higher education                    426\n",
       "to become educated                          408\n",
       "Name: purpose, dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Exiba os valores dos dados selecionados para categorização\n",
    "df['purpose'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "ed4777f0-1142-40e9-8d08-7253a9317eb9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['purchase of the house', 'car purchase', 'supplementary education',\n",
       "       'to have a wedding', 'housing transactions', 'education',\n",
       "       'having a wedding', 'purchase of the house for my family',\n",
       "       'buy real estate', 'buy commercial real estate',\n",
       "       'buy residential real estate', 'construction of own property',\n",
       "       'property', 'building a property', 'buying a second-hand car',\n",
       "       'buying my own car', 'transactions with commercial real estate',\n",
       "       'building a real estate', 'housing',\n",
       "       'transactions with my real estate', 'cars', 'to become educated',\n",
       "       'second-hand car purchase', 'getting an education', 'car',\n",
       "       'wedding ceremony', 'to get a supplementary education',\n",
       "       'purchase of my own house', 'real estate transactions',\n",
       "       'getting higher education', 'to own a car', 'purchase of a car',\n",
       "       'profile education', 'university education',\n",
       "       'buying property for renting out', 'to buy a car',\n",
       "       'housing renovation', 'going to university'], dtype=object)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verifique os valores exclusivos\n",
    "df['purpose'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1da12377-613b-45cd-96f3-49ec9528cca9",
   "metadata": {},
   "source": [
    "Pelas características únicas identificadas nos valores da coluna **`purpose`** foi decidido dividir em quatro categorias que agrupem esses valores. Sendo eles:\n",
    "\n",
    "- carro: empréstimo para a compra de um carro.\n",
    "- casa:  empréstimo para a compra de uma casa\n",
    "- casamento: empréstimo para a celebração de um casamento\n",
    "- educação:  empréstimo para o pagamento de uma educação superior."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "51be082b-375f-4ee0-acf8-d69d06803eb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Vamos escrever uma função para categorizar os dados com base em tópicos comunss\n",
    "def grup_purpose(data):\n",
    "    if 'car' in data['purpose']:\n",
    "        return 'carro'\n",
    "    if 'hous' in data['purpose'] or 'prop' in data['purpose'] or 'real est' in data['purpose']:\n",
    "        return 'casa'\n",
    "    if 'wedd' in data['purpose']:\n",
    "        return 'casamento'\n",
    "    if 'educ' in data['purpose'] or 'uni' in data['purpose']:\n",
    "        return 'educação'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "47446549-6852-450e-852e-b481c693265d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Crie uma coluna com as categorias e conte os valores para elas\n",
    "df['categ_purpose'] = df.apply(grup_purpose, axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "48803e65-2b6e-4120-8e5a-fb1508399951",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "casa         10810\n",
       "carro         4306\n",
       "educação      4013\n",
       "casamento     2323\n",
       "Name: categ_purpose, dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['categ_purpose'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d29aabc-2a3a-4879-b21c-b9a9572bbcbb",
   "metadata": {},
   "source": [
    "Os dados da coluna **`total_income`** serão categorizados por faixa de renda.\n",
    "\n",
    "- pobre: grupo de clientes com as 10% menores rendas. Percentil inferior a 0.10.\n",
    "- classe média: grupo de clientes no intervalo de renda entre os quartis 0.10 e 0.75.\n",
    "- classe média alta: grupo de clientes no intervalo de renda entre os quartis 0.75 e 0.99.\n",
    "- rico: clientes que estão no grupo de 1% renda mais elevadas. Quartil superior a 0.99."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "d850fc89-860d-4ba9-b6b6-e9c5cab4f08c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     21452.000000\n",
       "mean      26444.214836\n",
       "std       15691.083429\n",
       "min        3306.762000\n",
       "25%       17217.441750\n",
       "50%       23238.964500\n",
       "75%       31328.693750\n",
       "max      362496.645000\n",
       "Name: total_income, dtype: float64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Obter estatísticas resumidas para a coluna\n",
    "df['total_income'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "223b0f72-8add-4781-a870-06e069d88c65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10    12594.91110\n",
       "0.75    31328.69375\n",
       "0.99    80883.31671\n",
       "Name: total_income, dtype: float64"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['total_income'].quantile([0.1,0.75,0.99])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "83c3f2ec-7d31-4108-94d8-36f32edcdf65",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Criar função para categorização em diferentes grupos numéricos com base em intervalos\n",
    "rico = df['total_income'].quantile(0.99)\n",
    "media = df['total_income'].quantile(0.75)\n",
    "pobre = df['total_income'].quantile(0.10)\n",
    "\n",
    "def grup_income(renda):\n",
    "    if renda<= pobre:\n",
    "        return 'pobre'\n",
    "    if (renda> pobre) and (renda <= media):\n",
    "        return 'classe média'\n",
    "    if (renda> media) and (renda < rico):\n",
    "        return 'classe média alta'\n",
    "    if (renda >= rico):\n",
    "        return 'rico'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "e91528b4-22c6-4e93-a9ae-7be135aa0e98",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Criar coluna com categorias\n",
    "df['categ_income'] = df['total_income'].apply(grup_income)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "078f6909-130f-454b-af91-dec879673927",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "classe média         13943\n",
       "classe média alta     5148\n",
       "pobre                 2146\n",
       "rico                   215\n",
       "Name: categ_income, dtype: int64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Conte os valores de cada categoria para ver a distribuição\n",
    "df['categ_income'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "0aed83b1-91bd-4030-b702-cec5e4a9a1ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>children</th>\n",
       "      <th>days_employed</th>\n",
       "      <th>dob_years</th>\n",
       "      <th>education</th>\n",
       "      <th>education_id</th>\n",
       "      <th>family_status</th>\n",
       "      <th>family_status_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>income_type</th>\n",
       "      <th>debt</th>\n",
       "      <th>total_income</th>\n",
       "      <th>purpose</th>\n",
       "      <th>age_index</th>\n",
       "      <th>categ_purpose</th>\n",
       "      <th>categ_income</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>8437.673028</td>\n",
       "      <td>42</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>0</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>40620.102</td>\n",
       "      <td>purchase of the house</td>\n",
       "      <td>40-49</td>\n",
       "      <td>casa</td>\n",
       "      <td>classe média alta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>4024.803754</td>\n",
       "      <td>36</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>F</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>17932.802</td>\n",
       "      <td>car purchase</td>\n",
       "      <td>30-39</td>\n",
       "      <td>carro</td>\n",
       "      <td>classe média</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>5623.422610</td>\n",
       "      <td>33</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>23341.752</td>\n",
       "      <td>purchase of the house</td>\n",
       "      <td>30-39</td>\n",
       "      <td>casa</td>\n",
       "      <td>classe média</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4124.747207</td>\n",
       "      <td>32</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>married</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>employee</td>\n",
       "      <td>0</td>\n",
       "      <td>42820.568</td>\n",
       "      <td>supplementary education</td>\n",
       "      <td>30-39</td>\n",
       "      <td>educação</td>\n",
       "      <td>classe média alta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3402.660720</td>\n",
       "      <td>53</td>\n",
       "      <td>secondary education</td>\n",
       "      <td>1</td>\n",
       "      <td>civil partnership</td>\n",
       "      <td>1</td>\n",
       "      <td>F</td>\n",
       "      <td>retiree</td>\n",
       "      <td>0</td>\n",
       "      <td>25378.572</td>\n",
       "      <td>to have a wedding</td>\n",
       "      <td>50-59</td>\n",
       "      <td>casamento</td>\n",
       "      <td>classe média</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   children  days_employed  dob_years            education  education_id  \\\n",
       "0         1    8437.673028         42    bachelor's degree             0   \n",
       "1         1    4024.803754         36  secondary education             1   \n",
       "2         0    5623.422610         33  secondary education             1   \n",
       "3         3    4124.747207         32  secondary education             1   \n",
       "4         0    3402.660720         53  secondary education             1   \n",
       "\n",
       "       family_status  family_status_id gender income_type  debt  total_income  \\\n",
       "0            married                 0      F    employee     0     40620.102   \n",
       "1            married                 0      F    employee     0     17932.802   \n",
       "2            married                 0      M    employee     0     23341.752   \n",
       "3            married                 0      M    employee     0     42820.568   \n",
       "4  civil partnership                 1      F     retiree     0     25378.572   \n",
       "\n",
       "                   purpose age_index categ_purpose       categ_income  \n",
       "0    purchase of the house     40-49          casa  classe média alta  \n",
       "1             car purchase     30-39         carro       classe média  \n",
       "2    purchase of the house     30-39          casa       classe média  \n",
       "3  supplementary education     30-39      educação  classe média alta  \n",
       "4        to have a wedding     50-59     casamento       classe média  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "480e1250-f265-4076-b2b5-10d5227271dd",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Verificar as Hipóteses"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "409bd4fd-26a1-4bd0-a341-81889cc1e240",
   "metadata": {},
   "source": [
    "**Existe uma correlação entre a quantidade de filhos e do pagamento em dia?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "b3f0a8c6-bce4-491d-b950-b3ef7b1dd5e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>debt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>porcentagem_de_default</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>children</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13026.0</td>\n",
       "      <td>1063.0</td>\n",
       "      <td>7.544893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4410.0</td>\n",
       "      <td>445.0</td>\n",
       "      <td>9.165808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1926.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>9.492481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>303.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>8.181818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.756098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "debt            0       1  porcentagem_de_default\n",
       "children                                         \n",
       "0         13026.0  1063.0                7.544893\n",
       "1          4410.0   445.0                9.165808\n",
       "2          1926.0   202.0                9.492481\n",
       "3           303.0    27.0                8.181818\n",
       "4            37.0     4.0                9.756098\n",
       "5             9.0     NaN                0.000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>debt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>porcentagem_de_default</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>children</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13026.0</td>\n",
       "      <td>1063.0</td>\n",
       "      <td>7.544893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4410.0</td>\n",
       "      <td>445.0</td>\n",
       "      <td>9.165808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1926.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>9.492481</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "debt            0       1  porcentagem_de_default\n",
       "children                                         \n",
       "0         13026.0  1063.0                7.544893\n",
       "1          4410.0   445.0                9.165808\n",
       "2          1926.0   202.0                9.492481"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Verifique os dados das crianças e do pagamento em dia\n",
    "df_child_debt =  df.pivot_table(index='children',columns='debt',values='age_index',aggfunc='count')\n",
    "\n",
    "# Calcular a taxa de inadimplência com base no número de filhos\n",
    "df_child_debt['porcentagem_de_default'] = 100* (df.groupby('children')['debt'].mean())\n",
    "\n",
    "display(df_child_debt)\n",
    "\n",
    "display(df_child_debt.loc[[0,1,2]].sort_values(by='porcentagem_de_default'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45eeb744-8acf-4289-afef-5ec54b79d923",
   "metadata": {},
   "source": [
    "**Conclusão**\n",
    "\n",
    "Foi possível identificar uma correlação positiva entre a coluna **`children`** e a **`porcentagem_de_default`**, essa correlação só é vista quando utilizados os valores de  **`children`** com observações suficientes. \n",
    "\n",
    "Sendo assim, pode-se afirmar que quantos mais filhos menos pagamentos em dia, porém, a variação relevante é entre 0 filhos e 1 filho( de 7,5% para 9.2%), após isso, o crescimento já não é relevante. Logo, deve-se tratar que ter filhos ou não afeta o pagamento do empréstimo."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaa04a83-2f8c-473e-9c48-91945d76d381",
   "metadata": {},
   "source": [
    "**Existe uma correlação entre o status familiar e o pagamento em dia?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "88f26095-f40b-4fb0-ba1d-0fd05c10b851",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>debt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>porcentagem_de_default</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>family_status</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>widow / widower</th>\n",
       "      <td>896</td>\n",
       "      <td>63</td>\n",
       "      <td>6.569343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>divorced</th>\n",
       "      <td>1110</td>\n",
       "      <td>85</td>\n",
       "      <td>7.112971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>married</th>\n",
       "      <td>11408</td>\n",
       "      <td>931</td>\n",
       "      <td>7.545182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>civil partnership</th>\n",
       "      <td>3761</td>\n",
       "      <td>388</td>\n",
       "      <td>9.351651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unmarried</th>\n",
       "      <td>2536</td>\n",
       "      <td>274</td>\n",
       "      <td>9.750890</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "debt                   0    1  porcentagem_de_default\n",
       "family_status                                        \n",
       "widow / widower      896   63                6.569343\n",
       "divorced            1110   85                7.112971\n",
       "married            11408  931                7.545182\n",
       "civil partnership   3761  388                9.351651\n",
       "unmarried           2536  274                9.750890"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Verifique os dados de status da família e do pagamento em dia\n",
    "df_fam_debt =  df.pivot_table(index='family_status',columns='debt',values='age_index',aggfunc='count')\n",
    "\n",
    "# Calcular a taxa padrão com base no status da família\n",
    "df_fam_debt['porcentagem_de_default'] = 100* (df.groupby('family_status')['debt'].mean())\n",
    "\n",
    "display(df_fam_debt.sort_values(by='porcentagem_de_default'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ebf47a9-3dac-451d-aa5c-a5fb5f2c0816",
   "metadata": {},
   "source": [
    "**Conclusão**\n",
    "\n",
    "Foi possível identificar uma correlação entre **`debt`** e **`porcentagem_de_default`**. Com os indivíduos solteiros(unmarried) com a maior porcentagem de não pagamento(9,7%) e os indivíduos viúvos(6,5%) com a menor.\n",
    "\n",
    "Dessa forma, percebe-se que os indivíduos que nunca foram casados possuem uma propensão maior a inadimplência, entre 9,3 a 9,7%. Os indivíduos que já foram casados possuem uma tendência menor, entre 6,5% e 7,5%."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "643d0fee-0fb3-477f-93ed-d71e526bce59",
   "metadata": {},
   "source": [
    "**Existe uma correlação entre o nível de renda e o pagamento em dia?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "f260fffe-9328-4e33-95f6-a027952bfa23",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>debt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>porcentagem_de_default</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>categ_income</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>rico</th>\n",
       "      <td>201</td>\n",
       "      <td>14</td>\n",
       "      <td>6.511628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>classe média alta</th>\n",
       "      <td>4779</td>\n",
       "      <td>369</td>\n",
       "      <td>7.167832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pobre</th>\n",
       "      <td>1989</td>\n",
       "      <td>157</td>\n",
       "      <td>7.315937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>classe média</th>\n",
       "      <td>12742</td>\n",
       "      <td>1201</td>\n",
       "      <td>8.613641</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "debt                   0     1  porcentagem_de_default\n",
       "categ_income                                          \n",
       "rico                 201    14                6.511628\n",
       "classe média alta   4779   369                7.167832\n",
       "pobre               1989   157                7.315937\n",
       "classe média       12742  1201                8.613641"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Verifique os dados do nível de renda e do pagamento em dia\n",
    "df_inco_debt =  df.pivot_table(index='categ_income',columns='debt',values='age_index',aggfunc='count')\n",
    "\n",
    "\n",
    "# Calcular a taxa de inadimplência com base no nível de renda\n",
    "df_inco_debt['porcentagem_de_default'] = 100* (df.groupby('categ_income')['debt'].mean())\n",
    "\n",
    "display(df_inco_debt.sort_values(by='porcentagem_de_default'))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa6495cd-872d-4a53-b93f-49deadc4c4db",
   "metadata": {},
   "source": [
    "**Conclusão**\n",
    "\n",
    "Pelo uso das categorias da coluna **`categ_income`** em relação a **`debt`**. Percebe-se que a diferença de inadimplência entre as categorias não possui uma variação muito relevante entre elas, com as *categorias classe média alta* e *pobre* tendo o mesmo nível de inadimplência. \n",
    "\n",
    "A categoria *rico* possui uma taxa de inadimplência menor em relação as outras mas possui poucas observações, sendo assim, não é possível dizer que a diferença é significativa."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "deca6908-c716-4107-ac58-9423e6bb999a",
   "metadata": {},
   "source": [
    "**Como a finalidade do crédito afeta a taxa de inadimplência?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "b745c7be-ffe4-4323-9119-5ade617f3a0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>debt</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>porcentagem_de_default</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>categ_purpose</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>casa</th>\n",
       "      <td>10028</td>\n",
       "      <td>782</td>\n",
       "      <td>7.234043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>casamento</th>\n",
       "      <td>2137</td>\n",
       "      <td>186</td>\n",
       "      <td>8.006888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>educação</th>\n",
       "      <td>3643</td>\n",
       "      <td>370</td>\n",
       "      <td>9.220035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>carro</th>\n",
       "      <td>3903</td>\n",
       "      <td>403</td>\n",
       "      <td>9.359034</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "debt               0    1  porcentagem_de_default\n",
       "categ_purpose                                    \n",
       "casa           10028  782                7.234043\n",
       "casamento       2137  186                8.006888\n",
       "educação        3643  370                9.220035\n",
       "carro           3903  403                9.359034"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Confira os percentuais de inadimplência para cada finalidade de crédito e analise-o3333s\n",
    "df_por_debt =  df.pivot_table(index='categ_purpose',columns='debt',values='age_index',aggfunc='count')\n",
    "\n",
    "\n",
    "# Calcular a taxa de inadimplência com base no propósito\n",
    "df_por_debt['porcentagem_de_default'] = 100* (df.groupby('categ_purpose')['debt'].mean())\n",
    "\n",
    "display(df_por_debt.sort_values(by='porcentagem_de_default'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "911d6807-9c92-4592-91e3-2ced514916d2",
   "metadata": {},
   "source": [
    "**Conclusão**\n",
    "\n",
    "Pelo uso das categorias da coluna **`categ_purpose`** em relação a **`debt`**. Percebe-se que os empréstimos com finalidade imobiliária possuem a menor taxa de inadimplência, de 7,2%. Os empréstimos para aquisição de educação superior e carro possuem maiores taxas de inadimplência, de 9,22% e 9,35%. A taxa de inadimplência para financiar um casamento é de 8%.\n",
    "\n",
    "Dessa forma, os empréstimos para o mercado imobiliario pode ser considerado o mais seguro e o para carros o menos seguros. Com as informações disponíveis não é possível saber o motivo."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99211521-883e-40ff-acc5-961a6a712cf3",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Conclusão Geral \n",
    "\n",
    "Inicialmento houve a exploração dos dados, com o objetivo de identificar dados incorretos, ausentes ou disprepantes. Foram identifacados que as colunas **`days_employed`**, **`education`** e **`total_income`** precisavam de correção.\n",
    "\n",
    "Em relação aos valores ausentes, 2174 observações possuiam valores ausentes representando 10% do dataframe. Não foi identificada uma razão para os valores ausentes. Foi decidido preencher os valores ausentes. Para a correção dos valores negativos foi usado o módulo. Para os valores discrepantes, foram divididos para ficarem na ordem de grandeza dos outros valores. Em relação aos dados duplicados ou incorretos, foram 73 linhas, uma variação de 0,3%.\n",
    "\n",
    "Foi criado a coluna **`age_index`** para categorizar os dados da coluna **`dob_years`**. Para categorizar a coluna **`total_income`** foi criada a coluna **`categ_income`** e para a coluna **`purpose`** foi criado a coluna **`categ_income`**.\n",
    "\n",
    "\n",
    "As conclusões em relação as perguntas foram :\n",
    "\n",
    "Existe uma correlação entre a quantidade de filhos e do pagamento em dia?\n",
    "- Existe correlação entre ter filhos ou não e o pagamento em dia. O números de filhos não é relevante a taxa de inadimplência\n",
    "\n",
    "\n",
    "Existe uma correlação entre o status familiar e o pagamento em dia?\n",
    "- Os indivíduos que nunca foram casados possuem uma propensão maior a inadimplência, entre 9,3 a 9,7%. Os indivíduos que já foram casados possuem uma tendência menor, entre 6,5% e 7,5%.\n",
    "\n",
    "Existe uma correlação entre o nível de renda e o pagamento em dia?\n",
    "- O nível de renda não possui uma variação da taxa de inadimplência muito relevante entre elas, com as categorias classe média alta e pobre tendo o mesmo nível de inadimplência.\n",
    "\n",
    "Como a finalidade do crédito afeta a taxa de inadimplência?\n",
    "- Os empréstimos com finalidade imobiliária possuem a menor taxa de inadimplência, seguido pelo casamento. Os empréstimos para aquisição de educação superior e carro possuem as maiores taxas de inadimplências."
   ]
  }
 ],
 "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": 5
}