Course Materials/4-Cleaning_and_Manipulating-Reference.ipynb

{
  "cells": [
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "# Manipulating and Cleaning Data\n\nThis section marks a subtle change. Up until now, we have been introducing ideas and techniques in order to prepare you with a toolbox of techniques to deal with real-world situations. We are now going to start using some of those tools while also giving you some ideas about how and when to use them in your own work with data.\n\nReal-world data is messy. You will likely need to combine several data sources to get the data you actually want. The data from those sources will be incomplete. And it will likely not be formatted in exactly the way you want in order to perform your analysis. It's for these reasons that most data scientists will tell you that about 80 percent of any project is spent just getting the data into a form ready for analysis.\n\n## Exploring `DataFrame` information\n\n> **Learning goal:** By the end of this subsection, you should be comfortable finding general information about the data stored in pandas DataFrames.\n\nOnce you have loaded your data into pandas, it will more likely than not be in a `DataFrame`. However, if the data set in your `DataFrame` has 60,000 rows and 400 columns, how do you even begin to get a sense of what you're working with? Fortunately, pandas provides some conventient tools to quickly look at overall information about a `DataFrame` in addition to the first few and last few rows.\n\nIn order to explore this functionality, we will import the Python scikit-learn library and use an iconic dataset that every data scientist has seen hundreds of times: British biologist Ronald Fisher's *Iris* data set used in his 1936 paper \"The use of multiple measurements in taxonomic problems\":"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "import pandas as pd\nfrom sklearn.datasets import load_iris\n\niris = load_iris()\niris_df = pd.DataFrame(data=iris['data'], columns=iris['feature_names'])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### `DataFrame.info`\nLet's take a look at this dataset to see what we have:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "iris_df.info()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "From this, we know that the *Iris* dataset has 150 entries in four columns. All of the data is stored as 64-bit floating-point numbers."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### `DataFrame.head`\nNext, let's see what the first few rows of our `DataFrame` look like:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "iris_df.head()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:\n\nBy default, `DataFrame.head` returns the first five rows of a `DataFrame`. In the code cell below, can you figure out how to get it to show more?"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Hint: Consult the documentation by using iris_df.head?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### `DataFrame.tail`\nThe flipside of `DataFrame.head` is `DataFrame.tail`, which returns the last five rows of a `DataFrame`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "iris_df.tail()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "In practice, it is useful to be able to easily examine the first few rows or the last few rows of a `DataFrame`, particularly when you are looking for outliers in ordered datasets.\n\n> **Takeaway:** Even just by looking at the metadata about the information in a DataFrame or the first and last few values in one, you can get an immediate idea about the size, shape, and content of the data you are dealing with."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## Dealing with missing data\n\n> **Learning goal:** By the end of this subsection, you should know how to replace or remove null values from DataFrames.\n\nMost of the time the datasets you want to use (of have to use) have missing values in them. How missing data is handled carries with it subtle tradeoffs that can affect your final analysis and real-world outcomes.\n\nPandas handles missing values in two ways. The first you've seen before in previous sections: `NaN`, or Not a Number. This is a actually a special value that is part of the IEEE floating-point specification and it is only used to indicate missing floating-point values.\n\nFor missing values apart from floats, pandas uses the Python `None` object. While it might seem confusing that you will encounter two different kinds of values that say essentially the same thing, there are sound programmatic reasons for this design choice and, in practice, going this route enables pandas to deliver a good compromise for the vast majority of cases. Notwithstanding this, both `None` and `NaN` carry restrictions that you need to be mindful of with regards to how they can be used."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### `None`: non-float missing data\nBecause `None` comes from Python, it cannot be used in NumPy and pandas arrays that are not of data type `'object'`. Remember, NumPy arrays (and the data structures in pandas) can contain only one type of data. This is what gives them their tremendous power for large-scale data and computational work, but it also limits their flexibility. Such arrays have to upcast to the “lowest common denominator,” the data type that will encompass everything in the array. When `None` is in the array, it means you are working with Python objects.\n\nTo see this in action, consider the following example array (note the `dtype` for it):"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "import numpy as np\n\nexample1 = np.array([2, None, 6, 8])\nexample1",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The reality of upcast data types carries two side effects with it. First, operations will be carried out at the level of interpreted Python code rather than compiled NumPy code. Essentially, this means that any operations involving `Series` or `DataFrames` with `None` in them will be slower. While you would probably not notice this performance hit, for large datasets it might become an issue.\n\nThe second side effect stems from the first. Because `None` essentially drags `Series` or `DataFrame`s back into the world of vanilla Python, using NumPy/pandas aggregations like `sum()` or `min()` on arrays that contain a ``None`` value will generally produce an error:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example1.sum()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "**Key takeaway**: Addition (and other operations) between integers and `None` values is undefined, which can limit what you can do with datasets that contain them."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### `NaN`: missing float values\n\nIn contrast to `None`, NumPy (and therefore pandas) supports `NaN` for its fast, vectorized operations and ufuncs. The bad news is that any arithmetic performed on `NaN` always results in `NaN`. For example:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "np.nan + 1",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "np.nan * 0",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The good news: aggregations run on arrays with `NaN` in them don't pop errors. The bad news: the results are not uniformly useful:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example2 = np.array([2, np.nan, 6, 8]) \nexample2.sum(), example2.min(), example2.max()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# What happens if you add np.nan and None together?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Remember: `NaN` is just for missing floating-point values; there is no `NaN` equivalent for integers, strings, or Booleans."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### `NaN` and `None`: null values in pandas\n\nEven though `NaN` and `None` can behave somewhat differently, pandas is nevertheless built to handle them interchangeably. To see what we mean, consider a `Series` of integers:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "int_series = pd.Series([1, 2, 3], dtype=int)\nint_series",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Now set an element of int_series equal to None.\n# How does that element show up in the Series?\n# What is the dtype of the Series?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "In the process of upcasting data types to establish data homogeneity in `Seires` and `DataFrame`s, pandas will willingly switch missing values between `None` and `NaN`. Because of this design feature, it can be helpful to think of `None` and `NaN` as two different flavors of \"null\" in pandas. Indeed, some of the core methods you will use to deal with missing values in pandas reflect this idea in their names:\n\n- `isnull()`: Generates a Boolean mask indicating missing values\n- `notnull()`: Opposite of `isnull()`\n- `dropna()`: Returns a filtered version of the data\n- `fillna()`: Returns a copy of the data with missing values filled or imputed\n\nThese are important methods to master and get comfortable with, so let's go over them each in some depth."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Detecting null values\nBoth `isnull()` and `notnull()` are your primary methods for detecting null data. Both return Boolean masks over your data."
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "example3 = pd.Series([0, np.nan, '', None])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example3.isnull()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Look closely at the output. Does any of it surprise you? While `0` is an arithmetic null, it's nevertheless a perfectly good integer and pandas treats it as such. `''` is a little more subtle. While we used it in Section 1 to represent an empty string value, it is nevertheless a string object and not a representation of null as far as pandas is concerned.\n\nNow, let's turn this around and use these methods in a manner more like you will use them in practice. You can use Boolean masks  directly as a ``Series`` or ``DataFrame`` index, which can be useful when trying to work with isolated missing (or present) values."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Try running example3[example3.notnull()].\n# Before you do so, what do you expect to see?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "**Key takeaway**: Both the `isnull()` and `notnull()` methods produce similar results when you use them in `DataFrame`s: they show the results and the index of those results, which will help you enormously as you wrestle with your data."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Dropping null values\n\nBeyond identifying missing values, pandas provides a convenient means to remove null values from `Series` and `DataFrame`s. (Particularly on large data sets, it is often more advisable to simply remove missing [NA] values from your analysis than deal with them in other ways.) To see this in action, let's return to `example3`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example3 = example3.dropna()\nexample3",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Note that this should look like your output from `example3[example3.notnull()]`. The difference here is that, rather than just indexing on the masked values, `dropna` has removed those missing values from the `Series` `example3`.\n\nBecause `DataFrame`s have two dimensions, they afford more options for dropping data."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4 = pd.DataFrame([[1,      np.nan, 7], \n                         [2,      5,      8], \n                         [np.nan, 6,      9]])\nexample4",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "(Did you notice that pandas upcast two of the columns to floats to accommodate the `NaN`s?)\n\nYou cannot drop a single value from a `DataFrame`, so you have to drop full rows or columns. Depending on what you are doing, you might want to do one or the other, and so pandas gives you options for both. Because in data science, columns generally represent variables and rows represent observations, you are more likely to drop rows of data; the default setting for `dropna()` is to drop all rows that contain any null values:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4.dropna()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "If necessary, you can drop NA values from columns. Use `axis=1` to do so:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4.dropna(axis='columns')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice that this can drop a lot of data that you might want to keep, particularly in smaller datasets. What if you just want to drop rows or columns that contain several or even just all null values? You specify those setting in `dropna` with the `how` and `thresh` parameters.\n\nBy default, `how='any'` (if you would like to check for yourself or see what other parameters the method has, run `example4.dropna?` in a code cell). You could alternatively specify `how='all'` so as to drop only rows or columns that contain all null values. Let's expand our example `DataFrame` to see this in action."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4[3] = np.nan\nexample4",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# How might you go about dropping just column 3?\n# Hint: remember that you will need to supply both the axis parameter and the how parameter.\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The `thresh` parameter gives you finer-grained control: you set the number of *non-null* values that a row or column needs to have in order to be kept:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4.dropna(axis='rows', thresh=3)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Here, the first and last row have been dropped, because they contain only two non-null values."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Filling null values\n\nDepending on your dataset, it can sometimes make more sense to fill null values with valid ones rather than drop them. You could use `isnull` to do this in place, but that can be laborious, particularly if you have a lot of values to fill. Because this is such a common task in data science, pandas provides `fillna`, which returns a copy of the `Series` or `DataFrame` with the missing values replaced with one of your choosing. Let's create another example `Series` to see how this works in practice."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example5 = pd.Series([1, np.nan, 2, None, 3], index=list('abcde'))\nexample5",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can fill all of the null entries with a single value, such as `0`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example5.fillna(0)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# What happens if you try to fill null values with a string, like ''?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can **forward-fill** null values, which is to use the last valid value to fill a null:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example5.fillna(method='ffill')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can also **back-fill** to propagate the next valid value backward to fill a null:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example5.fillna(method='bfill')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "collapsed": true
      },
      "cell_type": "markdown",
      "source": "As you might guess, this works the same with `DataFrame`s, but you can also specify an `axis` along which to fill null values:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4.fillna(method='ffill', axis=1)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice that when a previous value is not available for forward-filling, the null value remains."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# What output does example4.fillna(method='bfill', axis=1) produce?\n# What about example4.fillna(method='ffill') or example4.fillna(method='bfill')?\n# Can you think of a longer code snippet to write that can fill all of the null values in example4?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can be creative about how you use `fillna`. For example, let's look at `example4` again, but this time let's fill the missing values with the average of all of the values in the `DataFrame`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example4.fillna(example4.mean())",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice that column 3 is still valueless: the default direction is to fill values row-wise.\n\n> **Takeaway:** There are multiple ways to deal with missing values in your datasets. The specific strategy you use (removing them, replacing them, or even how you replace them) should be dictated by the particulars of that data. You will develop a better sense of how to deal with missing values the more you handle and interact with datasets."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## Removing duplicate data\n\n> **Learning goal:** By the end of this subsection, you should be comfortable identifying and removing duplicate values from DataFrames.\n\nIn addition to missing data, you will often encounter duplicated data in real-world datasets. Fortunately, pandas provides an easy means of detecting and removing duplicate entries."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Identifying duplicates: `duplicated`\n\nYou can easily spot duplicate values using the `duplicated` method in pandas, which returns a Boolean mask indicating whether an entry in a `DataFrame` is a duplicate of an ealier one. Let's create another example `DataFrame` to see this in action."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example6 = pd.DataFrame({'letters': ['A','B'] * 2 + ['B'],\n                         'numbers': [1, 2, 1, 3, 3]})\nexample6",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example6.duplicated()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Dropping duplicates: `drop_duplicates`\n`drop_duplicates` simply returns a copy of the data for which all of the `duplicated` values are `False`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example6.drop_duplicates()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Both `duplicated` and `drop_duplicates` default to consider all columnsm but you can specify that they examine only a subset of columns in your `DataFrame`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "example6.drop_duplicates(['letters'])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "> **Takeaway:** Removing duplicate data is an essential part of almost every data-science project. Duplicate data can change the results of your analyses and give you spurious results!"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## Combining datasets: merge and join\n\n> **Learning goal:** By the end of this subsection, you should have a general knowledge of the various ways to combine `DataFrame`s.\n\nYour most interesting analyses will often come from data melded together from more than one source. Because of this, pandas provides several methods of merging and joining datasets to make this necessary job easier:\n - **`pandas.merge`** connects rows in `DataFrame`s based on one or more keys.\n - **`pandas.concat`** concatenates or “stacks” together objects along an axis.\n - The **`combine_first`** instance method enables you to splice together overlapping data to fill in missing values in one object with values from another.\n\nLet's examine merging data first, because it will be the most familiar to course attendees who are already familiar with SQL or other relational databases."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Categories of joins\n\n`merge` carries out several types of joins: *one-to-one*, *many-to-one*, and *many-to-many*. You use the same basic function call to implement all of them and we will examine all three (because you will need all three as some point in your data delving depending on the data). We will start with one-to-one joins because they are generally the simplest example."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### One-to-one joins\n\nConsider combining two `DataFrame`s that contain different information on the same employees in a company:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df1 = pd.DataFrame({'employee': ['Gary', 'Stu', 'Mary', 'Sue'],\n                    'group': ['Accounting', 'Marketing', 'Marketing', 'HR']})\ndf1",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df2 = pd.DataFrame({'employee': ['Mary', 'Stu', 'Gary', 'Sue'],\n                    'hire_date': [2008, 2012, 2017, 2018]})\ndf2",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Combine this information into a single `DataFrame` using the `merge` function:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df3 = pd.merge(df1, df2)\ndf3",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Pandas joined on the `employee` column because it was the only column common to both `df1` and `df2`. (Note also that the original indices of `df1` and `df2` were discarded by `merge`; this is generally the case with merges unless you conduct them by index, which we will dicuss later on.)"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### Many-to-one joins\n\nA many-to-one join is like a one-to-one join except that one of the two key columns contains duplicate entries. The `DataFrame` resulting from such a join will preserve those duplicate entries as appropriate:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df4 = pd.DataFrame({'group': ['Accounting', 'Marketing', 'HR'],\n                    'supervisor': ['Carlos', 'Giada', 'Stephanie']})\ndf4",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df3, df4)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The resulting `DataFrame` has an additional column for `supervisor`; that column has an extra occurence of 'Giada' that did not occur in `df4` because more than one employee in the merged `DataFrame` works in the 'Marketing' group.\n\nNote that we didn’t specify which column to join on. When you don't specify that information, `merge` uses the overlapping column names as the keys. However, that can be ambiguous; several columns might meet that condition. For that reason, it is a good practice to explicitly specify on which key to join. You can do this with the `on` parameter:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df3, df4, on='group')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### Many-to-many joins\nWhat happens if the key columns in both of the `DataFrame`s you are joining contain duplicates? That gives you a many-to-many join:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',\n                              'Marketing', 'Marketing', 'HR', 'HR'],\n                    'core_skills': ['math', 'spreadsheets', 'writing', 'communication',\n                               'spreadsheets', 'organization']})\ndf5",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1, df5, on='group')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Again, in order to avoid ambiguity as to which column to join on, it is a good idea to explicitly tell `merge` which one to use with the `on` parameter."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### `left_on` and `right_on` keywords\nWhat if you need to merge two datasets with no shared column names? For example, what if you are using a dataset in which the employee name is labeled as 'name' rather than 'employee'? In such cases, you will need to use the `left_on` and `right_on` keywords in order to specify the column names on which to join:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df6 = pd.DataFrame({'name': ['Gary', 'Stu', 'Mary', 'Sue'],\n                    'salary': [70000, 80000, 120000, 90000]})\ndf6",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1, df6, left_on=\"employee\", right_on=\"name\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Using the documentation, can you figure out how to use .drop() to get rid of the 'name' column?\n# Hint: You will need to supply two parameters to .drop()\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### `left_index` and `right_index` keywords\n\nSometimes it can be more advantageous to merge on an index rather than on a column. The `left_index` and `right_index` keywords make it possible to join by index. Let's revisit some of our earlier example `DataFrame`s to see what this looks like in action."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df1a = df1.set_index('employee')\ndf1a",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df2a = df2.set_index('employee')\ndf2a",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "To merge on the index, specify the `left_index` and `right_index` parameters in `merge`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1a, df2a, left_index=True, right_index=True)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# What happens if you specify only left_index or right_index?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can also use the `join` method for `DataFrame`s, which produces the same effect but merges on indices by default:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df1a.join(df2a)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can also mix and match `left_index`/`right_index` with `right_on`/`left_on`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1a, df6, left_index=True, right_on='name')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### Set arithmetic for joins\n\nLet's return to many-to-many joins for a moment. A consideration that is unique to them is the *arithmetic* of the join, specifically the set arithmetic we use for the join. To illustrate what we mean by this, let's restructure an old example `DataFrame`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df5 = pd.DataFrame({'group': ['Engineering', 'Marketing', 'Sales'],\n                    'core_skills': ['math', 'writing', 'communication']})\ndf5",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1, df5, on='group')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice that after we have restructured `df5` and then re-run the merge with `df1`, we have only two entries in the result. This is because we merged on `group` and 'Marketing' was the only entry that appeared in the `group` column of both `DataFrame`s.\n\nIn effect, what we have gotten is the *intersection* of both `DataFrame`s. This is know as the inner join in the database world and it is the default setting for `merge` although we can certainly specify it:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1, df5, on='group', how='inner')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The complement of the inner join is the outer join, which returns the *union* of the two `DataFrame`s."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# The keyword for perfoming an outer join is how='outer'. How would you perform it?\n# What do you expect the output of an outer join of df1 and df5 to be?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice in your resulting `DataFrame` that not every row in `df1` and `df5` had a value that corresponds to the union of the key values (the 'group' column). Pandas fills in these missing values with `NaN`s."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Inner and outer joins are not your only options. A *left join* returns all of the rows in the first (left-side) `DataFrame` supplied to `merge` along with rows from the other `DataFrame` that match up with the left-side key values (and `NaNs` rows with respective values):"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df1, df5, how='left')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Now run the right merge between df1 and df5.\n# What do you expect to see?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### `suffixes` keyword: dealing with conflicting column names\nBecause you can join datasets, you will eventually join two with conflicting column names. Let's look at another example to see what we mean:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df7 = pd.DataFrame({'name': ['Gary', 'Stu', 'Mary', 'Sue'],\n                    'rank': [1, 2, 3, 4]})\ndf7",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df8 = pd.DataFrame({'name': ['Gary', 'Stu', 'Mary', 'Sue'],\n                    'rank': [3, 1, 4, 2]})\ndf8",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df7, df8, on='name')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Each column name in a `DataFrame` must be unique, so in cases where two joined `DataFrame`s share column names (aside from the column serving as the key), the `merge` function automatically appends the suffix `_x` or `_y` to the conflicting column names in order to make them unique. In cases where it is best to control your column names, you can specify a custom suffix for `merge` to append through the `suffixes` keyword:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.merge(df7, df8, on='name', suffixes=['_left', '_right'])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Note that these suffixes work if there are multiple conflicting columns."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Concatenation in NumPy\nConcatenation in pandas is built off of the concatenation functionality for NumPy arrays. Here is what NumPy concatenation looks like:\n - For one-dimensional arrays:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "x = [1, 2, 3]\ny = [4, 5, 6]\nz = [7, 8, 9]\nnp.concatenate([x, y, z])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": " - For two-dimensional arrays:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "x = [[1, 2],\n     [3, 4]]\nnp.concatenate([x, x], axis=1)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice that the `axis=1` parameter makes the concatenation occur along columns rather than rows. Concatenation in pandas looks similar to this."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Concatenation in pandas\n\nPandas has a function, `pd.concat()` that can be used for a simple concatenation of `Series` or `DataFrame` objects in similar manner to `np.concatenate()` with ndarrays."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "ser1 = pd.Series(['a', 'b', 'c'], index=[1, 2, 3])\nser2 = pd.Series(['d', 'e', 'f'], index=[4, 5, 6])\npd.concat([ser1, ser2])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "It also concatenates higher-dimensional objects, such as ``DataFrame``s:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df9 = pd.DataFrame({'A': ['a', 'c'],\n                    'B': ['b', 'd']})\ndf9",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.concat([df9, df9])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Notice that `pd.concat` has preserved the indexing even though that means that it has been duplicated. You can have the results re-indexed (and avoid potential confusion down the road) like so:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.concat([df9, df9], ignore_index=True)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "By default, `pd.concat` concatenates row-wise within the `DataFrame` (that is, `axis=0` by default). You can specify the axis along which to concatenate:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.concat([df9, df9], axis=1)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Note that while pandas will display this without error, you will get an error message if you try to assign this result as a new `DataFrame`. Column names in `DataFrame`s must be unique."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Concatenation with joins\nJust as you did with merge above, you can use inner and outer joins when concatenating `DataFrame`s with different sets of column names."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df10 = pd.DataFrame({'A': ['a', 'd'],\n                     'B': ['b', 'e'],\n                     'C': ['c', 'f']})\ndf10",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df11 = pd.DataFrame({'B': ['u', 'x'],\n                     'C': ['v', 'y'],\n                     'D': ['w', 'z']})\ndf11",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.concat([df10, df11])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "As we saw earlier, the default join for this is an outer join and entries for which no data is available are filled with `NaN` values. You can also do an inner join:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.concat([df10, df11], join='inner')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Another option is to directly specify the index of the remaininig colums using the `join_axes` argument, which takes a list of index objects. Here, we will specify that the returned columns should be the same as those of the first input (`df10`):"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "pd.concat([df10, df11], join_axes=[df10.columns])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### `append()`\n\nBecause direct array concatenation is so common, ``Series`` and ``DataFrame`` objects have an ``append`` method that can accomplish the same thing in fewer keystrokes. For example, rather than calling ``pd.concat([df9, df9])``, you can simply call ``df9.append(df9)``:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df9.append(df9)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "**Important point**: Unlike the `append()` and `extend()` methods of Python lists, the `append()` method in pandas does not modify the original object. It instead creates a new object with the combined data.\n\n> **Takeaway:** A large part of the value you can provide as a data scientist comes from connecting multiple, often disparate datasets to find new insights. Learning how to join and merge data is thus an essential part of your skill set."
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## Exploratory statistics and visualization\n\n> **Learning goal:** By the end of this subsection, you should be familiar with some of the ways to visually explore the data stored in `DataFrame`s.\n\nOften when probing a new data set, it is invaluable to get high-level information about what the dataset holds. Earlier in this section we discussed using methods such as `DataFrame.info`, `DataFrame.head`, and `DataFrame.tail` to examine some aspects of a `DataFrame`. While these methods are critical, they are on their own often insufficient to get enough information to know how to approach a new dataset. This is where exploratory statistics and visualizations for datasets come in.\n\nTo see what we mean in terms of gaining exploratory insight (both visually and numerically), let's dig into one of the the datasets that come with the scikit-learn library, the Boston Housing Dataset (though you will load it from a CSV file):"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df = pd.read_csv('../Data/housing_dataset.csv')\ndf.head()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "This dataset contains information collected from the U.S Census Bureau concerning housing in the area of Boston, Massachusetts and was first published in 1978. The dataset has 14 columns:\n - **CRIM**:     Per-capita crime rate by town\n - **ZN**:       Proportion of residential land zoned for lots over 25,000 square feet\n - **INDUS**:    Proportion of non-retail business acres per town\n - **CHAS**:     Charles River dummy variable (= 1 if tract bounds river; 0 otherwise)\n - **NOX**:      Nitric oxides concentration (parts per 10 million)\n - **RM**:       Average number of rooms per dwelling\n - **AGE**:      Proportion of owner-occupied units built prior to 1940\n - **DIS**:      Weighted distances to five Boston employment centres\n - **RAD**:      Index of accessibility to radial highways\n - **TAX**:      Full-value property-tax rate per \\$10,000\n - **PTRATIO**:  Pupil-teacher ratio by town\n - **LSTAT**:    Percent of lower-status portion of the population\n - **MEDV**:     Median value of owner-occupied homes in \\$1,000s"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "One of the first methods we can use to better understand this dataset is `DataFrame.shape`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df.shape",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The dataset has 506 rows and 13 columns.\n\nTo get a better idea of the contents of each column we can use `DataFrame.describe`, which returns the maximum value, minimums value, mean, and standard deviation of numeric values in each columns, in addition to the quartiles for each column:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df.describe()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Because dataset can have so many columns in them, it can often be useful to transpose the results of `DataFrame.describe` to better use them:"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Note that you can also examine specific descriptive statistics for columns without having to invoke `DataFrame.describe`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['MEDV'].mean()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['MEDV'].max()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['AGE'].median()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Now find the maximum value in df['AGE'].\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Other information that you will often want to see is the relationship between different columns. You do this with the `DataFrame.groupby` method. For example, you could examine the average MEDV (median value of owner-occupied homes) for each value of AGE (proportion of owner-occupied units built prior to 1940):"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df.groupby(['AGE'])['MEDV'].mean()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# Now try to find the median value for AGE for each value of MEDV.\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can also apply a lambda function to each element of a `DataFrame` column by using the `apply` method. For example, say you wanted to create a new column that flagged a row if more than 50 percent of owner-occupied homes were build before 1940:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['AGE_50'] = df['AGE'].apply(lambda x: x>50)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Once applied, you also see how many values returned true and how many false by using the `value_counts` method:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['AGE_50'].value_counts()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can also examine figures from the groupby statement you created earlier:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df.groupby(['AGE_50'])['MEDV'].mean()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can also group by more than one variable, such AGE_50 (the one you just created), CHAS (whether a town is on the Charles River), and RAD (an index measuring access to the Boston-area radial highways), and then evaluate each group for the average median home price in that group:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "groupby_twovar=df.groupby(['AGE_50','RAD','CHAS'])['MEDV'].mean()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can then see what values are in this stacked group of variables:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "groupby_twovar",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Let's take a moment to analyze these results in a little depth. The first row reports that communities with less the half of houses built before 1940, with a highway-access index of 1, and that are not situated on the Charles River have a mean house price of \\$24,667 (1970s dollars); the next row shows that for communities similar to the first row except for being located on the Charles River have a mean house price of \\$50,000.\n\nOne insight that pops out from continuing down this is that, all else being equal, being located next to the Charles River can significantly increase the value of newer housing stock. The story is more ambiguous for communities dominated by older houses: proximity to the Charles significantly increases home prices in one community (and that one presumably farther away from the city); for all others, being situated on the river either provided a modest increase in value or actually decreased mean home prices.\n\nWhile groupings like this can be a great way to begin to interrogate your data, you might not care for the 'tall' format it comes in. In that case, you can unstack the data into a \"wide\" format:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "groupby_twovar.unstack()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "### Exercise:"
    },
    {
      "metadata": {
        "collapsed": true,
        "trusted": false
      },
      "cell_type": "code",
      "source": "# How could you use groupby to get a sense of the proportion \n# of residential land zoned for lots over 25,000 sq.ft., \n# the proportion of non-retail business acres per town, \n# and the distance of towns from employment centers in Boston?\n",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "collapsed": true
      },
      "cell_type": "markdown",
      "source": "It is also often valuable to know how many unique values a column has in it with the `nunique` method:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['CHAS'].nunique()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Complementary to that, you will also likely want to know what those unique values are, which is where the `unique` method helps:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['CHAS'].unique()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "You can use the `value_counts` method to see how many of each unique value there are in a column:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df['CHAS'].value_counts()",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Or you can easily plot a bar graph to visually see the breakdown:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "%matplotlib inline\ndf['CHAS'].value_counts().plot(kind='bar')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Note that the IPython magic command `%matplotlib inline` enables you to view the chart inline.\n\nLet's pull back to the dataset as a whole for a moment. Two major things that you will look for in almost any dataset are trends and relationships. A typical relationship between variables to explore is the Pearson correlation, or the extent to which two variables are linearly related. The `corr` method will show this in table format for all of the columns in a `DataFrame`:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "df.corr(method='pearson')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Suppose you just wanted to look at the correlations between all of the columns and just one variable? Let's examine just the correlation between all other variables and the percentage of owner-occupied houses build before 1940 (AGE). We will do this by accessing the column by index number:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "corr = df.corr(method='pearson')\ncorr_with_homevalue = corr.iloc[-1]\ncorr_with_homevalue[corr_with_homevalue.argsort()[::-1]]",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "With the correlations arranged in descending order, it's easy to start to see some patterns. Correlating AGE with a variable we created from AGE is a trivial correlation. However, it is interesting to note that the percentage of older housing stock in communities strongly correlates with air pollution (NOX) and the proportion of non-retail business acres per town (INDUS); at least in 1978 metro Boston, older towns are more industrial.\n\nGraphically, we can see the correlations using a heatmap from the Seaborn library:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "import seaborn as sns\nsns.heatmap(df.corr(),cmap=sns.cubehelix_palette(20, light=0.95, dark=0.15))",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Histograms are another valuable tool for investigating your data. For example, what is the overall distribution of prices of owner-occupied houses in the Boston area?"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "import matplotlib.pyplot as plt\nplt.hist(df['MEDV'])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The default bin size for the matplotlib histogram (essentially big of buckets of percentages that you include in each histogram bar in this case) is pretty large and might mask smaller details. To get a finer-grained view of the AGE column, you can manually increase the number of bins in the histogram:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "plt.hist(df['MEDV'],bins=50)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Seaborn has a somewhat more attractive version of the standard matplotlib histogram: the distribution plot. This is a combination histogram and kernel density estimate (KDE) plot (essentially a smoothed histogram):"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "sns.distplot(df['MEDV'])",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Another commonly used plot is the Seaborn jointplot, which combines histograms for two columns along with a scatterplot:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "sns.jointplot(df['RM'], df['MEDV'], kind='scatter')",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Unfortunately, many of the dots print over each other. You can help address this by adding some alpha blending, a figure that sets the transparency for the dots so that concentrations of them drawing over one another will be apparent:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "sns.jointplot(df['RM'], df['MEDV'], kind='scatter', alpha=0.3)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Another way to see patterns in your data is with a two-dimensional KDE plot. Darker colors here represent a higher concentration of data points:"
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "sns.kdeplot(df['RM'], df['MEDV'], shade=True)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "collapsed": true
      },
      "cell_type": "markdown",
      "source": "Note that while the KDE plot is very good at showing concentrations of data points, finer structures like linear relationships (such as the clear relationship between the number of rooms in homes and the house price) are lost in the KDE plot.\n\nFinally, the pairplot in Seaborn allows you to see scatterplots and histograms for several columns in one table. Here we have played with some of the keywords to produce a more sophisticated and easier to read pairplot that incorporates both alpha blending and linear regression lines for the scatterplots."
    },
    {
      "metadata": {
        "trusted": false
      },
      "cell_type": "code",
      "source": "sns.pairplot(df[['RM', 'AGE', 'LSTAT', 'DIS', 'MEDV']], kind=\"reg\", plot_kws={'line_kws':{'color':'red'}, 'scatter_kws': {'alpha': 0.1}})",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "collapsed": true
      },
      "cell_type": "markdown",
      "source": "Visualization is the start of the really cool, fun part of data science. So play around with these visualization tools and see what you can learn from the data!"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "> **Takeaway:** An old joke goes: “What does a data scientist seen when they look at a dataset? A bunch of numbers.” There is more than a little truth in that joke. Visualization is often the key to finding patterns and correlations in your data. While visualization cannot often deliver precise results, it can point you in the right direction to ask better questions and efficiently find value in the data."
    }
  ],
  "metadata": {
    "anaconda-cloud": {},
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "mimetype": "text/x-python",
      "nbconvert_exporter": "python",
      "name": "python",
      "file_extension": ".py",
      "version": "3.5.4",
      "pygments_lexer": "ipython3",
      "codemirror_mode": {
        "version": 3,
        "name": "ipython"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}