module-1/week-2/day-2-pandas-3/pandas-3-data-cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning with Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Changing data\n",
    "\n",
    "#### DataFrame.applymap() and Series.map()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ```.applymap()``` method takes a function as input that it will then apply to every entry in the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "uci = pd.read_csv('data/heart.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def successor(x):\n",
    "    return x + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci.applymap(successor).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.map()` method takes a function as input that it will then apply to every entry in the Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci['age'].map(successor).tail(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Anonymous Functions (Lambda Abstraction)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Simple functions can be defined right in the function call. This is called 'lambda abstraction'; the function thus defined has no name and hence is \"anonymous\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci['oldpeak'].map(lambda x: round(x))[:4]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise: Use an anonymous function to turn the entries in age to strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Methods for Re-Organizing DataFrames\n",
    "#### `.groupby()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those of you familiar with SQL have probably used the GROUP BY command. Pandas has this, too.\n",
    "\n",
    "The `.groupby()` method is especially useful for aggregate functions applied to the data grouped in particular ways."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci.groupby('sex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `.groups` and `.get_group()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci.groupby('sex').groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci.groupby('sex').get_group(0)  # .tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "uci.groupby('sex').std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise: Tell me the average cholesterol level for those with heart disease."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Your code here!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Reshaping a DataFrame\n",
    "\n",
    "### `.pivot()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those of you familiar with Excel have probably used Pivot Tables. Pandas has a similar functionality."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "uci.pivot(values='sex', columns='target').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Methods for Combining DataFrames: `.join()`, `.merge()`, `.concat()`, `.melt()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.join()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy1 = pd.DataFrame([[63, 142], [33, 47]], columns=['age', 'HP'])\n",
    "toy2 = pd.DataFrame([[63, 100], [33, 200]], columns=['age', 'HP'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "toy1.join(toy2.set_index('age'),\n",
    "          on='age',\n",
    "          lsuffix='_A',\n",
    "          rsuffix='_B').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.merge()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_chars = pd.read_csv('data/ds_chars.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "states = pd.read_csv('data/states.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_chars.merge(states,\n",
    "               left_on='home_state',\n",
    "               right_on='state',\n",
    "               how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `pd.concat()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise: Look up the documentation on pd.concat (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html) and use it to concatenate ds_chars and states.\n",
    "<br/>\n",
    "Your result should still have only five rows!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([ds_chars, states], sort=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `pd.melt()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Melting removes the structure from your DataFrame and puts the data in a 'variable' and 'value' format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds_chars.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.melt(ds_chars,\n",
    "        id_vars=['name'],\n",
    "        value_vars=['HP', 'home_state'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning\n",
    "## Scenario\n",
    "\n",
    "As data scientists, we want to build a model to predict the sale price of a house in Seattle in 2019, based on its square footage. We know that the King County Department of Assessments has comprehensive data available on real property sales in the Seattle area. We need to prepare the data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First, get the data!\n",
    "\n",
    "We'll need to download the two data files that we need. We can do this at the command line:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!cd data\n",
    "!wget https://aqua.kingcounty.gov/extranet/assessor/Real%20Property%20Sales.zip\n",
    "!wget https://aqua.kingcounty.gov/extranet/assessor/Residential%20Building.zip"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note:* If you do not have the `wget` command yet, you can install it with `brew install wget`, or use `curl <url> -O <filename>`.\n",
    "\n",
    "Note that `%20` in a URL translates into a space. Even though you should *never put spaces in filenames*, you may need to deal with spaces that _other_ people have used in filenames.\n",
    "\n",
    "There are two ways to handle the spaces in these filenames when referencing them at the command line."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1. You can _escape_ the spaces by putting a backslash (`\\`, remember _backslash is next to backspace_) before each one:\n",
    "\n",
    "`unzip Real\\ Property\\ Sales.zip`\n",
    "\n",
    "This is what happens if you tab-complete the filename in the terminal. Tab completion is your friend!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2. You can put the entire filename in quotes:\n",
    "\n",
    "`unzip \"Real Property Sales.zip\"`\n",
    "\n",
    "Try unzipping these files with the `unzip` command. The `unzip` command takes one argument, the name of the file that you want to unzip."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!unzip Real\\ Property\\ Sales.zip\n",
    "!cd .."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sales_df = pd.read_csv('data/Real Property Sales.zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Seeing pink? Warnings are useful!\n",
    "\n",
    "Note the warning above: `DtypeWarning: Columns (1, 2) have mixed types.` Because we start with an index of zero, the columns that we're being warned about are actually the _second_ and _third_ columns, `sales_df['Major']` and `sales_df['Minor']`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_df.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data overload?\n",
    "\n",
    "That's a lot of columns. We're only interested in identifying the date, sale price, and square footage of each specific property. What can we do?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_sales_df = sales_df[['Major', 'Minor', 'DocumentDate', 'SalePrice']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_sales_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bldg_df = pd.read_csv('data/Residential Building.zip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Another warning! Which column has index 11?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bldg_df.columns[11]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`ZipCode` seems like a potentially useful column. We'll need it to determine which house sales took place in Seattle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bldg_df.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### So many features!\n",
    "\n",
    "As data scientists, we should be _very_ cautious about discarding potentially useful data. But, today, we're interested in _only_ the total square footage of each property. What can we do?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_bldg_df = bldg_df[['Major', 'Minor', 'SqFtTotLiving', 'ZipCode']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "small_bldg_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = pd.merge(small_sales_df, small_bldg_df, on=['Major', 'Minor'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Error!\n",
    "\n",
    "Why are we seeing an error when we try to join the dataframes?\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <td style=\"text-align:left\"><pre>\n",
    "<class 'pandas.core.frame.DataFrame'>\n",
    "RangeIndex: 2013160 entries, 0 to 2013159\n",
    "Data columns (total 4 columns):\n",
    "Major           object\n",
    "Minor           object\n",
    "DocumentDate    object\n",
    "SalePrice       int64\n",
    "dtypes: int64(1), object(3)\n",
    "memory usage: 61.4+ MB</pre></td>\n",
    "        <td style=\"text-align:left\"><pre>\n",
    "<class 'pandas.core.frame.DataFrame'>\n",
    "RangeIndex: 511359 entries, 0 to 511358\n",
    "Data columns (total 4 columns):\n",
    "Major            511359 non-null int64\n",
    "Minor            511359 non-null int64\n",
    "SqFtTotLiving    511359 non-null int64\n",
    "ZipCode          468345 non-null object\n",
    "dtypes: int64(3), object(1)\n",
    "memory usage: 15.6+ MB\n",
    "</pre></td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "Review the error message in light of the above:\n",
    "\n",
    "* `ValueError: You are trying to merge on object and int64 columns.`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.to_numeric(sales_df['Major'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Error!\n",
    "\n",
    "Note the useful error message above:\n",
    "\n",
    "`ValueError: Unable to parse string \"      \" at position 936643`\n",
    "\n",
    "In this case, we want to treat non-numeric values as missing values. Let's see if there's a way to change how the `pd.to_numeric` function handles errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The single question mark means \"show me the docstring\"\n",
    "pd.to_numeric?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's the part that we're looking for:\n",
    "```\n",
    "errors : {'ignore', 'raise', 'coerce'}, default 'raise'\n",
    "    - If 'raise', then invalid parsing will raise an exception\n",
    "    - If 'coerce', then invalid parsing will be set as NaN\n",
    "    - If 'ignore', then invalid parsing will return the input\n",
    "```\n",
    "\n",
    "Let's try setting the `errors` parameter to `'coerce'`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_sales_df.loc[:,'Major'] = pd.to_numeric(sales_df['Major'], errors='coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Did it work?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_sales_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It worked! Let's do the same thing with the `Minor` parcel number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_sales_df['Minor'] = pd.to_numeric(sales_df['Minor'], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "small_sales_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's try our join again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = pd.merge(small_sales_df, small_bldg_df, on=['Major', 'Minor'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see right away that we're missing zip codes for many of the sales transactions. (1321536 non-null entries for ZipCode is fewer than the 1436772 entries in the dataframe.) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data.loc[sales_data['ZipCode'].isna()].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Because we are interested in finding houses in Seattle zip codes, we will need to drop the rows with missing zip codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = sales_data.loc[~sales_data['ZipCode'].isna(), :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Your turn: Data Cleaning with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Investigate and drop rows with invalid values in the SalePrice and SqFtTotLiving columns.\n",
    "\n",
    "Use multiple notebook cells to accomplish this! Press `[esc]` then `B` to create a new cell below the current cell. Press `[return]` to start typing in the new cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = sales_data.loc[(sales_data['SalePrice'] > 0) & sales_data['SqFtTotLiving'] > 0]\n",
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Investigate and handle non-numeric ZipCode values\n",
    "\n",
    "Can you find a way to shorten ZIP+4 codes to the first five digits?\n",
    "\n",
    "What's the right thing to do with missing values?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the error message and decide how to fix it.\n",
    "# Note: using errors='coerce' is the *wrong* choice in this case.\n",
    "def is_integer(x):\n",
    "    try:\n",
    "        _ = int(x)\n",
    "    except ValueError:\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "pd.to_numeric(sales_data['ZipCode'])\n",
    "# sales_data.ZipCode.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Add a column for PricePerSqFt\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Subset the data to 2019 sales only.\n",
    "\n",
    "We can assume that the DocumentDate is approximately the sale date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Subset the data to zip codes within the City of Seattle.\n",
    "\n",
    "You'll need to find a list of Seattle zip codes!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. What is the mean price per square foot for a house sold in Seattle in 2019?\n",
    "\n",
    "Don't just type the answer. Type code that generates the answer as output!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}