data-missing-values.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95f0a171",
   "metadata": {},
   "source": [
    "(missing-values)=\n",
    "# Missing Values\n",
    "\n",
    "## Introduction\n",
    "\n",
    "In this chapter, we'll look at the tools and tricks for dealing with missing values.  We'll start by discussing some general tools for working with missing values recorded as `NA`s. We'll then explore the idea of implicitly missing values, values are that are simply absent from your data, and show some tools you can use to make them explicit.\n",
    "We'll finish off with a related discussion of empty groups, caused by categories that don't appear in the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51a55374",
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import matplotlib_inline.backend_inline\n",
    "\n",
    "# Plot settings\n",
    "plt.style.use(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/raw/main/plot_style.txt\"\n",
    ")\n",
    "matplotlib_inline.backend_inline.set_matplotlib_formats(\"svg\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17575f3a",
   "metadata": {},
   "source": [
    "### Prerequisites\n",
    "\n",
    "This chapter will use the **pandas** data analysis package."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8757f369",
   "metadata": {},
   "source": [
    "## Explicit Missing Values\n",
    "\n",
    "To begin, let's explore a few handy tools for creating or eliminating missing explicit values, i.e. cells where you see an `NA` or `nan`.\n",
    "\n",
    "### Types of Missing Values\n",
    "\n",
    "One thing to note about missing values in **pandas** is that they are not all created alike!\n",
    "\n",
    "For example, real numbers in **pandas** (such as the `float64` dtype) uses a 'nan' (aka, Not a Number):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "535ef959",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame([5, 27.3, np.nan, -16], columns=[\"numbers\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b1c9ea7",
   "metadata": {},
   "source": [
    "But this isn't the only way! We can also use Python's built-in `None` value (which, here, gets converted to a NaN because the valid values are all floating point numbers) and **pandas**' `pd.NA`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf317bce",
   "metadata": {},
   "outputs": [],
   "source": [
    "numbers = pd.DataFrame([pd.NA, 27.3, np.nan, -16, None], columns=[\"numbers\"])\n",
    "numbers"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2dda456",
   "metadata": {},
   "source": [
    "However, with the object data type (the default for strings), the types can co-exist:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1c864d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "fruits = pd.DataFrame(\n",
    "    [\"orange\", np.nan, \"apple\", None, \"banana\", pd.NA], columns=[\"fruit\"]\n",
    ")\n",
    "fruits"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b5a2849",
   "metadata": {},
   "source": [
    "Both of these types of missing value can be found using **pandas** `.isna()` function. This returns a new column of boolean values that are `True` if the value is any kind of missing value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cbcfe630",
   "metadata": {},
   "outputs": [],
   "source": [
    "fruits.isna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fd6764d",
   "metadata": {},
   "source": [
    "As a convenience, there is also a `notna()` function."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "473f8756",
   "metadata": {},
   "source": [
    "### Dealing with Explicit Missing Values\n",
    "\n",
    "There are various options for dealing with missing values. The `fillna()` function achieves this. Let's take a look at it with some test data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c96f89e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df = pd.DataFrame(\n",
    "    [\n",
    "        [np.nan, 2, None, 0],\n",
    "        [3, 4, np.nan, 1],\n",
    "        [5, np.nan, np.nan, pd.NA],\n",
    "        [np.nan, 3, np.nan, 4],\n",
    "    ],\n",
    "    columns=list(\"ABCD\"),\n",
    ")\n",
    "\n",
    "nan_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53d12a89",
   "metadata": {},
   "source": [
    "First, we can just fill any missing values with a single fixed value:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6bc5fe2",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af8e2cb7",
   "metadata": {},
   "source": [
    "This can be done on a by-column basis; here we replace all NaN elements in column ‘A’, ‘B’, ‘C’, and ‘D’, with 0, 1, 2, and 3 respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e65b67c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.fillna(value={\"A\": 0, \"B\": 1, \"C\": 2, \"D\": 3})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2544fe5a",
   "metadata": {},
   "source": [
    "We can also propagate non-null values forward or backward (relative to the index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a19e196",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.ffill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b5b001e",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.bfill()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10696d53",
   "metadata": {},
   "source": [
    "The forward fill and backward fill options are particularly useful for time series—but be careful using them if you're doing a forecasting exercise!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca7e4ca9",
   "metadata": {},
   "source": [
    "Another feature of all of these functions is that you can limit the number of NaNs that get replaced using the `limit=` keyword argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "558e7a23",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.fillna(value={\"A\": 0, \"B\": 1, \"C\": 2, \"D\": 3}, limit=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99b94a01",
   "metadata": {},
   "source": [
    "Of course, another option might be just to filter out the missing values altogether. There are a couple of ways to do this depending if you want to remove entire rows (`axis=0`) or columns (`axis=1`)---but in this case, as there is at least one NaN in each column though, there will be no data left!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e3a81c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df[\"A\"].dropna(axis=0)  # on a single column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c1e312e",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.dropna(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bc57652",
   "metadata": {},
   "source": [
    "`dropna()` takes some keyword arguments too; for example `how=\"all\"` only drops a column or row if *all* of the values are NA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3296ea35",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df.dropna(how=\"all\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "512f8246",
   "metadata": {},
   "source": [
    "There is a `thresh` keyword (for threshold)—this allows you to keep only rows or columns containing at most a certain number of missing observations."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "095f53a5",
   "metadata": {},
   "source": [
    "Another way to filter out nans is to use the same filtering methods you would use normally, via boolean columns, in combination with the `.notna()` function. In the below example, we see all columns for the rows for which A is not NA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3e1af81",
   "metadata": {},
   "outputs": [],
   "source": [
    "nan_df[nan_df[\"A\"].notna()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1588f63",
   "metadata": {},
   "source": [
    "### Adding NA values\n",
    "\n",
    "Sometimes you'll hit the opposite problem where some concrete value actually represents a missing value. This typically arises in data generated by older software that doesn't have a proper way to represent missing values, so it must instead use some special value like 99 or -999.\n",
    "\n",
    "If possible, handle this when reading in the data, for example, by using the `na_values=` keyword argument when calling `pd.read_csv()`. If you discover the problem later, or your data source doesn't provide a way to handle it on reading the file, you can use a range of options to replace the given data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45668d12",
   "metadata": {},
   "outputs": [],
   "source": [
    "stata_df = pd.DataFrame([[3, 4, 5], [-7, 4, -99], [-99, 6, 5]], columns=list(\"ABC\"))\n",
    "\n",
    "stata_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81685807",
   "metadata": {},
   "source": [
    "The easiest option is probably `.replace()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48d75a09",
   "metadata": {},
   "outputs": [],
   "source": [
    "stata_df.replace({-99: np.nan})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d4d3d3d",
   "metadata": {},
   "source": [
    "Because `.replace()` accepts a dictionary, it's possible to replace several values at once:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12cc567a",
   "metadata": {},
   "outputs": [],
   "source": [
    "stata_df.replace({-99: np.nan, -7: np.nan})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4df2baca",
   "metadata": {},
   "source": [
    "Note that this applies to *every* column in the dataframe. To apply it to just one, just select that specific column."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85307077",
   "metadata": {},
   "source": [
    "## Implicit Missing Values\n",
    "\n",
    "So far we've talked about missing values that are **explicitly** missing, i.e. you can see an `NA` or similar in your data.\n",
    "But missing values can also be **implicitly** missing, if an entire row of data is simply absent from the data.\n",
    "Let's illustrate the difference with a simple data set that records the price of some stock each quarter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "057c5203",
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks = pd.DataFrame(\n",
    "    {\n",
    "        \"year\": [2020, 2020, 2020, 2020, 2021, 2021, 2021],\n",
    "        \"qtr\": [1, 2, 3, 4, 2, 3, 4],\n",
    "        \"price\": [1.88, 0.59, 0.35, np.nan, 0.92, 0.17, 2.66],\n",
    "    }\n",
    ")\n",
    "stocks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0724a86b",
   "metadata": {},
   "source": [
    "This dataset has two missing observations:\n",
    "\n",
    "-   The `price` in the fourth quarter of 2020 is explicitly missing, because its value is `NA`.\n",
    "\n",
    "-   The `price` for the first quarter of 2021 is implicitly missing, because it simply does not appear in the dataset.\n",
    "\n",
    "One way to think about the difference is with this Zen-like koan:\n",
    "\n",
    "> An explicit missing value is the presence of an absence.\n",
    ">\n",
    "> An implicit missing value is the absence of a presence.\n",
    "\n",
    "Sometimes you want to make implicit missings explicit in order to have something physical to work with.\n",
    "In other cases, explicit missings are forced upon you by the structure of the data and you want to get rid of them.\n",
    "The following sections discuss some tools for moving between implicit and explicit missingness."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "234b8547",
   "metadata": {},
   "source": [
    "### Pivoting\n",
    "\n",
    "You've already seen one tool that can make implicit missings explicit and vice versa: pivoting. Making data wider can make implicit missing values explicit because every combination of the rows and new columns must have some value For example, if we pivot `stocks` to put `quarter` in the columns (and make `year` the index), both missing values become explicit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87ab37d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.pivot(columns=\"qtr\", values=\"price\", index=\"year\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83096aeb",
   "metadata": {},
   "source": [
    "By default, making data longer preserves explicit missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cce8b43",
   "metadata": {},
   "source": [
    "### Missing Values in Categorical Variables\n",
    "\n",
    "A final type of missingness is the empty group, a group that doesn't contain any observations, which can arise when working with categorical data. \n",
    "\n",
    "For example, imagine we have a dataset that contains some health information about people:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92a914e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "health = pd.DataFrame(\n",
    "    {\n",
    "        \"name\": [\"Ikaia\", \"Oletta\", \"Leriah\", \"Dashay\", \"Tresaun\"],\n",
    "        \"smoker\": [\"no\", \"no\", \"previously\", \"no\", \"yes\"],\n",
    "        \"age\": [34, 88, 75, 47, 56],\n",
    "    }\n",
    ")\n",
    "health[\"smoker\"] = health[\"smoker\"].astype(\"category\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0dde79cf",
   "metadata": {},
   "source": [
    "Now we drop the last row of data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc904247",
   "metadata": {},
   "outputs": [],
   "source": [
    "health_cut = health.iloc[:-1, :]\n",
    "health_cut"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ae95b5f",
   "metadata": {},
   "source": [
    "The value 'yes' for smoker now doesn't (seem to) appear anywhere in our dataframe. But if we run `value_counts()` to get a count of the number of each type of category, you'll see that the data frame 'remembers' that there's a 'yes' category that isn't currently present:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "546bc395",
   "metadata": {},
   "outputs": [],
   "source": [
    "health_cut[\"smoker\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bde48398",
   "metadata": {},
   "source": [
    "You'll see the same thing happen with a `groupby()` operation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efb6c6ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "health_cut.groupby(\"smoker\", observed=False)[\"age\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a2af19e",
   "metadata": {},
   "source": [
    "You can see here that, because we took the mean of a number that doesn't exist, we got a NaN in place of a real value for the yes row (but there is a 'yes' row)."
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "encoding": "# -*- coding: utf-8 -*-",
   "formats": "md:myst",
   "main_language": "python"
  },
  "kernelspec": {
   "display_name": "codeforecon",
   "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.10.16"
  },
  "toc-showtags": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}