2 - Data Wrangling with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Wrangling with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've seen how to get data with Python. Now let's do some stuff! From here on, we're going to mostly use the PyData stack rather than Python built-in functionality.\n",
    "\n",
    "Our objective in this section is to learn enough to clean the larger sample of Chicago Health Inspection data and get it ready for modeling."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preliminaries: DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As mentioned, the core data structure in pandas is called a DataFrame. A DataFrame is a tabular data structure, holding many columns, similar to a spreadsheet.\n",
    "\n",
    "The **Key Features** are\n",
    "\n",
    "* Easy handling of **missing data**\n",
    "* **Size mutability**: columns can be inserted and deleted from DataFrames\n",
    "* Automatic and explicit **data alignment**: objects can be explicitly aligned to a set of labels, or the data can be aligned automatically\n",
    "* Powerful, flexible **group by functionality** to perform split-apply-combine operations on data sets\n",
    "* Intelligent label-based **slicing**, **fancy indexing**, and **subsetting** of large data sets\n",
    "* Intuitive **merging and joining** data sets\n",
    "* Flexible **reshaping and pivoting** of data sets\n",
    "* **Hierarchical labeling** of axes\n",
    "* Robust **IO tools** for loading data from flat files, Excel files, databases, and HDF5\n",
    "* **Time series functionality**: \n",
    "  * date range generation and frequency conversion\n",
    "  * moving window statistics\n",
    "  * moving window linear regressions\n",
    "  * date shifting and lagging, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = pd.read_csv(\"data/health_inspection_chi.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas provides labelled **indices** to access rows and columns, should they have natural labels."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For example, with this data set we have a natural unique identifier in the `inspection_id` column. We might wish to make this our index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = dta.set_index('inspection_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Indexing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To look at a column from a DataFrame, you can either use attribute lookup."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.address"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or you can use the **getitem** syntax that relies on square brackets `[]`, which is familiar from dealing with dictionaries (uses `__getitem__`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta['address']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These two operations return pandas **Series** objects. **Series** are like single-column DataFrames. If you want to preserve the DataFrame type, index the DataFrame with a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta[['address']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use this syntax to pull out multiple columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta[['address', 'inspection_date']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can index the rows, by using the **loc** and **iloc** accessors.\n",
    "\n",
    "`loc` does *label-based* indexing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.loc[[1965287, 1329698]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`iloc` on the other hand provides *integer-based* indexing. We can pass a list of rows integers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.iloc[[0, 2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Both support the Python **slice notation** (`start:stop:step`). This can be really powerful."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.iloc[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.loc[:1335320]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that these inspection ids are *not* sorted, yet we can still use slice notation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, we can also combine row and index labeling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.iloc[:5, [0, 5]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.loc[:68091, [\"address\", \"inspection_date\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Data for Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far, we've explicitly made an index. We may next want to convert to the dates to datetime types. Here we'll use the **apply** function to apply a function to each row of a Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.inspection_date = dta.inspection_date.apply(pd.to_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.inspection_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's cast zip code from a float to a string. Some zip codes can start with 0 (not in Chicago), and we need to account for that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "\n",
    "def float_to_zip(zip_code):\n",
    "    if np.isnan(zip_code):\n",
    "        return np.nan\n",
    "    \n",
    "    # 0 makes sure to left-pad with zero\n",
    "    # zip codes have 5 digits\n",
    "    # .0 means, we don't want anything after the decimal\n",
    "    # f is for float\n",
    "    zip_code = \"{:05.0f}\".format(zip_code)\n",
    "    return zip_code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we use Python's **string formatting** facilities to convert from a numeric type to a string. Some of the zip codes are empty strings in the file. Pandas uses numpy's `NaN` to indicate missingness, so we'll return it here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.zip = dta.zip.apply(float_to_zip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrames have a `dtypes` attribute for checking the data types. Pandas relies on NumPy's dtypes objects. Here we see that the `object` dtype is used to hold strings. This for technical reasons."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.dtypes[['inspection_date', 'zip']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also convert variables' types, using `astype`. Here, we'll explicitly cast to pandas Categorical type, which is the only non-native numpy type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.results = dta.results.astype('category')\n",
    "dta.risk = dta.risk.astype('category')\n",
    "dta.inspection_type = dta.inspection_type.astype('category')\n",
    "dta.facility_type = dta.facility_type.astype('category')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we only select the categorical types, we can see some categorical variables descriptions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `select_dtypes` method to pull out a DataFrame with only the asked for types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.select_dtypes(['category'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we might want to exclude a column like `location` since we have the separate `latitude` and `longitude` columns. We can delete columns in a DataFrame using Python's built-in `del` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del dta['location']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with Types using csv Reader"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do everything that we did above by providing options to `pd.read_csv`.\n",
    "\n",
    "We saw before that `csv` reads everything in as strings, `json` does some type conversion with facility for doing more, and `pandas` does a bit more type conversion but it isn't always what we want. For example, we want the zip codes to stay strings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at how to do with pandas `read_csv`. First, we can use the `parse_dates` argument to read in the larger inspections data sample and tell pandas that one of our columns is a date column. We'll also go ahead and make `inspection_id` the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = pd.read_csv(\n",
    "    \"data/health_inspection_chi.csv\", \n",
    "    index_col=\"inspection_id\",\n",
    "    parse_dates=[\"inspection_date\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we want to turn the zip codes into strings. Here, we need to assume that the input (from the file) is a string as opposed to the above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "\n",
    "def float_to_zip(zip_code):\n",
    "    # convert from the string in the file to a float\n",
    "    try:\n",
    "        zip_code = float(zip_code)\n",
    "    except ValueError:  # some of them are empty\n",
    "        return np.nan\n",
    "    \n",
    "    # 0 makes sure to left-pad with zero\n",
    "    # zip codes have 5 digits\n",
    "    # .0 means, we don't want anything after the decimal\n",
    "    # f is for float\n",
    "    zip_code = \"{:05.0f}\".format(zip_code)\n",
    "    return zip_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "float_to_zip('1234')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "float_to_zip('123456')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As another example of defensive programming, we have to make sure that empty strings are handled."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "float_to_zip('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can supply this function to the `converters` argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = pd.read_csv(\n",
    "    \"data/health_inspection_chi.csv\",\n",
    "    converters={\n",
    "        'zip': float_to_zip\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To exclude location, we can take advantage of the fact that the `usecols` argument accepts a function to exclude `location`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = pd.read_csv(\n",
    "    \"data/health_inspection_chi.csv\",\n",
    "    usecols=lambda col: col != 'location'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we are using a **lambda function** that returns `False` for the location parameter. Lambda functions are what are known as anonymous functions, because they don't have a name. This kind of thing is precisely their intended use.\n",
    "\n",
    "Here we use a function `lambda x: x` to map the identity function over a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(map(lambda x: x, [1, 2, 3]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, in a few cases we may want to take advantage of the pandas native `categorical` type. We can use the `dtype` argument for this, passing a dictionary of type mappings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta = pd.read_csv(\n",
    "    \"data/health_inspection_chi.csv\",\n",
    "    dtype={\n",
    "        'results': 'category',\n",
    "        'risk': 'category',\n",
    "        'inspection_type': 'category',\n",
    "        'facility_type': 'category'\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.risk.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Put all of the above `read_csv` options together in a single call to `read_csv`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/pandas_read_csv.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## String Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, let's start to dig into the data a little bit more. One of the things we're going to be really interested in exploring is the free text of the violations field."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The first thing to notice is that the violations field has null values in it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We may want to ask ourselves if these values are missing at random or if there is some reason there's no written violation field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.loc[dta.violations.isnull()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like we're ok. The next thing to notice is that the violation field actually has a lot of violations in the same field for the same visit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.option_context(\"display.max_colwidth\", 500):\n",
    "    print(dta.violations.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's split these out to make a longer DataFrame where each violation is a single row. Pandas provides a nice way to munge string data through the `str` accessor on string columns.\n",
    "\n",
    "```python\n",
    "dta.violations.str.<TAB>\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Let's see how many violations we have per visit. What does the distribution of violations look like? Explore the methods on the `str` accessor and, perhaps, the `quantile` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Type your solution here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load solutions/violation_distribution.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ok, we have a manageable number of violations. Let's split the violations and then turn them into a long DataFrame with a single row for each violation within each visit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations = dta.violations.str.split(\"\\|\", expand=True)\n",
    "violations.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we `unstack` the DataFrame, we're left with what's called a `MultiIndex`. This index has two *levels* now. One is the original `inspection_id`. The other is the, rather meaningless, column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations.unstack().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get rid of the empty rows first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations = violations.unstack().dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can drop the column name level, which we don't need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations.reset_index(level=0, drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One last cleaning step may be helpful here. When we split on the pipe ('`|`'), we likely kept some surrounding whitespace. We can remove that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations.str.startswith(\" \").any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations.str.strip().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "violations = violations.str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "((violations.str.startswith(\" \").any()) | \n",
    " (violations.str.endswith(\" \").any()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Later, we'll see how to combine these violations back with our original data to do some analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with Dates and Categoricals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above, we used the `str` accessor on the DataFrame. This isn't the only convenient accessor that pandas provides. There is also the `dt` accessor for datetime types and the `cat` accessor for categorical types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "dta.inspection_date.dt.<TAB>\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.inspection_date.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.inspection_date.dt.month.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's take a look at the categorical types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```python\n",
    "dta.risk.cat.<TAB>\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.risk.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dta.risk.cat.codes.head()"
   ]
  }
 ],
 "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
}