03.1-Missing-Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missing Values\n",
    "\n",
    "Missing data, or missing values, occur when __no data__ / __no value__ is stored for certain observations within a variable. \n",
    "\n",
    "Incomplete data is an unavoidable problem in most data sources, and may have a significant impact on the conclusions that can be derived from the data. \n",
    "\n",
    "### Why is data missing?\n",
    "\n",
    "The source of missing data can be very different. These are just a few examples:\n",
    "\n",
    "- A value is missing because it was forgotten, lost or not stored properly\n",
    "- For a certain observation, the value does not exist\n",
    "- The value can't be known or identified\n",
    "\n",
    "In many organisations, information is collected into a form by a person talking with a client on the phone, or alternatively, by customers filling forms online. Often, the person entering the data does not complete all the fields in the form. Many of the fields are not compulsory, which may lead to missing values.\n",
    "\n",
    "The reasons for omitting the information can vary: perhaps the person does not want to disclose some information, for example income, or they do not know the answer, or the answer is not applicable for a certain circumstance, or on the contrary, the person in the organisation wants to spare the customer some time, and therefore omits asking questions they think are not so relevant.\n",
    "\n",
    "There are other cases where the value for a certain variable does not exist. For example, in the variable 'total debt as percentage of total income' (very common in financial data), if the person has no income, then the total percentage of 0 does not exist, and therefore it will be a missing value.\n",
    "\n",
    "It is important to understand **how the missing data are introduced in the dataset**, that is, the **mechanisms** by which missing information is introduced in a dataset. Depending on the mechanism, we may choose to process the missing values differently. In addition, by knowing the source of missing data, we may choose to take action to control that source and decrease the amount of missing information looking forward during data collection.\n",
    "\n",
    "\n",
    "### Missing Data Mechanisms\n",
    "\n",
    "There are 3 mechanisms that lead to missing data, 2 of them involve missing data randomly or almost-randomly, and the third one involves a systematic loss of data.\n",
    "\n",
    "#### Missing Completely at Random, MCAR:\n",
    "\n",
    "A variable is missing completely at random (MCAR) if the probability of being missing is the same for all the observations. \n",
    "When data is MCAR, there is absolutely no relationship between the data missing and any other values, observed or missing, within the dataset. In other words, those missing data points are a random subset of the data. There is nothing systematic going on that makes some data more likely to be missing than other. If values for observations are missing completely at random, then disregarding those cases would not bias the inferences made.\n",
    "\n",
    "\n",
    "#### Missing at Random, MAR: \n",
    "\n",
    "MAR occurs when there is a relationship between the propensity of missing values and the observed data. In other words, the probability of an observation being missing depends on available information (i.e., other variables in the dataset). For example, if men are more likely to disclose their weight than women, weight is MAR. The weight information will be missing at random for those men and women who do not disclose their weight, but as men are more prone to disclose it, there will be more missing values for women than for men.\n",
    "\n",
    "In a situation like the above, if we decide to proceed with the variable with missing values (in this case weight), we might benefit from including gender to control the bias in weight for the missing observations.\n",
    "\n",
    "\n",
    "#### Missing Not at Random, MNAR: \n",
    "\n",
    "Missing data is not at random (MNAR) when there is a mechanism or a reason why missing values are introduced in the dataset. For example, MNAR would occur if people failed to fill in a depression survey because of their level of depression. Here, the missing of data is related to the outcome, depression. Similarly, when a financial company asks for bank and identity documents from customers in order to prevent identity fraud, typically, fraudsters impersonating someone else will not upload documents, because they don't have them, because they are fraudsters. Therefore, there is a systematic relationship between the missing documents and the target we want to predict: fraud.\n",
    "\n",
    "Understanding the mechanism by which data is missing is important to decide which methods to use to impute the missing values.\n",
    "\n",
    "===================================================================================================="
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In this Demo:\n",
    "\n",
    "In the following cells we will:\n",
    "\n",
    "- Learn how to detect and quantify missing values\n",
    "\n",
    "- Try to identify the 3 different mechanisms of missing data introduction\n",
    "\n",
    "We will use the Loan book data from Lending Club and the Titanic dataset.\n",
    "\n",
    "- To download the datasets, please refer to the **Datasets** lecture in **Section 1** of the course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# to display the total number columns present in the dataset\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>pclass</th>\n",
       "      <th>survived</th>\n",
       "      <th>name</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>ticket</th>\n",
       "      <th>fare</th>\n",
       "      <th>cabin</th>\n",
       "      <th>embarked</th>\n",
       "      <th>boat</th>\n",
       "      <th>body</th>\n",
       "      <th>home.dest</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allen, Miss. Elisabeth Walton</td>\n",
       "      <td>female</td>\n",
       "      <td>29.0000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>24160</td>\n",
       "      <td>211.3375</td>\n",
       "      <td>B5</td>\n",
       "      <td>S</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>St Louis, MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Allison, Master. Hudson Trevor</td>\n",
       "      <td>male</td>\n",
       "      <td>0.9167</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22</td>\n",
       "      <td>S</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Miss. Helen Loraine</td>\n",
       "      <td>female</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Mr. Hudson Joshua Creighton</td>\n",
       "      <td>male</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>135.0</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Allison, Mrs. Hudson J C (Bessie Waldo Daniels)</td>\n",
       "      <td>female</td>\n",
       "      <td>25.0000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>113781</td>\n",
       "      <td>151.5500</td>\n",
       "      <td>C22</td>\n",
       "      <td>S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Montreal, PQ / Chesterville, ON</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pclass  survived                                             name     sex  \\\n",
       "0       1         1                    Allen, Miss. Elisabeth Walton  female   \n",
       "1       1         1                   Allison, Master. Hudson Trevor    male   \n",
       "2       1         0                     Allison, Miss. Helen Loraine  female   \n",
       "3       1         0             Allison, Mr. Hudson Joshua Creighton    male   \n",
       "4       1         0  Allison, Mrs. Hudson J C (Bessie Waldo Daniels)  female   \n",
       "\n",
       "       age  sibsp  parch  ticket      fare cabin embarked boat   body  \\\n",
       "0  29.0000      0      0   24160  211.3375    B5        S    2    NaN   \n",
       "1   0.9167      1      2  113781  151.5500   C22        S   11    NaN   \n",
       "2   2.0000      1      2  113781  151.5500   C22        S  NaN    NaN   \n",
       "3  30.0000      1      2  113781  151.5500   C22        S  NaN  135.0   \n",
       "4  25.0000      1      2  113781  151.5500   C22        S  NaN    NaN   \n",
       "\n",
       "                         home.dest  \n",
       "0                     St Louis, MO  \n",
       "1  Montreal, PQ / Chesterville, ON  \n",
       "2  Montreal, PQ / Chesterville, ON  \n",
       "3  Montreal, PQ / Chesterville, ON  \n",
       "4  Montreal, PQ / Chesterville, ON  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's load the titanic dataset\n",
    "data = pd.read_csv('../titanic.csv')\n",
    "\n",
    "# let's inspect the first 5 rows\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In python, the missing values are stored as NaN, see for example the first row for the variable Cabin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pclass          0\n",
       "survived        0\n",
       "name            0\n",
       "sex             0\n",
       "age           263\n",
       "sibsp           0\n",
       "parch           0\n",
       "ticket          0\n",
       "fare            1\n",
       "cabin        1014\n",
       "embarked        2\n",
       "boat          823\n",
       "body         1188\n",
       "home.dest     564\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we can quantify the total number of missing values using\n",
    "# the isnull method plus the sum method on the dataframe\n",
    "\n",
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 263 missing values for Age, 1014 for Cabin and 2 for Embarked."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pclass       0.000000\n",
       "survived     0.000000\n",
       "name         0.000000\n",
       "sex          0.000000\n",
       "age          0.200917\n",
       "sibsp        0.000000\n",
       "parch        0.000000\n",
       "ticket       0.000000\n",
       "fare         0.000764\n",
       "cabin        0.774637\n",
       "embarked     0.001528\n",
       "boat         0.628724\n",
       "body         0.907563\n",
       "home.dest    0.430863\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# alternatively, we can use the mean method after isnull\n",
    "# to visualise the percentage of\n",
    "# missing values for each variable\n",
    "\n",
    "data.isnull().mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are missing data in the variables Age (20% missing), Cabin -in which the passenger was traveling- (77% missing), and Embarked -the port from which the passenger got into the Titanic- (~0.2%  missing)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Mechanisms of Missing Data\n",
    "\n",
    "### Missing data Not At Random (MNAR): Systematic missing values\n",
    "\n",
    "In the Titanic dataset, both the missing values of the variables **age** and **cabin**, were introduced systematically. For many of the people who did not survive, the **age** they had or the **cabin** they were traveling in, could not be established. The people who survived could be otherwise asked for that information.\n",
    "\n",
    "Can we infer this by looking at the data?\n",
    "\n",
    "In a situation like this, we could expect a greater number of missing values for people who did not survive.\n",
    "\n",
    "Let's have a look."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's create a binary variable that indicates \n",
    "# whether the value of cabin is missing\n",
    "\n",
    "data['cabin_null'] = np.where(data['cabin'].isnull(), 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived\n",
       "0    0.873918\n",
       "1    0.614000\n",
       "Name: cabin_null, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's evaluate the percentage of missing values in\n",
    "# cabin for the people who survived vs the non-survivors.\n",
    "\n",
    "# the variable Survived takes the value 1 if the passenger\n",
    "# survived, or 0 otherwise\n",
    "\n",
    "# group data by Survived vs Non-Survived\n",
    "# and find the percentage of nulls for cabin\n",
    "data.groupby(['survived'])['cabin_null'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived\n",
       "0    0.873918\n",
       "1    0.614000\n",
       "Name: cabin, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# another way of doing the above, with less lines\n",
    "# of code :)\n",
    "\n",
    "data['cabin'].isnull().groupby(data['survived']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe that the percentage of missing values is higher for people who did not survive (87%), respect to people who survived (60%). This finding is aligned with our hypothesis that the data is missing because after people died, the information could not be retrieved.\n",
    "\n",
    "**Note**: Having said this, to truly underpin whether the data is missing not at random, we would need to get extremely familiar with the way data was collected. Analysing datasets, can only point us in the right direction or help us build assumptions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived\n",
       "0    0.234858\n",
       "1    0.146000\n",
       "Name: age_null, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's do the same for the variable age:\n",
    "\n",
    "# First we create a binary variable to indicates\n",
    "# whether the value of Age is missing\n",
    "\n",
    "data['age_null'] = np.where(data['age'].isnull(), 1, 0)\n",
    "\n",
    "# and then look at the mean in the different survival groups:\n",
    "data.groupby(['survived'])['age_null'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived\n",
       "0    0.234858\n",
       "1    0.146000\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# or the same with simpler code :)\n",
    "\n",
    "data['age'].isnull().groupby(data['survived']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, we observe a higher number of missing data for the people who did not survive the tragedy. The analysis therefore suggests that there is a systematic loss of data: people who did not survive tend to have more missing information. Presumably, the method chosen to gather the information, contributes to the generation of these missing data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing data Completely At Random (MCAR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>pclass</th>\n",
       "      <th>survived</th>\n",
       "      <th>name</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>ticket</th>\n",
       "      <th>fare</th>\n",
       "      <th>cabin</th>\n",
       "      <th>embarked</th>\n",
       "      <th>boat</th>\n",
       "      <th>body</th>\n",
       "      <th>home.dest</th>\n",
       "      <th>cabin_null</th>\n",
       "      <th>age_null</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Icard, Miss. Amelie</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113572</td>\n",
       "      <td>80.0</td>\n",
       "      <td>B28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>284</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Stone, Mrs. George Nelson (Martha Evelyn)</td>\n",
       "      <td>female</td>\n",
       "      <td>62.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>113572</td>\n",
       "      <td>80.0</td>\n",
       "      <td>B28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cincinatti, OH</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     pclass  survived                                       name     sex  \\\n",
       "168       1         1                        Icard, Miss. Amelie  female   \n",
       "284       1         1  Stone, Mrs. George Nelson (Martha Evelyn)  female   \n",
       "\n",
       "      age  sibsp  parch  ticket  fare cabin embarked boat  body  \\\n",
       "168  38.0      0      0  113572  80.0   B28      NaN    6   NaN   \n",
       "284  62.0      0      0  113572  80.0   B28      NaN    6   NaN   \n",
       "\n",
       "          home.dest  cabin_null  age_null  \n",
       "168             NaN           0         0  \n",
       "284  Cincinatti, OH           0         0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# In the titanic dataset, there are also missing values\n",
    "# for the variable Embarked.\n",
    "# Let's have a look.\n",
    "\n",
    "# Let's slice the dataframe to show only the observations\n",
    "# with missing values for Embarked\n",
    "\n",
    "data[data['embarked'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These 2 women were traveling together, Miss Icard was the maid of Mrs Stone.\n",
    "\n",
    "A priori, there does not seem to be an indication that the missing information in the variable Embarked is depending on any other variable, and the fact that these women survived, means that they could have been asked for this information.\n",
    "\n",
    "Very likely the values were lost at the time of building the dataset.\n",
    "\n",
    "If these values are MCAR, the probability of data being missing for these 2 women is the same as the probability for values to missing for any other person on the titanic. Of course this will be hard, if possible at all, to prove. But I hope this serves as a demonstration."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing data at Random (MAR)\n",
    "\n",
    "For this example, I will use the Lending Club loan book. I will look at the variables employer name (emp_title) and years in employment (emp_length), both declared by the borrowers at the time of applying for a loan. emp_title refers to the name of the company for which the borrower works. emp_length refers to how many years the borrower has worked for the company mentioned in emp_title. In this example, data missing in emp_title is associated with undeclared length of work in emp_length."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>emp_title</th>\n",
       "      <th>emp_length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ryder</td>\n",
       "      <td>&lt; 1 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10+ years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AIR RESOURCES BOARD</td>\n",
       "      <td>10+ years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>University Medical Group</td>\n",
       "      <td>1 year</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  emp_title emp_length\n",
       "0                       NaN  10+ years\n",
       "1                     Ryder   < 1 year\n",
       "2                       NaN  10+ years\n",
       "3       AIR RESOURCES BOARD  10+ years\n",
       "4  University Medical Group     1 year"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's load the columns of interest from the\n",
    "# Lending Club loan book dataset\n",
    "\n",
    "##########################################\n",
    "# Note: newer versions of pandas automatically cast strings as NA,\n",
    "# so to follow along with the notebook load the data as below if using\n",
    "# the latest pandas version. Loading method may need to be adjusted if\n",
    "# using older versions of pandas\n",
    "##########################################\n",
    "\n",
    "data = pd.read_csv('../loan.csv',\n",
    "                   usecols=['emp_title', 'emp_length'],\n",
    "                   na_values='',\n",
    "                   keep_default_na=False)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "emp_title     0.057988\n",
       "emp_length    0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's check the percentage of missing data\n",
    "data.isnull().mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Around 6% of the observations contain missing data for emp_title. No values are missing for emp_length."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of different employer names: 299273\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([nan, 'Ryder', 'AIR RESOURCES BOARD', 'University Medical Group',\n",
       "       'Veolia Transportaton', 'Southern Star Photography',\n",
       "       'MKC Accounting ', 'Starbucks', 'Southwest Rural metro', 'UCLA',\n",
       "       'Va. Dept of Conservation/Recreation', 'Target', 'SFMTA',\n",
       "       'Internal revenue Service', \"Chin's Restaurant\", 'Duracell',\n",
       "       'Connection Inspection', 'Network Interpreting Service',\n",
       "       'Archdiocese of Galveston Houston', 'Osram Sylvania'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's insptect the different employer names\n",
    "\n",
    "# number of different employers names\n",
    "print('Number of different employer names: {}'.format(\n",
    "    len(data.emp_title.unique())))\n",
    "\n",
    "# a few examples of employers names\n",
    "data.emp_title.unique()[0:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We observe the missing information (nan), and several different employer names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['10+ years', '< 1 year', '1 year', '3 years', '8 years', '9 years',\n",
       "       '4 years', '5 years', '6 years', '2 years', '7 years', 'n/a'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's inspect the variable emp_length\n",
    "data.emp_length.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The value 'n/a', \"not applicable\" is the one we are interested in. The customer can't enter an employment length, perhaps because they are not employed. They could be students, retired, self-employed, or work in the house."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10+ years    0.328573\n",
       "2 years      0.088880\n",
       "< 1 year     0.079566\n",
       "3 years      0.078913\n",
       "1 year       0.064341\n",
       "5 years      0.062774\n",
       "4 years      0.059196\n",
       "n/a          0.050514\n",
       "7 years      0.050254\n",
       "8 years      0.049534\n",
       "6 years      0.048401\n",
       "9 years      0.039055\n",
       "Name: emp_length, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's look at the percentage of borrowers within\n",
    "# each label / category of emp_length variable\n",
    "\n",
    "# value counts counts the observations per category\n",
    "# if we divide by the number of observations (len(data))\n",
    "# we obtain the percentages of observations per category\n",
    "\n",
    "data.emp_length.value_counts() / len(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5 % of the borrowers in the dataset have disclosed 'n/a' for emp_lenght. From previous cells, we know that ~5% of the borrowers present missing data for emp_title. Could there be a relationship between missing values in emp_title and 'n/a' in emp_length? Let's have a look."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'10+ years': '10+ years',\n",
       " '< 1 year': '0-10 years',\n",
       " '1 year': '0-10 years',\n",
       " '3 years': '0-10 years',\n",
       " '8 years': '0-10 years',\n",
       " '9 years': '0-10 years',\n",
       " '4 years': '0-10 years',\n",
       " '5 years': '0-10 years',\n",
       " '6 years': '0-10 years',\n",
       " '2 years': '0-10 years',\n",
       " '7 years': '0-10 years',\n",
       " 'n/a': 'n/a'}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the variable emp_length has many categories.\n",
    "# I will summarise it into 3 for simplicity:\n",
    "# '0-10 years' or '10+ years' or 'n/a'\n",
    "\n",
    "# let's build a dictionary to re-map emp_length to just 3 categories:\n",
    "\n",
    "length_dict = {k: '0-10 years' for k in data.emp_length.unique()}\n",
    "length_dict['10+ years'] = '10+ years'\n",
    "length_dict['n/a'] = 'n/a'\n",
    "\n",
    "# let's look at the dictionary\n",
    "length_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['10+ years', '0-10 years', 'n/a'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's re-map the emp_length variable\n",
    "\n",
    "data['emp_length_redefined'] = data.emp_length.map(length_dict)\n",
    "\n",
    "# let's see if it worked\n",
    "data.emp_length_redefined.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It worked, our new variable has only 3 different categories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "emp_length_redefined\n",
       "10+ years     0.054006\n",
       "0-10 years    0.082457\n",
       "n/a           0.863537\n",
       "Name: emp_length, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's calculate the proportion of working years\n",
    "# with the same employer for those who miss data on emp_title\n",
    "\n",
    "# data[data.emp_title.isnull()] represents the observations\n",
    "# with missing data in emp_title. I use this below:\n",
    "\n",
    "# Calculations:\n",
    "# number of borrowers for whom employer name is missing\n",
    "# aka, not employed people\n",
    "not_employed = len(data[data.emp_title.isnull()])\n",
    "\n",
    "# % of borrowers for whom employer name is missing\n",
    "# within each category of employment length\n",
    "\n",
    "data[data.emp_title.isnull()].groupby(\n",
    "    ['emp_length_redefined'])['emp_length'].count().sort_values() / not_employed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above output tells us the following:\n",
    "From all the borrowers who show missing information in emp_title, so those who are not employed:\n",
    "- 5.4% declared more than 10 years in emp_length (maybe they are self-employed)\n",
    "- 8.4% declared between 0-10 years in emp_length (same as above, potentially self-employed)\n",
    "- 86.3 % declared n/a in emp_length (maybe they are students, or work at home, or retired)\n",
    "\n",
    "The majority of the missing values in emp_title coincides with the label 'n/a' of emp_length (86%). This supports the idea that the 2 variables are related. Therefore, the missing data in emp_title, is MAR."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "emp_length_redefined\n",
       "n/a           0.000467\n",
       "10+ years     0.345475\n",
       "0-10 years    0.654059\n",
       "Name: emp_length, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's do the same for those bororwers who reported\n",
    "# the employer name\n",
    "\n",
    "# number of borrowers for whom employer name is present:\n",
    "# employed people\n",
    "employed = len(data.dropna(subset=['emp_title']))\n",
    "\n",
    "# % of borrowers within each category\n",
    "data.dropna(subset=['emp_title']).groupby(\n",
    "    ['emp_length_redefined'])['emp_length'].count().sort_values() / employed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The number of borrowers who have reported an employer name (emp_title) and yet indicate 'n/a' as emp_length is minimal. This further supports that the missing values in emp_title are related to 'n/a' in emp_length.\n",
    "\n",
    "'n/a' in emp_length could be supplied by people who are retired, or students, or self-employed. In those cases there would not be a number of years in employment to provide, therefore the customer would enter 'n/a' in emp_length and leave the form empty for 'emp_title'.\n",
    "\n",
    "A missing value in the variable emp_title depends on, or is related to, the 'n/a' label in the variable emp_length. This is an example of MAR. The value in emp_title is missing at random for those customer who are not employed, but emp_length being missing is influenced by emp_title being missing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "**That is all for this demonstration. I hope you enjoyed the notebook, and see you in the next one.**"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}