{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas for Exploratory Data Analysis II \n",
    "\n",
    "Pandas a very useful Python library for data manipulation and exploration. We have so much more to see!\n",
    "\n",
    "In this lesson, we'll continue exploring Pandas for EDA. Specifically: \n",
    "\n",
    "- Identify and handle missing values with Pandas.\n",
    "- Implement groupby statements for specific segmented analysis.\n",
    "- Use apply functions to clean data with Pandas.\n",
    "\n",
    "We'll implicitly review many functions from our first Pandas lesson along the way!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remember the AdventureWorks Cycles Dataset?\n",
    "<img align=\"right\" src=\"http://lh6.ggpht.com/_XjcDyZkJqHg/TPaaRcaysbI/AAAAAAAAAFo/b1U3q-qbTjY/AdventureWorks%20Logo%5B5%5D.png?imgmax=800\">\n",
    "\n",
    "Here's the Production.Product table [data dictionary](https://www.sqldatadictionary.com/AdventureWorks2014/Production.Product.html), which is a description of the fields (columns) in the table (the .csv file we will import below):<br>\n",
    "- **ProductID** - Primary key for Product records.\n",
    "- **Name** - Name of the product.\n",
    "- **ProductNumber** - Unique product identification number.\n",
    "- **MakeFlag** - 0 = Product is purchased, 1 = Product is manufactured in-house.\n",
    "- **FinishedGoodsFlag** - 0 = Product is not a salable item. 1 = Product is salable.\n",
    "- **Color** - Product color.\n",
    "- **SafetyStockLevel** - Minimum inventory quantity.\n",
    "- **ReorderPoint** - Inventory level that triggers a purchase order or work order.\n",
    "- **StandardCost** - Standard cost of the product.\n",
    "- **ListPrice** - Selling price.\n",
    "- **Size** - Product size.\n",
    "- **SizeUnitMeasureCode** - Unit of measure for the Size column.\n",
    "- **WeightUnitMeasureCode** - Unit of measure for the Weight column.\n",
    "- **DaysToManufacture** - Number of days required to manufacture the product.\n",
    "- **ProductLine** - R = Road, M = Mountain, T = Touring, S = Standard\n",
    "- **Class** - H = High, M = Medium, L = Low\n",
    "- **Style** - W = Womens, M = Mens, U = Universal\n",
    "- **ProductSubcategoryID** - Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.\n",
    "- **ProductModelID** - Product is a member of this product model. Foreign key to ProductModel.ProductModelID.\n",
    "- **SellStartDate** - Date the product was available for sale.\n",
    "- **SellEndDate** - Date the product was no longer available for sale.\n",
    "- **DiscontinuedDate** - Date the product was discontinued.\n",
    "- **rowguid** - ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.\n",
    "- **ModifiedDate** - Date and time the record was last updated.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np # used for linear algebra and random sampling\n",
    "# used for plotting charts within the notebook (instead of a separate window)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read in the dataset\n",
    "\n",
    "We are using the `read_csv()` method (and the `\\t` separator to specify tab-delimited columns)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# let's check out the first 3 rows again, for old time's sake\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# and the number of rows x cols\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reset our index (like last time)\n",
    "\n",
    "Let's bring our `ProductID` column into the index since it's the PK (primary key) of our table and that's where PKs belong as a best practice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# repalace auto-generated index with the ProductID column\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handling missing data\n",
    "\n",
    "Recall missing data is a systemic, challenging problem for data scientists. Imagine conducting a poll, but some of the data gets lost, or you run out of budget and can't complete it! 😮<br><br>\n",
    "\n",
    "\"Handling missing data\" itself is a broad topic. We'll focus on two components:\n",
    "\n",
    "- Using Pandas to identify we have missing data\n",
    "- Strategies to fill in missing data (known in the business as `imputing`)\n",
    "- Filling in missing data with Pandas\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identifying missing data\n",
    "\n",
    "Before *handling*, we must identify we're missing data at all!\n",
    "\n",
    "We have a few ways to explore missing data, and they are reminiscient of our Boolean filters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# True when data isn't missing\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# True when data is missing\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we may want to see null values in aggregate. We can use `sum()` to sum down a given column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here is a quick and dirty way to do it\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look! We've found missing values!\n",
    "\n",
    "How could this missing data be problematic for our analysis?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understanding missing data\n",
    "\n",
    "Finding missing data is the easy part! Determining way to do next is more complicated.\n",
    "\n",
    "Typically, we are most interested in knowing **why** we are missing data. Once we know what 'type of missingness' we have (the source of missing data), we can proceed effectively."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first quantify how much data we are missing. Here is another implementation of `prod.isnull().sum()`, only wrapped with a `DataFrame` and some labels to make it a little more user-friendly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# or we can make things pretty as follows\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling in missing data\n",
    "\n",
    "How we fill in data depends largely on why it is missing (types of missingness) and what sampling we have available to us.\n",
    "\n",
    "We may:\n",
    "\n",
    "- Delete missing data altogether\n",
    "- Fill in missing data with:\n",
    "    - The average of the column\n",
    "    - The median of the column\n",
    "    - A predicted amount based on other factors\n",
    "- Collect more data:\n",
    "    - Resample the population\n",
    "    - Followup with the authority providing data that is missing\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our case, let's focus on handling missing values in `Color`. Let's get a count of the unique values in that column. We will need to use the `dropna=False` kwarg, otherwise the `pd.Series.value_counts()` method will not count `NaN` (null) values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahoy! We have 248 nulls!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Option 1: Drop the missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drops rows where any row has a missing value - this does not happen *in place*, so we are not actually dropping\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Important!** `pd.DataFrame.dropna()` and `pd.Series.dropna()` are very versatile! Let's look at the docs (Series is similar):\n",
    "\n",
    "```python\n",
    "Signature: pd.DataFrame.dropna(self, axis=0, how='any', thresh=None, subset=None, inplace=False)\n",
    "Docstring:\n",
    "Remove missing values.\n",
    "\n",
    "See the :ref:`User Guide <missing_data>` for more on which values are\n",
    "considered missing, and how to work with missing data.\n",
    "\n",
    "Parameters\n",
    "----------\n",
    "axis : {0 or 'index', 1 or 'columns'}, default 0\n",
    "    Determine if rows or columns which contain missing values are\n",
    "    removed.\n",
    "\n",
    "    * 0, or 'index' : Drop rows which contain missing values.\n",
    "    * 1, or 'columns' : Drop columns which contain missing value.\n",
    "\n",
    "    .. deprecated:: 0.23.0: Pass tuple or list to drop on multiple\n",
    "    axes.\n",
    "how : {'any', 'all'}, default 'any'\n",
    "    Determine if row or column is removed from DataFrame, when we have\n",
    "    at least one NA or all NA.\n",
    "\n",
    "    * 'any' : If any NA values are present, drop that row or column.\n",
    "    * 'all' : If all values are NA, drop that row or column.\n",
    "thresh : int, optional\n",
    "    Require that many non-NA values.\n",
    "subset : array-like, optional\n",
    "    Labels along other axis to consider, e.g. if you are dropping rows\n",
    "    these would be a list of columns to include.\n",
    "inplace : bool, default False\n",
    "    If True, do operation inplace and return None.\n",
    "```\n",
    "\n",
    "**how**: This tells us if we want to remove a row if _any_ of the columns have a null, or _all_ of the columns have a null.<br>\n",
    "**subset**: We can input an array here, like `['Color', 'Size', 'Weight']`, and it will only consider nulls in those columns. This is very useful!<br>\n",
    "**inplace**: This is if you want to mutate (change) the source dataframe. Default is `False`, so it will return a _copy_ of the source dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To accomplish the same thing, but implement it on our entire dataframe, we can do the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drops all nulls from the Color column, but returns the entire dataframe instead of just the Color column\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Option 2: Fill in missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Traditionally, we fill missing data with a median, average, or mode (most frequently occurring). For `Color`, let's replace the nulls with the string value `NoColor`.\n",
    "\n",
    "Let's first look at the way we'd do it with a single column, using the `pd.Series.fillna()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's see how we'd do it to the whole dataframe, using the `pd.DataFrame.fillna()` method. Notice the similar API between the methods with the `value` kwarg. Good congruent design, pandas development team! The full dataframe is returned, and not just a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But wait! There's more! We can reference any other data or formulas we want with the imputation (the value we fill the nulls with). This is very handy if you want to impute with the average or median of that column... or even another column altogether! Here is an example where we will the nulls of `Color` with the average value from the `ListPrice` column. This has no practical value in this application, but immense value in other applications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "They're gone! Important points:\n",
    "\n",
    "- Don't forget to use the `inplace=True` kwarg to mutate the source dataframe (i.e. 'save changes'). \n",
    "- It is helpful to not use `inplace=True` initially to ensure your code/logic is correct, prior to making permanent changes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Groupby Statements\n",
    "\n",
    "In Pandas, groupby statements are similar to pivot tables in that they allow us to segment our population to a specific subset.\n",
    "\n",
    "For example, if we want to know the average number of bottles sold and pack sizes per city, a groupby statement would make this task much more straightforward.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To think how a groupby statement works, think about it like this:\n",
    "\n",
    "- **Split:** Separate our DataFrame by a specific attribute, for example, group by `Color`\n",
    "- **Combine:** Put our DataFrame back together and return some _aggregated_ metric, such as the `sum`, `count`, or `max`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![](http://i.imgur.com/yjNkiwL.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try it out!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's group by `Color`, and get a count of products for each color."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group by Color, giving the number of products of each color\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What do we notice about this output? Are all columns the same? Why or why not?\n",
    "\n",
    "We can see that the `.count()` method excludes nulls, and there is no way to change this with the current implementation:\n",
    "```python\n",
    "Signature: .count()\n",
    "Docstring: Compute count of group, excluding missing values \n",
    "File:      ~/miniconda3/envs/ga/lib/python3.7/site-packages/pandas/core/groupby/groupby.py\n",
    "Type:      method\n",
    "```\n",
    "\n",
    "As a best practice, you should either:\n",
    "- fill in nulls prior to your .count(), or\n",
    "- use the PK (primary key) of the table, which is guaranteed non-null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# here we can use 'x' as a dummy placeholder for nulls, simply to get consistent counts for all columns\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find out the most expensive price for an item, by `Color`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also do multi-level groupbys. This is referred to as a `Multiindex` dataframe. Here, we can see the following fields in a nested group by, with a count of Name (with nulls filled!); effectively giving us a count of the number of products for every unique Class/Style combination:\n",
    "\n",
    "- Class - H = High, M = Medium, L = Low\n",
    "- Style - W = Womens, M = Mens, U = Universal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use the `.agg()` method with multiple arguments, to simulate a `.describe()` method like we used before:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Apply functions for column operations\n",
    "\n",
    "Apply functions allow us to perform a complex operation across an entire columns highly efficiently."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example, let's say we want to change our colors from a word, to just a single letter. How would we do that?\n",
    "\n",
    "The first step is writing a function, with the argument being the value we would receive from each cell in the column. This function will mutate the input, and return the result. This result will then be _applied_ to the source dataframe (if desired)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def color_to_letter(color):\n",
    "    # this maps the original color to the color we want it to be\n",
    "    color_dict = {\n",
    "        'Black': 'B',\n",
    "        'Silver': 'S',\n",
    "        'Red': 'R',\n",
    "        'White': 'W',\n",
    "        'Blue': 'B',\n",
    "        'Multi': 'M',\n",
    "        'Yellow': 'Y',\n",
    "        'Grey': 'G',\n",
    "        'Silver/Black': 'V'\n",
    "    }\n",
    "    try:\n",
    "        return color_dict[color]\n",
    "    # this catches nulls, or any other color we haven't\n",
    "    # defined in our color_dict, and fills it with 'N'\n",
    "    except KeyError:\n",
    "        return 'N'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can _apply_ this function to our `pd.Series` object, returning the result (which we can use to overwrite the source, if we choose)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `pd.DataFrame.apply` implementation is similar, however it effectively 'scrolls through' the columns and passes each one sequentially to your function:\n",
    "\n",
    "```python\n",
    "Objects passed to the function are Series objects whose index is\n",
    "either the DataFrame's index (``axis=0``) or the DataFrame's columns\n",
    "(``axis=1``).\n",
    "```\n",
    "\n",
    "It should only be used when you wish to apply the same function to all columns (or rows) of your `pd.DataFrame` object."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use `pd.Series.apply()` with a **labmda expression**. This is an undeclared function and is commonly used for simple functions within the `.apply()` method. Let's use it to add $100 to our `ListPrice` column. Hey, baby needs new shoes!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# without apply\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# and now with 100 more dollars!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Boom! Maybe financing that new boat wasn't such a bad idea after all!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Your turn:** Identify one other column where we may want to write a new apply function, or use the one we just created for the purposes of cleaning up our dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# identify a column to mutate (change)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write a function to mutate that column (or columns) note: if using a lambda function, you can leave this blank\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# apply that function across the whole column\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Wrap up\n",
    "\n",
    "We've covered even more useful information! Here are the key takeaways:\n",
    "\n",
    "- **Missing data** comes in many shapes and sizes. Before deciding how to handle it, we identify it exists. We then derive how the missingness is affecting our dataset, and make a determination about how to fill in values.\n",
    "\n",
    "```python\n",
    "# pro tip for identifying missing data\n",
    "df.isnull().sum()\n",
    "```\n",
    "\n",
    "- **Grouby** statements are particularly useful for a subsection-of-interest analysis. Specifically, zooming in on one condition, and determining relevant statstics.\n",
    "\n",
    "```python\n",
    "# group by \n",
    "df.groupby('column').agg['count', 'mean', 'max', 'min']\n",
    "```\n",
    "\n",
    "- **Apply functions** help us clean values across an entire DataFrame column. They are *like* a for loop for cleaning, but many times more efficient. They follow a common pattern:\n",
    "1. Write a function that works on a single value\n",
    "2. Test that function on a single value\n",
    "3. Apply that function to a whole column\n",
    "\n",
    "(The most confusing part of apply functions is that we write them with *a single value* in mind, and then apply them to many single values at once.)"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
