Missing-Data-Pandas/How to Deal with Missing Data in Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to Deal with Missing Data in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## The Employee Dataset\n",
    "\n",
    "We will be working with a very small Employee Dataset for this tutorial. Download the dataset in csv format from my Github repo and store it in your current working directory: [employees.csv](https://github.com/ChaitanyaBaweja/Programming-Tutorials/tree/master/Missing-Data-Pandas)\n",
    "\n",
    "Let’s import this dataset into Python and take a look at it. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  First Name  Gender  Salary Bonus % Senior Management             Team\n",
      "0    Douglas    Male   97308   6.945              TRUE        Marketing\n",
      "1     Thomas    Male   61933     NaN              TRUE              NaN\n",
      "2      Maria  Female  130590  11.858             FALSE          Finance\n",
      "3      Jerry    Male     NaN    9.34              TRUE          Finance\n",
      "4      Larry    Male  101004   1.389              TRUE  Client Services\n"
     ]
    }
   ],
   "source": [
    "# Importing libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Read csv file into a pandas dataframe\n",
    "df = pd.read_csv(\"employees.csv\")\n",
    "\n",
    "# Prints out the first few rows\n",
    "print(df.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1000 columns with 8 variables. You can get some basic statistics out using the `.dtypes` and `.describe()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First Name           object\n",
      "Gender               object\n",
      "Salary               object\n",
      "Bonus %              object\n",
      "Senior Management    object\n",
      "Team                 object\n",
      "dtype: object\n",
      "       First Name  Gender  Salary Bonus % Senior Management             Team\n",
      "count         931     852     998     997               932              957\n",
      "unique        201       3     993     968                 4               13\n",
      "top       Marilyn  Female  121160  12.182              TRUE  Client Services\n",
      "freq           11     428       2       3               467              105\n"
     ]
    }
   ],
   "source": [
    "print(df.dtypes)\n",
    "print(df.describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You would notice that the dtypes of all the columns is object. This shouldn't be the case for Salary, Senior Management and Bonus. This happens because we have **corrupt values in these columns**. Once we handle these missing values, we will convert these to the required type using `.astype()` method. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How to mark invalid/ corrupt values as missing\n",
    "\n",
    "Pandas treat None and NaN as essentially interchangeable for indicating missing or null values. Other values like na and ? are not recognized by Pandas by default. Let’s focus on the Salary Column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Salary\n",
      "0     97308\n",
      "1     61933\n",
      "2    130590\n",
      "3       NaN\n",
      "4    101004\n",
      "5    115163\n",
      "6     65476\n",
      "7     45906\n",
      "8       NaN\n",
      "9    139852\n",
      "Name: Salary, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print('Salary')\n",
    "print(df['Salary'].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the 8th row there’s a missing value and in the 3rd row there is a NA, which Pandas automatically fills with NaN. But what happens with other symbols like ?, n.a., etc. Let's look at the Gender column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      Male\n",
      "1      Male\n",
      "2    Female\n",
      "3      Male\n",
      "4      Male\n",
      "5      n.a.\n",
      "6    Female\n",
      "7    Female\n",
      "8       NaN\n",
      "9    Female\n",
      "Name: Gender, dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(df['Gender'].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We notice that n.a. isn't converted to NaN and remains in its original form. \n",
    "We can pass these formats in the `.read_csv()` method to allow Pandas to recognize them as corrupt values. Take a look:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0      Male\n",
      "1      Male\n",
      "2    Female\n",
      "3      Male\n",
      "4      Male\n",
      "5       NaN\n",
      "6    Female\n",
      "7    Female\n",
      "8       NaN\n",
      "9    Female\n",
      "Name: Gender, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# a list with all missing value formats\n",
    "missing_value_formats = [\"n.a.\",\"?\",\"NA\",\"n/a\", \"na\", \"--\"]\n",
    "df = pd.read_csv(\"employees.csv\", na_values = missing_value_formats)\n",
    "\n",
    "#print gender again\n",
    "print(df['Gender'].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Till now, our missing values had unique identifiers which, made them pretty easy to catch. But what happens when we get an invalid data type. I have designed a function that allows me to check for invalid data types in a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     97308.0\n",
      "1     61933.0\n",
      "2    130590.0\n",
      "3         NaN\n",
      "4    101004.0\n",
      "Name: Salary, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "missing_value_formats = [\"n.a.\",\"?\",\"NA\",\"n/a\", \"na\", \"--\"]\n",
    "df = pd.read_csv(\"employees.csv\", na_values = missing_value_formats)\n",
    "\n",
    "def make_int(i):\n",
    "    try:\n",
    "        return int(i)\n",
    "    except:\n",
    "        return pd.np.nan\n",
    "\n",
    "# apply make_int function to the entire series using map\n",
    "df['Salary'] = df['Salary'].map(make_int)\n",
    "print(df['Salary'].head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Marking missing values using isnull and notnull\n",
    "\n",
    "In Pandas, we have two functions for marking missing values:   \n",
    "- `isnull()` function to mark all of the NaN values in the dataset as True  \n",
    "- `notnull()` to mark all of the NaN values in the dataset as False.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    False\n",
      "1    False\n",
      "2    False\n",
      "3    False\n",
      "4    False\n",
      "5     True\n",
      "6    False\n",
      "7    False\n",
      "8     True\n",
      "9    False\n",
      "Name: Gender, dtype: bool\n",
      "0     True\n",
      "1     True\n",
      "2     True\n",
      "3     True\n",
      "4     True\n",
      "5    False\n",
      "6     True\n",
      "7     True\n",
      "8    False\n",
      "9     True\n",
      "Name: Gender, dtype: bool\n"
     ]
    }
   ],
   "source": [
    "print(df['Gender'].isnull().head(10)) # NaN values are marked True\n",
    "print(df['Gender'].notnull().head(10)) # non-NaN values are marked True\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the outputs of the `isnull` and `notnull` function for filtering. Let’s print all those rows of the database for which Gender is not missing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  First Name  Gender    Salary  Bonus % Senior Management             Team\n",
      "0    Douglas    Male   97308.0    6.945              True        Marketing\n",
      "1     Thomas    Male   61933.0      NaN              True              NaN\n",
      "2      Maria  Female  130590.0   11.858             False          Finance\n",
      "3      Jerry    Male       NaN    9.340              True          Finance\n",
      "4      Larry    Male  101004.0    1.389              True  Client Services\n"
     ]
    }
   ],
   "source": [
    "# returns True on indices for which Gender is not NaN\n",
    "null_filter = df['Gender'].notnull()\n",
    "print(df[null_filter].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Missing Value Statistics\n",
    "`isnull` and `notnull` can also be used to summarize missing values. \n",
    "\n",
    "To check if there are any missing values in our data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "print(df.isnull().values.any())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Total number of missing values per column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "First Name            70\n",
      "Gender               149\n",
      "Salary                 5\n",
      "Bonus %                4\n",
      "Senior Management     71\n",
      "Team                  48\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(df.isnull().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to remove rows with missing values\n",
    "\n",
    "Pandas library provides the `dropna()` function that can be used to drop either columns or rows with missing data. \n",
    "\n",
    "In the example below, we use dropna() to remove all rows with missing data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    }
   ],
   "source": [
    "# drop all rows with NaN values\n",
    "new_df = df.dropna(axis=0)\n",
    "\n",
    "# check if we have any NaN values in our dataset\n",
    "print(new_df.isnull().values.any())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use the `how` parameter.\n",
    "- `how = 'any'`: at least one value must be null.\n",
    "- `how = 'all'`: all values must be null."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all rows with atleast one NaN\n",
    "new_df = df.dropna(axis = 0, how ='any')  \n",
    "\n",
    "# drop all rows with all NaN\n",
    "new_df = df.dropna(axis = 0, how ='all')\n",
    "\n",
    "# drop all columns with atleast one NaN\n",
    "new_df = df.dropna(axis = 1, how ='any')\n",
    "\n",
    "# drop all columns with all NaN\n",
    "new_df = df.dropna(axis = 1, how ='all')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replacing NaNs with a single constant value\n",
    "\n",
    "We will use `fillna()` to replace missing values in the **Salary** column with 0.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3           0.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Salary'].fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also do the same for categorical variables like **Gender**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           Male\n",
       "1           Male\n",
       "2         Female\n",
       "3           Male\n",
       "4           Male\n",
       "         ...    \n",
       "995    No Gender\n",
       "996         Male\n",
       "997         Male\n",
       "998         Male\n",
       "999         Male\n",
       "Name: Gender, Length: 1000, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Gender'].fillna('No Gender')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replacing NaNs with the value from the previous row\n",
    "\n",
    "This is a common approach when filling missing values in image data. We use `method = 'pad'`. Let us try the same for the Salary Column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3      130590.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Salary'].fillna(method='pad')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replacing NaNs with the value from the next row\n",
    "We use `method = 'bfill'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3      101004.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Salary'].fillna(method='bfill')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replacing NaNs using Median/Mean of the column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3       90370.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using median\n",
    "df['Salary'].fillna(df['Salary'].median())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3       90522.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#using mean\n",
    "df['Salary'].fillna(int(df['Salary'].mean()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using the replace method\n",
    "The replace method is a more generic form of the fillna method. Here, we specify both the value to be replaced and the replacement value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3           0.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# will replace NaN value in Salary with value 0  \n",
    "df['Salary'].replace(to_replace = np.nan, value = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using the interpolate method\n",
    "`interpolate()` function is used to fill NaN values using various interpolation techniques. \n",
    "\n",
    "Let us interpolate the missing values using the Linear Interpolation method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       97308.0\n",
       "1       61933.0\n",
       "2      130590.0\n",
       "3      115797.0\n",
       "4      101004.0\n",
       "         ...   \n",
       "995    132483.0\n",
       "996     42392.0\n",
       "997     96914.0\n",
       "998     60500.0\n",
       "999    129949.0\n",
       "Name: Salary, Length: 1000, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Salary'].interpolate(method='linear', direction = 'forward')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}