3 - Exploratory Analysis with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploratory Analysis\n",
    "\n",
    "That's it for some preliminary cleaning. Don't worry, there will be more. Let's start to look in a bit more detail at the data, though. In this section, we're going to start to write some code that's typical for day-to-day data cleaning tasks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from load_data import dta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `info` to get some high-level information about the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And `describe` goes into a bit more detail for the *numeric* types, of which we don't have many here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could do the same for the categorical types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.select_dtypes(['category']).describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GroupBy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's ask the most obvious question. Which are the best and the worst restaurants? We'll want to use pandas `GroupBy` functionality to implement the `split-apply-combine` pattern.\n",
    "\n",
    "The idea here is that we **split** the data by some key or set of keys then **apply** a function to each group and then **combine** the outputs back into a single DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let's see how many result categories there are. We can use `value_counts` to answer this question. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context(\"max.rows\", 10):\n",
    "    print(dta.results.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, let's group on the inspection `results` column and see who the best and worst are.\n",
    "\n",
    "When we call the `groupby` method we get back a `DataFrameGroupBy` object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper = dta.groupby(dta.results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can access the variables on this object, the same as a DataFrame, and any code called will execute within the groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = grouper.dba_name.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result is a Series with a `MultiIndex`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result.index.names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can index on the first element in a `MultiIndex` using square brackets and then use `sort_values` to find those restaurants that had a result of Fail the most."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context('max.rows', 15):\n",
    "    print(result[\"Fail\"].sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take a closer look above. Looks like we have some more data cleaning to do."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context('max.rows', 15):\n",
    "    print(result[\"Pass\"].sort_values(ascending=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is probably not the right way to think about this since there are many more Subways than local establishments.\n",
    "\n",
    "We could instead look at the ratio of Fail to Pass, though, of course, this isn't perfect either. \n",
    "\n",
    "Sometimes, it's not *always* obvious how to go about computing things that you want to compute. The `get_group` method allows you to pull out one of the split DataFrames and try your apply function on it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "GroupBy the `dba_name`. Use `get_group` to pull out the \"MCDONALD'S\" group. Write a function that calculates the relative number of Fail to Pass for this group. Run this function on the McDonald's group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/get_group.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can run this on everything, but it's going to be a little slow. Let's look at another way to approach this problem.\n",
    "\n",
    "Here group by *both* the inspection results and the DBA name. Then we ask for the `size` of each one of these groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = dta.groupby((dta.results, dta.dba_name)).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `div` method to divide these for us. As you can see the indices don't line up, but we don't have to worry about it. Pandas take care of index alignment for us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result[\"Fail\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result[\"Pass\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratio = result[\"Fail\"].div(result[\"Pass\"])\n",
    "ratio.sort_values(ascending=False, inplace=True)\n",
    "ratio"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have a lot of `NaN`s in the results from division-by-zero. We can drop those with a call to `dropna`. Also note that pandas lets you decide whether to treat `inf` as an NA."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context(\"use_inf_as_null\", True,\n",
    "                       \"max.rows\", 15):\n",
    "    print(ratio.dropna())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We might still not be wholly satisfied with our rules around comparisons here. First, we're looking at restaurant names not particular establishments. What does the distribution of inspection visits for establishments look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context(\"max.rows\", 10):\n",
    "    grouper = dta.groupby((dta.address, dta.dba_name))\n",
    "    print(grouper.size().describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, let's make things a little more challenging. Let's see what the Fail:Pass ratio is for restaurants with at least 3 visits that involved a high risk level. \n",
    "\n",
    "\n",
    "Now we're starting to get into some much more powerful pandas constructs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited = (dta.query(\"risk == 'Risk 1 (High)'\")\n",
    "           .groupby(('address', 'dba_name'))\n",
    "           .size()\n",
    "           .rename('n_visits')\n",
    "           .reset_index()  # make into a DataFrame\n",
    "           .query(\"n_visits >= 4\"))\n",
    "\n",
    "visited"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's unpack this. The first thing to note is how this code is organized. Each one of these methods return a pandas data structure on which we call the next method. This is called **method chaining**. We use the same trick seen above to split strings across lines to split several method calls by including the code between `()`.\n",
    "\n",
    "Next, we see several new methods. The first is **query**. When subsetting a DataFrame we have a few options. As we save above, we can index a DataFrame using integers. Likewise, we could pass an object of booleans as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.risk == \"Risk 1 (High)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.loc[dta.risk == \"Risk 1 (High)\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Always using indexing can be verbose, however. You may need compound statements, for example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.loc[(dta.risk == \"Risk 1 (High)\") | (dta.risk == \"Risk 1 (Medium)\")].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead, by using query we could write the following, which is slightly easier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.query(\"(risk == 'Risk 1 (High)') | (risk == 'Risk 1 (Medium)')\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Use `query` to find the visits that are to restaurants and that are complaint re-inspections."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/query.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "visited = (dta.query(\"risk == 'Risk 1 (High)'\")\n",
    "           .groupby(('address', 'dba_name'))\n",
    "           .size()\n",
    "           .rename('n_visits')  # size returns a nameless series\n",
    "           .reset_index()  # make into a DataFrame\n",
    "           .query(\"n_visits >= 4\"))\n",
    "```\n",
    "\n",
    "\n",
    "The next new method is the **rename** method. We use this to rename the unnamed Series returned by `size`.\n",
    "\n",
    "Finally, we filter on restaurants with 4 or more total visits."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The final piece is computing the Fail:Pass ratio of these restaurants. To do this, we need to take the output we've created `visited` and line our original data up with these addresses and DBA names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visited.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do this by using the **merge** method. Merge allows us to make two pandas DataFrames into a single DataFrame. By default, the `merge` method will join together two DataFrames on common columns, using an inner join method (a set intersection)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_visits = visited.merge(dta)\n",
    "merged_visits.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, we need to redo the analysis we started above for McDonald's. We take these merged DataFrames, group them by the inspection results, the address, and the DBA name and ask for the size of each group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(merged_visits\n",
    " .groupby(('results', 'address', 'dba_name'))\n",
    " .size()).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Take this result and **pipe** it (using the `pipe` method) to a function that computes Fail/Pass. Make sure your result does not have any missing values, and sort it such that those with the highest Fail/Pass ratios are highest.\n",
    "\n",
    "The **pipe** method allows for including user-defined functions in method chains. It takes the output of the thing on the left, and passes it to the thing on the right."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/pipe_results.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Now, take everything that we've done above, from `dta` to this final result, and put it together into a single method chain."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/complete_chain.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often, I'll try to place a method chain to get the data ready for more exploratory work at the top of a notebook, so I can proceed with any analyses."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's go back to our original data and add in the unstacked violations to the information that's unchanging. Recall from the previous notebook that we unstack the violations as follows. There are two new things to note here though. We add in a `to_frame` method to turn the unstacked Series into a DataFrame, and we `rename` the unnamed column in the resulting DataFrame back to `violations`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(dta.violations\n",
    " .str.split(\"|\", expand=True)\n",
    " .unstack()\n",
    " .dropna()\n",
    " .reset_index(level=0, drop=True)\n",
    " .str.strip()\n",
    " .rename('violations')\n",
    " .to_frame())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, we need to drop the violations from the original DataFrame, then we need to merge it with the unstacked violations Series that we created before. You can use `drop` to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.drop([\"violations\"], axis='columns').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Drop the original violations from `dta`, and **join** this to the unstacked violations as computed above. You'll probably want to use a **right join**.\n",
    "\n",
    "We use **join** here rather than **merge**. Join uses merge under the hood but conveniently allows us to join on the indices of the two DataFrames by default. One other difference is that join uses an inner merge by default, but that's not what we want here. Since we drop the null violations on the right-hand side DataFrame, we want to do a right join. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/join_violations.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have a relatively clean DataFrame, let's ask a few more questions. \n",
    "\n",
    "First, how many unique violations do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.violations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.violations.unique().shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Is this true? Do we really think there are this many violation numbers? Probably not. We can use the `str` accessor and some more munging to answer this. Here we pass a **regular expression** to `str.extract`. Extract expects a *capture group*, indicated by `()`. The regular expression `(\\d+\\)(?=\\.)` means capture 1 or more (`+`) digits (`\\d`) that is followed by (`(?=)`) a period `\\.`. We escape the period because a plain `.` is a wildcard for any character."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(dta.violations\n",
    " .str.extract(\"(\\d+)(?=\\.)\", expand=False)\n",
    " .astype(int))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So how many unique violations do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.sort(\n",
    "    dta.violations\n",
    "        .str.extract(\"(\\d+)(?=\\.)\", expand=False)\n",
    "        .astype(int)\n",
    "        .unique()\n",
    ").shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Second, can we figure out how many times an establishment previously failed an inspection (within the sample we have)? How might we approach this? \n",
    "\n",
    "First, we want to restrict the data to just a single row for each inspection. Since we merged everything with the unstacked violations above, we'll need to use `drop_duplicates` to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visits = dta.drop_duplicates([\"address\", \"dba_name\", \"inspection_date\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're going to rely on some pandas time-series functionality to do this, so we will need to ensure that the inspection dates are sorted within each group. GroupBy will preserve this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visits = visits.sort_values([\"address\", \"dba_name\", \"inspection_date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper = visits.groupby((visits.address, visits.dba_name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, we might ask, \"now what?\" Remember the trick to pull out groups? Let's use it to work with something we can think about."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_key = list(grouper.groups.keys())[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group = grouper.get_group(group_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group[['inspection_date', 'results']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since, we need this to be backwards looking, we will **shift** the data by one visit. Shifting will move the data around by either a number of periods or a frequency. In this case, we use a number of periods and shift forward by 1 period."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "group.shift(1)[['inspection_date', 'results']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we take the cumulative sum of this, we'll have an accurate picture of previous failures."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(group.shift(1).results == 'Fail').cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visit_num = grouper.apply(lambda df: (df.shift(1).results == 'Fail').cumsum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visit_num.head(n=15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(visit_num.reset_index(level=[0, 1], drop=True)\n",
    " .to_frame()\n",
    " .rename(\n",
    "    columns={'results': 'num_fails'}\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visit_num.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "visit_num.reset_index(level=[0, 1], drop=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(visit_num.reset_index(level=[0, 1], drop=True)\n",
    " .to_frame()\n",
    " .rename(\n",
    "    columns={'results': 'num_fails'}\n",
    "))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.join((visit_num.reset_index(level=[0, 1], drop=True)\n",
    " .to_frame()\n",
    " .rename(\n",
    "    columns={'results': 'num_fails'}\n",
    ")))"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}