credit_card_clients/credit_card_clients.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5f4167c3-b65f-4cbd-87ad-2d0c60ab760a",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "# Jupyter notebook info\n",
    "\n",
    "The first thing we need to know about jupyter notebook is that there are two kinds of cells. This is a markdown cell.\n",
    "\n",
    "To change to the other kind of cell, called **code** cell enter in command mode, pressing ESC and type *y*, to back to markdown type *m*.\n",
    "\n",
    "To execute a cell content press SHIFT + ENTER"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d1303de-0beb-4aa3-aca4-e70e888c8389",
   "metadata": {},
   "source": [
    "# Credit card clients project details\n",
    "\n",
    "## Introduction\n",
    "This data science project represents a practical application of the advanced concepts I've acquired. I've taken the opportunity to refine and expand upon the original work, incorporating my own insights and enhancements. The project serves as a stepping stone in my ongoing journey to tackle increasingly complex data science challenges.\n",
    "\n",
    "## Dataset\n",
    "\n",
    "The dataset for this project was sourced from the UCI Machine Learning Repository [http://archive.ics.uci.edu/ml], provided by the University of California, School of Information and Computer Science. It contains demographic and financial data from the latest 6 months and comprises around 30,000 samples.\n",
    "\n",
    "## Project goal\n",
    "\n",
    "To accomplish this project, we will explore the dataset, gaining important insights and understanding its contents. Additionally, we will clean the dataset using Pandas and apply various data science techniques.\n",
    "\n",
    "## Data science techniques\n",
    "\n",
    "- Boolean masks\n",
    "- Boolean matrix\n",
    "- Dictionary data\n",
    "\n",
    "## Vocabulary\n",
    " - Dataset -> the whole data\n",
    " - Row definition -> what each row of dataset means\n",
    " - Labels -> column names from dataset\n",
    " - Data dictionary -> document that explains the definitions of column labels\n",
    " - Dataframe -> python object representing the dataset\n",
    " - Series -> python object representing the column from dataframe\n",
    "\n",
    "## Study case\n",
    "\n",
    "In this project, we will examine a case from a credit card company. They have provided us with a dataset containing the last 6 months of client accounts at the account level. In other words, there **is one row for each account**. (*It's essential to always define the row definition in a dataset*).\n",
    "\n",
    "The rows are labeled based on whether a client defaulted in the following month relative to the historical period.\n",
    "\n",
    "## Challenge\n",
    "\n",
    "Our goal is develop a model that can predict whether an account will default on its bills in the next month, based on demographic and financial data."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba740b1c-fcaa-4fac-aa06-948475faa739",
   "metadata": {},
   "source": [
    "#### Step 1 - Load the dataset and getting shape info (rows and columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "598c0779-4133-4df3-bc8e-726c3fd5ff00",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ac6e8472-81bf-472b-bc78-b007006c9841",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('credit_card_clients.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "507a12b5-c0f1-4696-bb67-b3091d9437f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30000, 25)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d01d9049-04eb-452f-9e89-65aeb2642e56",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 30000 entries, 0 to 29999\n",
      "Data columns (total 25 columns):\n",
      " #   Column                      Non-Null Count  Dtype \n",
      "---  ------                      --------------  ----- \n",
      " 0   ID                          30000 non-null  object\n",
      " 1   LIMIT_BAL                   30000 non-null  int64 \n",
      " 2   SEX                         30000 non-null  int64 \n",
      " 3   EDUCATION                   30000 non-null  int64 \n",
      " 4   MARRIAGE                    30000 non-null  int64 \n",
      " 5   AGE                         30000 non-null  int64 \n",
      " 6   PAY_1                       30000 non-null  object\n",
      " 7   PAY_2                       30000 non-null  int64 \n",
      " 8   PAY_3                       30000 non-null  int64 \n",
      " 9   PAY_4                       30000 non-null  int64 \n",
      " 10  PAY_5                       30000 non-null  int64 \n",
      " 11  PAY_6                       30000 non-null  int64 \n",
      " 12  BILL_AMT1                   30000 non-null  int64 \n",
      " 13  BILL_AMT2                   30000 non-null  int64 \n",
      " 14  BILL_AMT3                   30000 non-null  int64 \n",
      " 15  BILL_AMT4                   30000 non-null  int64 \n",
      " 16  BILL_AMT5                   30000 non-null  int64 \n",
      " 17  BILL_AMT6                   30000 non-null  int64 \n",
      " 18  PAY_AMT1                    30000 non-null  int64 \n",
      " 19  PAY_AMT2                    30000 non-null  int64 \n",
      " 20  PAY_AMT3                    30000 non-null  int64 \n",
      " 21  PAY_AMT4                    30000 non-null  int64 \n",
      " 22  PAY_AMT5                    30000 non-null  int64 \n",
      " 23  PAY_AMT6                    30000 non-null  int64 \n",
      " 24  default payment next month  30000 non-null  int64 \n",
      "dtypes: int64(23), object(2)\n",
      "memory usage: 5.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e34401ad-9768-4c27-9eaa-255924b3d6d4",
   "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>ID</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_1</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>798fc410-45c1</td>\n",
       "      <td>20000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>689</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8a8c8f3b-8eb4</td>\n",
       "      <td>120000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3272</td>\n",
       "      <td>3455</td>\n",
       "      <td>3261</td>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85698822-43f5</td>\n",
       "      <td>90000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>14331</td>\n",
       "      <td>14948</td>\n",
       "      <td>15549</td>\n",
       "      <td>1518</td>\n",
       "      <td>1500</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0737c11b-be42</td>\n",
       "      <td>50000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>28314</td>\n",
       "      <td>28959</td>\n",
       "      <td>29547</td>\n",
       "      <td>2000</td>\n",
       "      <td>2019</td>\n",
       "      <td>1200</td>\n",
       "      <td>1100</td>\n",
       "      <td>1069</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3b7f77cc-dbc0</td>\n",
       "      <td>50000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>57</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>20940</td>\n",
       "      <td>19146</td>\n",
       "      <td>19131</td>\n",
       "      <td>2000</td>\n",
       "      <td>36681</td>\n",
       "      <td>10000</td>\n",
       "      <td>9000</td>\n",
       "      <td>689</td>\n",
       "      <td>679</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ID  LIMIT_BAL  SEX  EDUCATION  MARRIAGE  AGE PAY_1  PAY_2  \\\n",
       "0  798fc410-45c1      20000    2          2         1   24     2      2   \n",
       "1  8a8c8f3b-8eb4     120000    2          2         2   26    -1      2   \n",
       "2  85698822-43f5      90000    2          2         2   34     0      0   \n",
       "3  0737c11b-be42      50000    2          2         1   37     0      0   \n",
       "4  3b7f77cc-dbc0      50000    1          2         1   57    -1      0   \n",
       "\n",
       "   PAY_3  PAY_4  ...  BILL_AMT4  BILL_AMT5  BILL_AMT6  PAY_AMT1  PAY_AMT2  \\\n",
       "0     -1     -1  ...          0          0          0         0       689   \n",
       "1      0      0  ...       3272       3455       3261         0      1000   \n",
       "2      0      0  ...      14331      14948      15549      1518      1500   \n",
       "3      0      0  ...      28314      28959      29547      2000      2019   \n",
       "4     -1      0  ...      20940      19146      19131      2000     36681   \n",
       "\n",
       "   PAY_AMT3  PAY_AMT4  PAY_AMT5  PAY_AMT6  default payment next month  \n",
       "0         0         0         0         0                           1  \n",
       "1      1000      1000         0      2000                           1  \n",
       "2      1000      1000      1000      5000                           0  \n",
       "3      1200      1100      1069      1000                           0  \n",
       "4     10000      9000       689       679                           0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "056e344d-ff44-48e5-bae7-1c102c36ca89",
   "metadata": {},
   "source": [
    "Above we show the `shape` to get the rows and columns quantity\n",
    "\n",
    "after we executed `info` that brings to us each column and datatype\n",
    "\n",
    "And `head`, that show the first rows from the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3c0961e-5c2c-48d0-8652-90e6a74a21cf",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "25a8d2d0-c364-4b32-a352-4ca507dc2a64",
   "metadata": {},
   "source": [
    "#### Step 2 - Exploring data\n",
    "\n",
    "Generally, there are two kinds of dataset sources: those **you participated** in constructing and those you **did not**.\n",
    "\n",
    "To aid in the exploration of data, we should utilize a **data dictionary**, which is a document that explains the definitions of column labels.\n",
    "\n",
    "If you created the dataset, it is your responsibility to generate the **data dictionary**.\n",
    "\n",
    "If you did not create the dataset, you will need to attempt to obtain the **data dictionary** whenever possible.\n",
    "\n",
    "#### Dataset dictionary\n",
    "This research employed a binary variable, default payment (**Yes = 1, No = 0**), as the response variable. This study reviewed the literature and used the following 23 variables as explanatory variables:\n",
    "\n",
    "**LIMIT_BAL**: Amount of the given credit (NT dollar): it includes both the individual consumer credit and his/her family (supplementary) credit.\n",
    "\n",
    "**SEX**: Gender (**1 = male; 2 = female**).\n",
    "\n",
    "**EDUCATION**: Education (**1 = graduate school; 2 = university; 3 = high school; 4 = others**).\n",
    "\n",
    "**MARRIAGE**: Marital status (**1 = married; 2 = single; 3 = others**).\n",
    "\n",
    "**AGE**: Age (year).\n",
    "\n",
    "**PAY_1 - PAY_6**: History of past payment. We tracked the past monthly payment records (from April to September, 2005) as follows: PAY_1 = the repayment status in September, 2005; PAY_2 = the repayment status in August, 2005; . . .;PAY_6 = the repayment status in April, 2005. The measurement scale for the repayment status is: **-1 = pay duly; 1 = payment delay for one month; 2 = payment delay for two months; . . .; 8 = payment delay for eight months; 9 = payment delay for nine months and above.**\n",
    "\n",
    "**BILL_AMT1 - BILL_AMT6**: Amount of bill statement (NT dollar). BILL_AMT1 = amount of bill statement in September, 2005; BILL_AMT2 = amount of bill statement in August, 2005; . . .; BILL_AMT6 = amount of bill statement in April, 2005. \n",
    "\n",
    "**PAY_AMT1 - PAY_AMT6**: Amount of previous payment (NT dollar). PAY_AMT1 = amount paid in September, 2005; PAY_AMT2 = amount paid in August, 2005; . . .;PAY_AMT6 = amount paid in April, 2005.\n",
    "\n",
    "#### Exploring data - basic steps\n",
    "\n",
    "We have the follow steps to exploring data:\n",
    "\n",
    "    1 - Knows how many columns has the dataset (can be: characteristics, answers or metadata)\n",
    "\n",
    "    2 - How many rows (samples)\n",
    "\n",
    "    3 - What kind of characteristics exists? What are categories and what are numerics?\n",
    "\n",
    "    4 - Which is the data apparence according with its charecteristics\n",
    "        - For example: interval between values on numerics charecteristics or frequency of class in category charecteristics\n",
    "        \n",
    "    5 - Is there null data?\n",
    "\n",
    "Let´s pratice it!\n",
    "\n",
    "##### Exploring data - Question 1 / Question 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ad9ed2ab-75e9-4552-a701-ed8dc155a8fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30000, 25)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5f3f391-5347-4b5f-b46f-8a33833494ce",
   "metadata": {},
   "source": [
    "Now let´s check if there is duplicate data on our dataset, to do that, we will use the id column, because is supposed to be unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7050b492-fe0e-4744-952a-82d3d6c2a85b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ID', 'LIMIT_BAL', 'SEX', 'EDUCATION', 'MARRIAGE', 'AGE', 'PAY_1',\n",
       "       'PAY_2', 'PAY_3', 'PAY_4', 'PAY_5', 'PAY_6', 'BILL_AMT1', 'BILL_AMT2',\n",
       "       'BILL_AMT3', 'BILL_AMT4', 'BILL_AMT5', 'BILL_AMT6', 'PAY_AMT1',\n",
       "       'PAY_AMT2', 'PAY_AMT3', 'PAY_AMT4', 'PAY_AMT5', 'PAY_AMT6',\n",
       "       'default payment next month'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4a2afee8-0f73-4eb9-b5dd-93c15ac7f9f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29687"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ID'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa735ef-01c1-4867-bb84-43c2c3a11887",
   "metadata": {},
   "source": [
    "Explaing the command above, so fist we select a pandas column and got a Series with: *df['ID']* that is: dataframe + [' + column_name ' +]\n",
    "\n",
    "So on, we apply the nunique() function\n",
    "\n",
    "We figure out our first data problem, nunique function shows 29687 while our dataset has 30000 rows, this means that we have duplicate data\n",
    "\n",
    "Now, we need to investigate it and answer some question like:\n",
    "- May does only one ID is duplicate many times?\n",
    "- How many IDs are duplicate?\n",
    "\n",
    "To answer, we will use the value_counts function. This is similar to count/group by from SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0867523e-d382-4c20-8bb7-4702c19cf729",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_counts = df['ID'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d6e4e475-48aa-4aba-80bb-bc603a3011ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID\n",
       "ad23fe5c-7b09    2\n",
       "1fb3e3e6-a68d    2\n",
       "89f8f447-fca8    2\n",
       "7c9b7473-cc2f    2\n",
       "90330d02-82d9    2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7761bbc1-d0cd-4caa-b209-8d146145c87c",
   "metadata": {},
   "source": [
    "**head function returned the first 5 results. If needed you can modify the results quantity, passing head(number)**\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d3ec731b-cb6b-435c-aea1-fd1eaac10891",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID\n",
       "ad23fe5c-7b09    2\n",
       "1fb3e3e6-a68d    2\n",
       "89f8f447-fca8    2\n",
       "7c9b7473-cc2f    2\n",
       "90330d02-82d9    2\n",
       "2a793ecf-05c6    2\n",
       "75938fec-e5ec    2\n",
       "7be61027-a493    2\n",
       "a3a5c0fc-fdd6    2\n",
       "b44b81b2-7789    2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e4cfb43-0037-46ba-ae38-1910cd45bc5d",
   "metadata": {},
   "source": [
    "Now we will show the duplicated IDs grouping by values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e65f7ddb-ba4c-44e3-8a48-09823b725ec0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count\n",
       "1    29374\n",
       "2      313\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "188d9ed4-64c7-4ff0-b1ca-fda371ac0db2",
   "metadata": {},
   "source": [
    "We can observe that there are 29,374 IDs with one occurrence each, and 313 IDs with two occurrences each. This means that 313 IDs appear twice, and no ID appears three times"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4605c8e5-dc0b-4f87-a6f8-688b89b544e0",
   "metadata": {},
   "source": [
    "#### Boolean masks\n",
    "\n",
    "To assist with cleaning tasks, we will apply a technique known as a boolean mask or logical mask. This technique involves applying a filter to an array using a boolean condition, resulting in an array that contains entries meeting the specified condition, these results are **True or False**\n",
    "\n",
    "Let´s see how its works, using a fake data created with **numpy**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2c28e2d0-87c7-4db8-a4c0-134241486824",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([3, 4, 1, 4, 2])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "np.random.seed(seed=24) #to assure the numbers generated will be always the same\n",
    "random_integers = np.random.randint(low=1, high=5, size=100) # generate 100 random numbers beteween 1 and 5\n",
    "random_integers[:5] # shows the first 5 numbers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "decb9c5c-9fce-42c8-a479-91c0d131dba9",
   "metadata": {},
   "source": [
    "Now, let´s considering we want to know all location of the random numbers are equals to 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "53f1d5fa-53d4-4f33-99dd-369fea26e7c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "is_equal_to_3 = random_integers == 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "27759ba3-2dc5-4f7c-bdb3-45f9c9403023",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ True, False, False, False, False, False, False, False, False,\n",
       "       False, False, False, False,  True, False, False, False, False,\n",
       "       False, False, False,  True, False, False, False, False, False,\n",
       "       False, False, False,  True, False, False, False, False, False,\n",
       "       False,  True, False, False,  True,  True, False,  True, False,\n",
       "       False,  True, False, False,  True, False, False, False, False,\n",
       "       False, False,  True,  True, False, False, False, False, False,\n",
       "       False, False,  True, False, False, False,  True, False, False,\n",
       "        True, False, False,  True, False, False, False,  True,  True,\n",
       "       False,  True, False, False, False, False, False, False, False,\n",
       "       False, False,  True, False, False, False,  True,  True, False,\n",
       "       False])"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_equal_to_3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7ccf06b-5f79-4eb4-8245-7765a399efe2",
   "metadata": {},
   "source": [
    "Great! That´s it! Our first boolean mask, so the result is: when True, this means in that position the array contains a number 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daa59a36-9093-4d51-95a8-0ecc773793e7",
   "metadata": {},
   "source": [
    "Some more tricks with this technique, how many numbers are equals to 3?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "27a26209-a0ba-4820-80c0-29fa30ed43aa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(is_equal_to_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b82032ab-0c65-40a0-ae1d-54b7e08fbee8",
   "metadata": {},
   "source": [
    "We can also use the boolean mask as a index on the original array, for example, let´s use to grab all the elements 3 from the array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "59db3233-6e29-40e1-b110-dc3bcdc01ce4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_integers[is_equal_to_3]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "558e9cc3-0dc9-4762-aeb5-b683eb9e3ba3",
   "metadata": {},
   "source": [
    "you can invert the mask with ~ negative operator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "441d2010-0386-441d-b762-5519a2cc2e95",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([False,  True,  True,  True,  True,  True,  True,  True,  True,\n",
       "        True,  True,  True,  True, False,  True,  True,  True,  True,\n",
       "        True,  True,  True, False,  True,  True,  True,  True,  True,\n",
       "        True,  True,  True, False,  True,  True,  True,  True,  True,\n",
       "        True, False,  True,  True, False, False,  True, False,  True,\n",
       "        True, False,  True,  True, False,  True,  True,  True,  True,\n",
       "        True,  True, False, False,  True,  True,  True,  True,  True,\n",
       "        True,  True, False,  True,  True,  True, False,  True,  True,\n",
       "       False,  True,  True, False,  True,  True,  True, False, False,\n",
       "        True, False,  True,  True,  True,  True,  True,  True,  True,\n",
       "        True,  True, False,  True,  True,  True, False, False,  True,\n",
       "        True])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "~is_equal_to_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a108aeba-9751-4ec0-b68d-914c8af5fbda",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([4, 1, 4, 2, 2, 2, 1, 4, 4, 1, 4, 4, 4, 4, 4, 4, 4, 4, 2, 4, 4, 2,\n",
       "       4, 2, 4, 1, 1, 1, 4, 2, 2, 1, 4, 2, 2, 2, 4, 2, 4, 1, 4, 2, 2, 4,\n",
       "       1, 2, 2, 1, 2, 4, 2, 1, 4, 1, 2, 1, 4, 2, 4, 1, 2, 1, 1, 4, 2, 4,\n",
       "       1, 4, 4, 4, 4, 1, 4, 2, 4, 4, 2, 4])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_integers[~is_equal_to_3] # get all numbers are not equal to 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "990bf876-e657-4267-934f-1e0f0ffd872a",
   "metadata": {},
   "source": [
    "Perfect, so now we know something about how **boolean mask** or **boolean array** work, a thing that is usefull is apply the **.loc** method from a DataFrame to index their rows with boolean mask and the columns with labels"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2753be03-e179-4de6-89d8-e767180bcb21",
   "metadata": {},
   "source": [
    "Let´s apply our **boolean mask** technique to get the indexes from **Serie** id_counts where count is equal 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "93e5af4b-01ce-4042-8975-feb2251a4fa0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID\n",
       "ad23fe5c-7b09    True\n",
       "1fb3e3e6-a68d    True\n",
       "89f8f447-fca8    True\n",
       "7c9b7473-cc2f    True\n",
       "90330d02-82d9    True\n",
       "Name: count, dtype: bool"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dupe_mask = id_counts == 2\n",
    "dupe_mask[0:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c65a9e5a-d6ce-4934-93bd-afad2eb47f45",
   "metadata": {},
   "source": [
    "At **id_counts** Serie the account Id was used as Index, so we can access the Index to use our **boolean mask** and get what we need"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f8370c56-064f-4ee6-842f-2a521d730982",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ad23fe5c-7b09', '1fb3e3e6-a68d', '89f8f447-fca8', '7c9b7473-cc2f',\n",
       "       '90330d02-82d9'],\n",
       "      dtype='object', name='ID')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts.index[0:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "b2b4d403-1ae1-4c6b-b6a0-5a17737e43bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "313"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dupe_ids = id_counts.index[dupe_mask]\n",
    "dupe_ids = list(dupe_ids)\n",
    "len(dupe_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "538c10ba-4f1f-4b1d-a12d-e3ded199ca76",
   "metadata": {},
   "source": [
    "We changed the dupe_ids data type to list, because we need to use at next steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "66d7ca91-12f4-4388-9b1b-f3d57974873b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ad23fe5c-7b09',\n",
       " '1fb3e3e6-a68d',\n",
       " '89f8f447-fca8',\n",
       " '7c9b7473-cc2f',\n",
       " '90330d02-82d9']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dupe_ids[0:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd540879-819c-4359-9fa6-aaad81b4ceea",
   "metadata": {},
   "source": [
    "We already have the duplicate IDs, so, our next goal is to explore the accounts with this IDs and analyse the data, mainly the properties and its data, and checkout it if there is something diff in theses entries, to achieve that, we use **.isin** and **.loc** "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f17960ee-a5b7-42ba-8515-a01aa3511e75",
   "metadata": {},
   "source": [
    "First of all, we want to find the rows from our DF that contains the first 3 duplicated IDs, we will apply the **.isin** to create a a new **boolean mask** at **df['ID']** Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e55355ab-c769-48a1-bddd-3e13a51cbbd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_3_duplicated_ids_mask = df['ID'].isin(dupe_ids[0:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fb31c5c-e7be-4d47-aba4-addd1db69f4d",
   "metadata": {},
   "source": [
    "The next step is to use this mask in our Df with **.loc** method, as first parameter, the second parameter is \":\" meaning that all columns must be retrieved"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b5df2fca-97a2-449f-9ca1-1e9467fe7b1f",
   "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>ID</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_1</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5033</th>\n",
       "      <td>89f8f447-fca8</td>\n",
       "      <td>320000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>169371</td>\n",
       "      <td>172868</td>\n",
       "      <td>150827</td>\n",
       "      <td>8000</td>\n",
       "      <td>8000</td>\n",
       "      <td>5500</td>\n",
       "      <td>6100</td>\n",
       "      <td>6000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5133</th>\n",
       "      <td>89f8f447-fca8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16727</th>\n",
       "      <td>1fb3e3e6-a68d</td>\n",
       "      <td>80000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>27394</td>\n",
       "      <td>29922</td>\n",
       "      <td>31879</td>\n",
       "      <td>0</td>\n",
       "      <td>2000</td>\n",
       "      <td>2000</td>\n",
       "      <td>3000</td>\n",
       "      <td>2600</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16827</th>\n",
       "      <td>1fb3e3e6-a68d</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29685</th>\n",
       "      <td>ad23fe5c-7b09</td>\n",
       "      <td>50000</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>12882</td>\n",
       "      <td>8131</td>\n",
       "      <td>3983</td>\n",
       "      <td>3000</td>\n",
       "      <td>2871</td>\n",
       "      <td>1000</td>\n",
       "      <td>163</td>\n",
       "      <td>3983</td>\n",
       "      <td>3771</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29785</th>\n",
       "      <td>ad23fe5c-7b09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  ID  LIMIT_BAL  SEX  EDUCATION  MARRIAGE  AGE PAY_1  PAY_2  \\\n",
       "5033   89f8f447-fca8     320000    2          2         1   32     0      0   \n",
       "5133   89f8f447-fca8          0    0          0         0    0     0      0   \n",
       "16727  1fb3e3e6-a68d      80000    1          2         2   33     2      2   \n",
       "16827  1fb3e3e6-a68d          0    0          0         0    0     0      0   \n",
       "29685  ad23fe5c-7b09      50000    1          3         1   32     0      0   \n",
       "29785  ad23fe5c-7b09          0    0          0         0    0     0      0   \n",
       "\n",
       "       PAY_3  PAY_4  ...  BILL_AMT4  BILL_AMT5  BILL_AMT6  PAY_AMT1  PAY_AMT2  \\\n",
       "5033       0      0  ...     169371     172868     150827      8000      8000   \n",
       "5133       0      0  ...          0          0          0         0         0   \n",
       "16727      0      0  ...      27394      29922      31879         0      2000   \n",
       "16827      0      0  ...          0          0          0         0         0   \n",
       "29685      0      0  ...      12882       8131       3983      3000      2871   \n",
       "29785      0      0  ...          0          0          0         0         0   \n",
       "\n",
       "       PAY_AMT3  PAY_AMT4  PAY_AMT5  PAY_AMT6  default payment next month  \n",
       "5033       5500      6100      6000      5000                           0  \n",
       "5133          0         0         0         0                           0  \n",
       "16727      2000      3000      2600         0                           1  \n",
       "16827         0         0         0         0                           0  \n",
       "29685      1000       163      3983      3771                           1  \n",
       "29785         0         0         0         0                           0  \n",
       "\n",
       "[6 rows x 25 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[first_3_duplicated_ids_mask,:].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a27406b9-7025-41bf-ac90-8dd627dce281",
   "metadata": {},
   "source": [
    "Analysing the data, we can see the duplicated IDs has valid data row and one with only zeros. May this could be happened in reason of some\n",
    "SQL query error, or something else.\n",
    "\n",
    "We conclude that we need to remove the rows that contains zeros.\n",
    "\n",
    "One approach to achieve that is to find the rows where only contains zero, except in the first column (because is ID).\n",
    "We can find these entries using a **boolean matrix**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "3fc27cce-8553-4b31-bc85-3ab319131603",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_zero_mask = df == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "dd40c01d-6b36-4ae1-b9c1-bb49de0b6972",
   "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>ID</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_1</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID  LIMIT_BAL    SEX  EDUCATION  MARRIAGE    AGE  PAY_1  PAY_2  PAY_3  \\\n",
       "0  False      False  False      False     False  False  False  False  False   \n",
       "1  False      False  False      False     False  False  False  False   True   \n",
       "2  False      False  False      False     False  False   True   True   True   \n",
       "3  False      False  False      False     False  False   True   True   True   \n",
       "4  False      False  False      False     False  False  False   True  False   \n",
       "\n",
       "   PAY_4  ...  BILL_AMT4  BILL_AMT5  BILL_AMT6  PAY_AMT1  PAY_AMT2  PAY_AMT3  \\\n",
       "0  False  ...       True       True       True      True     False      True   \n",
       "1   True  ...      False      False      False      True     False     False   \n",
       "2   True  ...      False      False      False     False     False     False   \n",
       "3   True  ...      False      False      False     False     False     False   \n",
       "4   True  ...      False      False      False     False     False     False   \n",
       "\n",
       "   PAY_AMT4  PAY_AMT5  PAY_AMT6  default payment next month  \n",
       "0      True      True      True                       False  \n",
       "1     False      True     False                       False  \n",
       "2     False     False     False                        True  \n",
       "3     False     False     False                        True  \n",
       "4     False     False     False                        True  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_zero_mask.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "a986c71a-84d7-4f19-a9aa-f26fd0b8ddfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_zero_mask = df_zero_mask.iloc[:,1:].all(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f029325-e828-47bd-98e7-c1fc73873914",
   "metadata": {},
   "source": [
    "This created a **boolean serie**. Explaing: \n",
    "- **.iloc** -> return the row based at index (integer), for example df.iloc[3] -> return the second row\n",
    "- **the first \":\"** -> means that will return all rows\n",
    "- **the \"1:\"** -> means that will return from the second column until the last column\n",
    "- **all** ->  is a method that checks if all elements in a DataFrame are True (or a non-zero number if the DataFrame contains numeric values).\n",
    "- **axis==1** -> means the operation is performed across columns for each row. If all elements in a row (across all columns) are True, the result will be True for that row; otherwise, it will be False.\n",
    "\n",
    "In other words, this querie return a **boolean serie** containing **True** to indexes from **df_zero_mask** where all columns (exception the first) are True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9089d217-f997-461a-bfba-ff7ed08beffa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        False\n",
       "1        False\n",
       "2        False\n",
       "3        False\n",
       "4        False\n",
       "         ...  \n",
       "29995    False\n",
       "29996    False\n",
       "29997    False\n",
       "29998    False\n",
       "29999    False\n",
       "Length: 30000, dtype: bool"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_zero_mask"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "9223de32-088f-4520-9f8c-fa70faf04bd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "315"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(feature_zero_mask)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87bd067c-fd83-4ed7-9c47-cf43cfa47c84",
   "metadata": {},
   "source": [
    "This sum, confirm that we have 315 rows with only zeros columns. So, if we delete these rows, we will solve the duplicated IDs problem.\n",
    "\n",
    "Let´s clean the dataset creating a new copy without the rows with zero columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "15e8a107-2201-48d6-b429-d59fdd885dba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_clean_1 = df.loc[~feature_zero_mask,:].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1eea8bc8-4afc-4d4c-beaa-7ae87408f1c1",
   "metadata": {},
   "source": [
    "Done that, now we will check the Df shape and confirm the rows quantity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "f468c34a-9f61-42d1-929e-d12360946cdb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(29685, 25)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean_1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "be8950c3-052d-447c-b9fa-03da6966cb88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29685"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_clean_1['ID'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3de704b5-f45a-4153-802c-01fc9eea3ed6",
   "metadata": {},
   "source": [
    "Great! It´s work, now we have a Df without duplicated IDs. This was our first and complex cleaning dataset task"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f2c0a23-a2db-43ae-9e61-f8e6b39136c6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}