{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning and Normalizing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notebook Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## import modules\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Notebook Settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Enable multiple outputs from jupyter cells\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "## disable the Pandas \"setting a copy of a slice\" warning\n",
    "# pd.options.mode.chained_assignment = None\n",
    "\n",
    "## set default number of DataFrame rows printed to 8\n",
    "pd.set_option('display.max_rows', 20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The Data\n",
    "\n",
    "This notebook uses the following data set:\n",
    "\n",
    "> `data > customers.csv`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data is intended to track signups for credit cards.  The customer signs up under one or more promotional programs:  'bronze', 'silver' or 'gold'.  They are also perceived to be either from a low-population (Rural) or higher-population (Urban) area.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Fw4qNQgtX4mr",
    "tags": []
   },
   "outputs": [],
   "source": [
    "## import data\n",
    "df_customer = pd.read_csv(\"data/customers.csv\")\n",
    "df_customer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with Strings in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The <B>.str</B> attribute of the pandas <B>Series</B> gives us access to string methods and functionality when working with string data.  We'll find this extremely helpful in text-related situations.\n",
    "\n",
    "First, let's look at how strings work with basic Python.  Some useful string methods are:  \n",
    "\n",
    "- `.find(\"pattern\")`: find the index of a pattern inside a string\n",
    "- `.count(\"pattern\")`: count the occurences of a pattern in the string\n",
    "- `.upper()`: returns an uppercase copy of the string\n",
    "- `.startswith(\"pattern\")`: test if a pattern starts a string\n",
    "- `.endswith(\"pattern\")`: test if a pattern ends a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zqqe5_JGPEnK",
    "outputId": "41137391-9575-4ff0-ebfa-398a3e2b8fd1",
    "tags": []
   },
   "outputs": [],
   "source": [
    "file1 = \"january_data.csv\"\n",
    "file2 = \"january_data.xlsx\"\n",
    "\n",
    "file1.startswith(\"jan\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zqqe5_JGPEnK",
    "outputId": "41137391-9575-4ff0-ebfa-398a3e2b8fd1",
    "tags": []
   },
   "outputs": [],
   "source": [
    "file2.endswith(\".csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "file2.upper()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### \"Parsing\" (Spliting and Slicing) Strings\n",
    "\n",
    "The two principal ways we like to \"subdivide\" strings are splitting and slicing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# splitting on a delimiter\n",
    "line = 'I,have,happy,feet'\n",
    "items = line.split(',')\n",
    "items\n",
    "\n",
    "# no argument: splitting on 'whitespace' \n",
    "# (includes any spaces, tabs, newlines)\n",
    "line = 'I   have happy  feet.   '\n",
    "items = line.split()\n",
    "items\n",
    "\n",
    "# slicing\n",
    "date = '20250309'\n",
    "year = date[0:4]\n",
    "year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenate Strings\n",
    "\n",
    "We can also concatenate strings using the `+` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "\"Here is the first file: \" + file1"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas String Methods\n",
    "---\n",
    "Pandas provides many very powerful tools for manipulating strings, based on the tools found in standard Python. \n",
    "\n",
    "### Vectorized Operations\n",
    "\n",
    "One of the most important features of working with Pandas string columns is that operations are vectorized/broadcast.  Instead of looping through each item, we can just work with the entire column at once when using a Pandas DataFrame.\n",
    "\n",
    "To access string methods, we first use the `.str` accessor. Most of the DataFrame/Series string methods have similar names as their base Python counterparts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# produce a new Series with FirstName uppercased\n",
    "df_customer.FirstName.str.upper()                    # df_customer['FirstName']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "You can also concatenate columns together.  Even the static string \", \" is broadcast across items in the resulting Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# produce a new Series with each corresponding value of 2 Series concatena\n",
    "df_customer.LastName + \", \" + df_customer.FirstName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.split()` method\n",
    "\n",
    "As `split()` returns a list of values based on a delimiter, we can broadcast this across rows in a Series, resulting in a DataFrame with two values per row. \n",
    "\n",
    "If we wish to split on whitespace, we leave the position argument out.  This will see \"one or more spaces\" as the \"split character\".  \n",
    "\n",
    "In the following example we use the `expand=True` argument to get all the elements separated into their own columns.\n",
    "\n",
    "Note that absent values are noted with the `None` value.  This is not the pandas/numpy `NaN` value; it is Python's version of it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_fixed_names = df_customer.FirstName.str.split(expand=True)\n",
    "df_fixed_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### setting a slice of a DataFrame\n",
    "\n",
    "The None values in the middle initial column should be set to be proper missing values (numpy's `NaN` value).  Currently thanks to the split, they are `None`.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we need to identify which rows have empty values.  \n",
    "\n",
    "Then, we can use the below code inside of a call to `.loc[]`, but do all of that on the left side of the `=` assignment operator. On the right side of the operator we assign `np.nan` (the numpy/pandas null value, a.k.a. \"Not a Number\").\n",
    "\n",
    "Unfortunately, comparisons to `None` do not work in pandas indexing tests, but... \n",
    "Fortunately the pandas `.isnull()` method will return `True` for `None` (as well as `NaN`) values.  \n",
    "\n",
    "So what we are saying below is \"set all of the empty values in the customer DataFrame identified by `.loc` column `1` to have the value `NaN`.\"\n",
    "\n",
    "We'll discuss missing data, `NaN` and `.isnull()` more thoroughly below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# where column 1 is empty string, set to np.nan (Not a Number)\n",
    "df_fixed_names.loc[  df_fixed_names[1].isnull(), 1  ] = np.nan\n",
    "df_fixed_names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now save these columns back into our main customer DataFrame.  Because the new Series are exactly the same length as the DataFrame from which they were generated, the values match when we assign them back.  (Note that `MiddleInitial` is the last column - we could use `.reindex()` to place it with the names if we desired.\n",
    "\n",
    "Also note that I can use the attribute syntax to access the `FirstName` column, but to create a <I>new</I> column I must use the subscript."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# note that the new MiddleInitial appears at the end of the table\n",
    "df_customer.FirstName = df_fixed_names[0]\n",
    "df_customer['MiddleInitial'] = df_fixed_names[1]\n",
    "df_customer.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.extract()` method:  apply a pattern to isolate and extract parts of a string\n",
    "\n",
    "We note that <B>SignUpProgram</B> has what appears to be a somewhat inconsistent set of values.  We can use <B>.unique()</B> to show all unique values in the Series, and <B>.value_counts()</B> to see how many of each:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_customer.SignUpProgram.unique()\n",
    "print()\n",
    "\n",
    "df_customer.SignUpProgram.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We would prefer this column be consistent:  \"bronze\", \"silver\" or \"gold\".  Fortunately each of these values features that word somewhere, so we can just isolate it.  \n",
    "\n",
    "We will use Pandas string methods to clean up this column. Specifically, we are going to do the following things:\n",
    "1. convert all observations to lower case\n",
    "2. extract the patterns ('gold', 'silver', 'bronze')\n",
    "3. convert these patterns to title case\n",
    "\n",
    "When extracting the pattern, we use the `|` operator to specify an `or`. What we are saying is \"find the pattern *gold*, or the pattern *silver*, or the pattern *bronze*. Then, we use parentheses to say that we want to capture that pattern and extract it.\n",
    "\n",
    "This is technically a type of regular expression, which is a powerful set of tools for specifying string patterns. For a thorough introduction to using regular expressions with Python see the following:\n",
    "\n",
    "> [\"Regular Expressions: Regexes in Python (Part 1)\"](https://realpython.com/regex-python/) from the realpython.org website"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_customer['SignUpProgramClean'] = (\n",
    "    df_customer.SignUpProgram.str.lower()\n",
    "                                .str.extract('(gold|silver|bronze)', expand=False)\n",
    "                                .str.title()\n",
    ") \n",
    "# expand=False ensures that only a Series, and not a DataFrame,\n",
    "# will be returned (allowing us to continue with .str.title())\n",
    "\n",
    "# (note I use parentheses to allow a statement to occupy multiple lines.\n",
    "#  You could also use backslash, the 'line continuation character')\n",
    "\n",
    "# use .value_counts() to see how many of each were extracted\n",
    "df_customer.SignUpProgramClean.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# cross-tabulate the old value to the new to confirm our approach was correct\n",
    "pd.crosstab(df_customer.SignUpProgramClean, df_customer.SignUpProgram)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a more comprehensive treatment of string methods available in the Pandas module, see the following resources:\n",
    "\n",
    "> [\"Working with Strings\"](https://jakevdp.github.io/PythonDataScienceHandbook/03.10-working-with-strings.html) from the Python Data Science Handbook\n",
    "\n",
    "> [\"Working with Text Data\"](https://pandas.pydata.org/pandas-docs/stable/user_guide/text.html#working-with-text-data) from the Pandas User Guide"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.replace()` method\n",
    "\n",
    "This method works through a Series (or DataFrame) performing replacements, returning a new Series (or DataFrame). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_customer['urban_rural_alt'] = (\n",
    "    df_customer.UrbanRural.str.replace('Urban', 'City Folk')\n",
    ")\n",
    "df_customer.urban_rural_alt.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll managing these \"missing\" values in the next section."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Categorical Data\n",
    "\n",
    "Any column that contains a limited number of unique values (`UrbanRural` with 'urban' and 'rural', or `SignUpProgramClean` with 'gold', 'silver' or 'bronze') contains categorical information (as contrasted with values like 'LastName' or 'Birthdate', which will have many different values).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's look at the data again:  keep in mind the new columns were added to the end\n",
    "df_customer\n",
    "\n",
    "df_customer.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The pandas dtype 'category' can be used to optimize our work with categorical values. \n",
    "\n",
    "The 'category' dtype specifies a fixed number of unique values, potentially with a specified order.  It should be noted too that because it is aware of valid types and internally uses an integer to represent each value (rather than repeated strings), the category type is more memory and time efficient, and will ensure the column's integrity (i.e., prevent unintended values from being set).  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 'object' dtype (default for string values)\n",
    "df_customer.UrbanRural.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can cast such values to Categorical using `pd.Categorical()` :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer['UrbanRural_cat'] = pd.Categorical(df_customer.UrbanRural)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now when we view the `.dtype` we see not only the new type, but we also see all unique values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.UrbanRural_cat.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Harmonizing Levels\n",
    "\n",
    "As often happens, it looks like there are some odd values, this time in the `UrbanRural` column, and in this case these odd values should be considered as missing or empty.  They may have been manually entered, or entered by different processes.  \n",
    "\n",
    "We need to set the '*\\*unknown\\**', and the '*\\*Missing\\**' values to be missing.  With plain string type values we could do this overtly using code like the following, with `.loc[]` and the `.isin()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make new column\n",
    "df_customer['UrbanRural2'] = df_customer.UrbanRural\n",
    "\n",
    "# use .loc[] with the .isin() test to identify these values; then \n",
    "df_customer.loc[   df_customer.UrbanRural2.isin(['*unknown*','*Missing*']), 'UrbanRural2'   ] = np.nan\n",
    "\n",
    "# Check if fixed\n",
    "df_customer.UrbanRural2.value_counts(dropna=False)\n",
    "\n",
    "# Remove that column\n",
    "del df_customer['UrbanRural2']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<B>Or</B>, you could do this harmonization implicitly (and much more conveniently!), by identifying valid category levels, and then implicitly setting other levels (anything not listed) to missing (`Nan`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change non-valid levels to missing \n",
    "df_customer['UrbanRural_cat'] = pd.Categorical(df_customer.UrbanRural,\n",
    "                                               categories=['Urban', 'Rural'])\n",
    "\n",
    "# Verify it is fixed\n",
    "df_customer.UrbanRural_cat.value_counts(dropna=False, sort=False)\n",
    "df_customer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Renaming Categories\n",
    "\n",
    "One more thing we may want to do is rename some of the categories.\n",
    "\n",
    "The following `.cat.rename_categories()` method takes a dictionary as its argument, where we specify the old-to-new mapping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer['UrbanRural_renamed'] = ( df_customer.UrbanRural_cat\n",
    "                                      .cat.rename_categories({'Urban':'City', \n",
    "                                                              'Rural':'Outlying Areas'})\n",
    "                                    )\n",
    "df_customer.loc[:, ['UrbanRural_renamed', 'UrbanRural_cat']]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Missing Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Accounting for missing data is an important part of any analysis. We first need to be able to notice and quantify the missing data in our DataFrame.  We may wish to consider how the data came to be missing.  Finally we'll want to decide what to do with rows or columns that have missing data:  fill the missing data with a appropriate values, or drop the rows or columns entirely.  \n",
    "\n",
    "\n",
    "Missing data mechanics: \n",
    "* Missing data is represented as NaN in a dataframe. \n",
    "* Pandas has dedicated methods for dealing with missing data appropriately. \n",
    "\n",
    "\n",
    "What kinds of things can we do when we have missing data?\n",
    "<ul>\n",
    "  <li>Quantify the missingness. How much is missing, and where?</li>\n",
    "  <li>Flag the missing values so we can hopefully retrieve them by asking the source of our data to send us the missing elements. </li>\n",
    "  <li>Exclude the rows with missing elements from our analysis</li>\n",
    "  <li><I>Impute</I> the missing data, like substituting the mean of a numeric variable in its place, or forward-filling it with the last observed value.</li>\n",
    "</ul>    \n",
    "Concerning that last option, missing data imputation should not be taken lightly as it may distort the character or meaning of the existing data. \n",
    "\n",
    "\n",
    "### Boolean summary functions `any()` and `all()`\n",
    "We will need these functions to summarize the missing values that we see.\n",
    "\n",
    "`any()` will return `True` if <B><I>any</I></B> values in a container (or iterable) can be seen as `True` values.  \n",
    "`all()` will return `True` if <B><I>all</I></B> values in a container (or iterable) can be seen as `True` values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'any([NaN, False, False]): {any([0, 0, 0])}')\n",
    "print()\n",
    "print(f'any([False, False, True]): {any([0, 0, 1])}')\n",
    "print()\n",
    "print(f'all([True, True, NaN]): {all([1, 1, 0])}')\n",
    "print()\n",
    "print(f'all([True, True, True]): {all([1, 1, 1])}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Besides `False` and `0`, also note that `NaN` or `None` values count as `False` as well.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using `sum()` with `True` and `False` values\n",
    "\n",
    "Of course `sum()` will sum up any numbers in its container (or iterable).  But when used with boolean values, `sum()` <I>counts the number of `True` values found</I>.  In other words when used with `sum()`, `True` is counted as `1`, and `False` is counted as `0`.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sum([True, True, False])\n",
    "sum([False, False, False])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quantify Missing Data\n",
    "\n",
    "We will start with detecting missing data. We can detect missing data using the `isnull()` method. You can then apply the `any()`, `all()` and `sum()` methods to summarize \"missingness\" by column or row.  You can also use the `any()` and `all()` functions on series or DataFrames to similar (but sometimes different) effect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .isnull() on a DataFrame returns a matching DataFrame of booleans\n",
    "df_customer.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# are there any False values in the above DataFrame?\n",
    "any(df_customer.isnull())              # True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# which *columns* have missing data?\n",
    "df_customer.isnull().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# which *rows* have missing data?\n",
    "df_customer.isnull().any(axis=1)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# .isnull() on a Series returns a matching Series of booleans\n",
    "df_customer.CustomerID.isnull()\n",
    "\n",
    "# are there any False values in the CustomerID column?\n",
    "any(df_customer.CustomerID.isnull())   # False\n",
    "\n",
    "# same question (and answer) using the .any() method\n",
    "df_customer.CustomerID.isnull().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# How many missing per column?\n",
    "pd.options.display.max_rows = 20\n",
    "\n",
    "df_customer.isnull().sum()\n",
    "\n",
    "pd.options.display.max_rows = 8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# another easy way to get the count of non-nulls\n",
    "df_customer.info()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping Missing Data:  `.dropna()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Missing data can of course distort our view of the data.  We can drop any rows with missing data using `.dropna()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check out the df first - note early rows have missing data\n",
    "df_customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop rows with missing data\n",
    "dfc = df_customer.dropna()\n",
    "dfc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course we may want to scrutinize only certain columns for missing data - if an essential column has a value that must be there, but the others could have missing data, then we can specify a subset of columns to check:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop rows with data missing in only selected columns\n",
    "df_customer.dropna(subset=['CustomerID', 'LastName'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop columns with missing data\n",
    "# maybe less useful...\n",
    "df_customer.dropna(axis=1).head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Replacing Missing Data:  `.fillna()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also have the choice of replacing any missing data, for example to fill missing values with a default value, such as empty string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.MiddleInitial.fillna('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or more commonly, we may wish to replace the missing values with an <I>imputed</I> value, such as the mean of all the values.  (Categorical values will correctly remain missing because there is no mean.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_means = df_customer.mean(numeric_only=True)\n",
    "col_means\n",
    "\n",
    "df_customer_no_na = df_customer.fillna(col_means)\n",
    "df_customer_no_na"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course values like `CustomerID` and `Zipcode` don't have a meaningful mean, so we may wish to be selective:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make a copy to play with\n",
    "df_customer_no_na2 = df_customer.copy()\n",
    "\n",
    "# fill in missing values in CreditScore with mean (rounded to integer)\n",
    "df_customer_no_na2.CreditScore.fillna(round(df_customer.CreditScore.mean()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# how many missing per column?\n",
    "pd.options.display.max_rows = 100\n",
    "\n",
    "df_customer_no_na.isnull().sum()\n",
    "\n",
    "pd.options.display.max_rows = 8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could also impute these values with the last value carried forward, or other options (Check the help for `ffill()` and `bfill()`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Fill with last value carried forward\n",
    "df_customer_no_na = df_customer.ffill()\n",
    "df_customer_no_na"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For explore this topic further, consider the following resources:\n",
    "\n",
    "> [\"Working with Missing Data\"](https://pandas.pydata.org/docs/user_guide/missing_data.html#working-with-missing-data) tutorial from the Pandas User Guide\n",
    "\n",
    "> [\"Handling Missing Data\"](https://jakevdp.github.io/PythonDataScienceHandbook/03.04-missing-values.html) from the Python for Data Science Handbook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dealing with Duplicates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another common task when cleaning up your data is to identify duplicated observations. There are a couple of scenarios we will mention here where duplicates may occur:\n",
    "- You can get duplicated rows from a bad merge earlier in your cleaning process. In this case finding the duplicates clues you in to the bad merge so you can go back and fix it.\n",
    "- You may have received data that has duplicates, in which case you may want to just remove the duplicates.\n",
    "\n",
    "In the simplest case where entire rows have ben duplicated, we can use some basic Pandas methods. We will use the `duplicated()` method to find duplicated rows. By default, the first duplicated row is not considered a duplicate, with any following rows considered duplicates.\n",
    "\n",
    "We also use the Pandas `isin()` method below to find IDs that are in a Series of IDs that we identified as duplicated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# are there any duplicated rows (any True values)?\n",
    "df_customer.duplicated().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# how many duplicated rows?\n",
    "df_customer.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# look at the duplicated rows\n",
    "dup_ids = df_customer.loc[df_customer.duplicated()].CustomerID\n",
    "dup_ids\n",
    "\n",
    "list(dup_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# there were 9 rows with duplicated ids found, so the total of these ids is 18\n",
    "df_customer.CustomerID.isin(dup_ids).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.loc[df_customer.CustomerID.isin(dup_ids), :]             \\\n",
    "                                      .sort_values(['CustomerID'])   \\\n",
    "                                      .head(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.shape\n",
    "df_customer.drop_duplicates(inplace=True)\n",
    "df_customer.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may want to also consider whether just a subset of your columns have been duplicated, potentially hiding more subtle issues with your data. We use the `subset=` argument to check for duplicates across just specific columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lets just check IDs and names\n",
    "id_cols = ['CustomerID','FirstName','LastName']\n",
    "\n",
    "# are there any duplicated rows?\n",
    "df_customer.duplicated(subset=id_cols).any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# how many duplicated rows?\n",
    "df_customer.duplicated(subset=id_cols).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# look at the duplicated rows\n",
    "dup_ids = df_customer.loc[df_customer.duplicated(subset=id_cols), 'CustomerID']\n",
    "\n",
    "df_customer.loc[df_customer.CustomerID.isin(dup_ids), :].sort_values(['CustomerID'])\n",
    "# got one!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Can you see the issue that caused the duplication above? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customer.drop_duplicates(inplace=True, subset=id_cols)\n",
    "\n",
    "# is there the same number of rows as unique IDs now?\n",
    "df_customer.shape\n",
    "df_customer.CustomerID.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the customer data\n",
    "df_customer.to_csv('data/customers_clean_temp.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.14.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {
    "height": "585.994px",
    "width": "429.219px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "229.6px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
