Chapter 7 - Cleaning up messy data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The usual preamble\n",
    "%matplotlib inline\n",
    "\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "\n",
    "# Make the graphs a bit prettier, and bigger\n",
    "plt.style.use('ggplot')\n",
    "plt.rcParams['figure.figsize'] = (15, 5)\n",
    "plt.rcParams['font.family'] = 'sans-serif'\n",
    "\n",
    "# This is necessary to show lots of columns in pandas 0.12. \n",
    "# Not necessary in pandas 0.13.\n",
    "pd.set_option('display.width', 5000) \n",
    "pd.set_option('display.max_columns', 60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the main problems with messy data is: how do you know if it's messy or not?\n",
    "\n",
    "We're going to use the NYC 311 service request dataset again here, since it's big and a bit unwieldy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests = pd.read_csv('data/311-service-requests.csv', dtype='unicode')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7.1 How do we know if it's messy? "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We're going to look at a few columns here. I know already that there are some problems with the zip code, so let's look at that first.\n",
    " \n",
    "To get a sense for whether a column has problems, I usually use `.unique()` to look at all its values. If it's a numeric column, I'll instead plot a histogram to get a sense of the distribution.\n",
    "\n",
    "When we look at the unique values in \"Incident Zip\", it quickly becomes clear that this is a mess.\n",
    "\n",
    "Some of the problems:\n",
    "\n",
    "* Some have been parsed as strings, and some as floats\n",
    "* There are `nan`s \n",
    "* Some of the zip codes are `29616-0759` or `83`\n",
    "* There are some N/A values that pandas didn't recognize, like 'N/A' and 'NO CLUE'\n",
    "\n",
    "What we can do:\n",
    "\n",
    "* Normalize 'N/A' and 'NO CLUE' into regular nan values\n",
    "* Look at what's up with the 83, and decide what to do\n",
    "* Make everything strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests['Incident Zip'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7.2 Fixing the nan values and string/float confusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can pass a `na_values` option to `pd.read_csv` to clean this up a little bit. We can also specify that the type of Incident Zip is a string, not a float."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "na_values = ['NO CLUE', 'N/A', '0']\n",
    "requests = pd.read_csv('data/311-service-requests.csv', na_values=na_values, dtype={'Incident Zip': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests['Incident Zip'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7.3 What's up with the dashes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows_with_dashes = requests['Incident Zip'].str.contains('-').fillna(False)\n",
    "len(requests[rows_with_dashes])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests[rows_with_dashes]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I thought these were missing data and originally deleted them like this:\n",
    "\n",
    "`requests['Incident Zip'][rows_with_dashes] = np.nan`\n",
    "\n",
    "But then my friend Dave pointed out that 9-digit zip codes are normal. Let's look at all the zip codes with more than 5 digits, make sure they're okay, and then truncate them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "long_zip_codes = requests['Incident Zip'].str.len() > 5\n",
    "requests['Incident Zip'][long_zip_codes].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those all look okay to truncate to me."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests['Incident Zip'] = requests['Incident Zip'].str.slice(0, 5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Done."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Earlier I thought 00083 was a broken zip code, but turns out Central Park's zip code 00083! Shows what I know. I'm still concerned about the 00000 zip codes, though: let's look at that. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests[requests['Incident Zip'] == '00000']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This looks bad to me. Let's set these to nan."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_zips = requests[requests['Incident Zip'] == '00000']\n",
    "requests.loc[zero_zips.index, 'Incident Zip'] = np.nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great. Let's see where we are now:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(unique_zips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "unique_zips = requests['Incident Zip'].unique()\n",
    "# remove NA values\n",
    "unique_zips = unique_zips[unique_zips != np.nan]\n",
    "unique_zips = [x for x in unique_zips if str(x) != 'nan']\n",
    "unique_zips = [int(x) for x in unique_zips]\n",
    "unique_zips.sort()\n",
    "unique_zips"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Amazing! This is much cleaner. There's something a bit weird here, though -- I looked up 77056 on Google maps, and that's in Texas.\n",
    "\n",
    "Let's take a closer look:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zips = requests['Incident Zip']\n",
    "# Let's say the zips starting with '0' and '1' are okay, for now. (this isn't actually true -- 13221 is in Syracuse, and why?)\n",
    "is_close = zips.str.startswith('0') | zips.str.startswith('1')\n",
    "# There are a bunch of NaNs, but we're not interested in them right now, so we'll say they're False\n",
    "is_far = ~(is_close) & zips.notnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zips[is_far]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests[is_far][['Incident Zip', 'Descriptor', 'City']].sort_values('Incident Zip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, there really are requests coming from LA and Houston! Good to know. Filtering by zip code is probably a bad way to handle this -- we should really be looking at the city instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests['City'].str.upper().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like these are legitimate complaints, so we'll just leave them alone."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 7.4 Putting it together"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's what we ended up doing to clean up our zip codes, all together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "na_values = ['NO CLUE', 'N/A', '0']\n",
    "requests = pd.read_csv('data/311-service-requests.csv', \n",
    "                       na_values=na_values, \n",
    "                       dtype={'Incident Zip': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_zip_codes(zips):\n",
    "    # Truncate everything to length 5 \n",
    "    zips = zips.str.slice(0, 5)\n",
    "    \n",
    "    # Set 00000 zip codes to nan\n",
    "    zero_zips = zips == '00000'\n",
    "    zips[zero_zips] = np.nan\n",
    "    \n",
    "    return zips"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests['Incident Zip'] = fix_zip_codes(requests['Incident Zip'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requests['Incident Zip'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<style>\n",
    "    @font-face {\n",
    "        font-family: \"Computer Modern\";\n",
    "        src: url('http://mirrors.ctan.org/fonts/cm-unicode/fonts/otf/cmunss.otf');\n",
    "    }\n",
    "    div.cell{\n",
    "        width:800px;\n",
    "        margin-left:16% !important;\n",
    "        margin-right:auto;\n",
    "    }\n",
    "    h1 {\n",
    "        font-family: Helvetica, serif;\n",
    "    }\n",
    "    h4{\n",
    "        margin-top:12px;\n",
    "        margin-bottom: 3px;\n",
    "       }\n",
    "    div.text_cell_render{\n",
    "        font-family: Computer Modern, \"Helvetica Neue\", Arial, Helvetica, Geneva, sans-serif;\n",
    "        line-height: 145%;\n",
    "        font-size: 130%;\n",
    "        width:800px;\n",
    "        margin-left:auto;\n",
    "        margin-right:auto;\n",
    "    }\n",
    "    .CodeMirror{\n",
    "            font-family: \"Source Code Pro\", source-code-pro,Consolas, monospace;\n",
    "    }\n",
    "    .text_cell_render h5 {\n",
    "        font-weight: 300;\n",
    "        font-size: 22pt;\n",
    "        color: #4057A1;\n",
    "        font-style: italic;\n",
    "        margin-bottom: .5em;\n",
    "        margin-top: 0.5em;\n",
    "        display: block;\n",
    "    }\n",
    "    \n",
    "    .warning{\n",
    "        color: rgb( 240, 20, 20 )\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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}