archived/aggregating_combining_dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Aggregating-and-Combining-pandas-DataFrames\" data-toc-modified-id=\"Aggregating-and-Combining-pandas-DataFrames-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Aggregating and Combining <code>pandas</code> DataFrames</a></span><ul class=\"toc-item\"><li><span><a href=\"#Learning-Goals\" data-toc-modified-id=\"Learning-Goals-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Learning Goals</a></span><ul class=\"toc-item\"><li><span><a href=\"#Austin-Animal-Center-Data\" data-toc-modified-id=\"Austin-Animal-Center-Data-1.1.1\"><span class=\"toc-item-num\">1.1.1&nbsp;&nbsp;</span>Austin Animal Center Data</a></span></li></ul></li><li><span><a href=\"#Aggregating-over-DataFrames:-.groupby()\" data-toc-modified-id=\"Aggregating-over-DataFrames:-.groupby()-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Aggregating over DataFrames: <code>.groupby()</code></a></span><ul class=\"toc-item\"><li><span><a href=\"#.groups-and-.get_group()\" data-toc-modified-id=\".groups-and-.get_group()-1.2.1\"><span class=\"toc-item-num\">1.2.1&nbsp;&nbsp;</span><code>.groups</code> and <code>.get_group()</code></a></span><ul class=\"toc-item\"><li><span><a href=\"#Multi-Indexing\" data-toc-modified-id=\"Multi-Indexing-1.2.1.1\"><span class=\"toc-item-num\">1.2.1.1&nbsp;&nbsp;</span>Multi-Indexing</a></span></li></ul></li><li><span><a href=\"#Aggregating\" data-toc-modified-id=\"Aggregating-1.2.2\"><span class=\"toc-item-num\">1.2.2&nbsp;&nbsp;</span>Aggregating</a></span></li><li><span><a href=\"#Exercise\" data-toc-modified-id=\"Exercise-1.2.3\"><span class=\"toc-item-num\">1.2.3&nbsp;&nbsp;</span>Exercise</a></span></li></ul></li><li><span><a href=\"#Pivoting-a-DataFrame\" data-toc-modified-id=\"Pivoting-a-DataFrame-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Pivoting a DataFrame</a></span><ul class=\"toc-item\"><li><span><a href=\"#.pivot_table()\" data-toc-modified-id=\".pivot_table()-1.3.1\"><span class=\"toc-item-num\">1.3.1&nbsp;&nbsp;</span><code>.pivot_table()</code></a></span><ul class=\"toc-item\"><li><span><a href=\"#Example\" data-toc-modified-id=\"Example-1.3.1.1\"><span class=\"toc-item-num\">1.3.1.1&nbsp;&nbsp;</span>Example</a></span></li><li><span><a href=\"#Back-to-Austin-animals\" data-toc-modified-id=\"Back-to-Austin-animals-1.3.1.2\"><span class=\"toc-item-num\">1.3.1.2&nbsp;&nbsp;</span>Back to Austin animals</a></span></li></ul></li><li><span><a href=\"#Exercise\" data-toc-modified-id=\"Exercise-1.3.2\"><span class=\"toc-item-num\">1.3.2&nbsp;&nbsp;</span>Exercise</a></span></li></ul></li><li><span><a href=\"#Methods-for-Combining-DataFrames:-.join(),-.merge(),-pd.concat()\" data-toc-modified-id=\"Methods-for-Combining-DataFrames:-.join(),-.merge(),-pd.concat()-1.4\"><span class=\"toc-item-num\">1.4&nbsp;&nbsp;</span>Methods for Combining DataFrames: <code>.join()</code>, <code>.merge()</code>, <code>pd.concat()</code></a></span><ul class=\"toc-item\"><li><span><a href=\"#.join()\" data-toc-modified-id=\".join()-1.4.1\"><span class=\"toc-item-num\">1.4.1&nbsp;&nbsp;</span><code>.join()</code></a></span></li><li><span><a href=\"#.merge()\" data-toc-modified-id=\".merge()-1.4.2\"><span class=\"toc-item-num\">1.4.2&nbsp;&nbsp;</span><code>.merge()</code></a></span></li><li><span><a href=\"#The-how-Parameter\" data-toc-modified-id=\"The-how-Parameter-1.4.3\"><span class=\"toc-item-num\">1.4.3&nbsp;&nbsp;</span>The <code>how</code> Parameter</a></span></li><li><span><a href=\"#pd.concat()\" data-toc-modified-id=\"pd.concat()-1.4.4\"><span class=\"toc-item-num\">1.4.4&nbsp;&nbsp;</span><code>pd.concat()</code></a></span></li><li><span><a href=\"#EXTRA\" data-toc-modified-id=\"EXTRA-1.4.5\"><span class=\"toc-item-num\">1.4.5&nbsp;&nbsp;</span>EXTRA</a></span></li></ul></li><li><span><a href=\"#King-County-Assessments\" data-toc-modified-id=\"King-County-Assessments-1.5\"><span class=\"toc-item-num\">1.5&nbsp;&nbsp;</span>King County Assessments</a></span><ul class=\"toc-item\"><li><span><a href=\"#First,-get-the-data!\" data-toc-modified-id=\"First,-get-the-data!-1.5.1\"><span class=\"toc-item-num\">1.5.1&nbsp;&nbsp;</span>First, get the data!</a></span></li><li><span><a href=\"#Exercise\" data-toc-modified-id=\"Exercise-1.5.2\"><span class=\"toc-item-num\">1.5.2&nbsp;&nbsp;</span>Exercise</a></span></li></ul></li><li><span><a href=\"#Time-Permitting:-Data-Cleaning-with-Pandas\" data-toc-modified-id=\"Time-Permitting:-Data-Cleaning-with-Pandas-1.6\"><span class=\"toc-item-num\">1.6&nbsp;&nbsp;</span>Time Permitting: Data Cleaning with Pandas</a></span><ul class=\"toc-item\"><li><span><a href=\"#1.-Investigate-and-drop-rows-with-invalid-values-in-the-SalePrice-and-SqFtTotLiving-columns.\" data-toc-modified-id=\"1.-Investigate-and-drop-rows-with-invalid-values-in-the-SalePrice-and-SqFtTotLiving-columns.-1.6.1\"><span class=\"toc-item-num\">1.6.1&nbsp;&nbsp;</span>1. Investigate and drop rows with invalid values in the SalePrice and SqFtTotLiving columns.</a></span></li><li><span><a href=\"#2.-Investigate-and-handle-non-numeric-ZipCode-values\" data-toc-modified-id=\"2.-Investigate-and-handle-non-numeric-ZipCode-values-1.6.2\"><span class=\"toc-item-num\">1.6.2&nbsp;&nbsp;</span>2. Investigate and handle non-numeric ZipCode values</a></span></li><li><span><a href=\"#3.-Add-a-column-for-PricePerSqFt\" data-toc-modified-id=\"3.-Add-a-column-for-PricePerSqFt-1.6.3\"><span class=\"toc-item-num\">1.6.3&nbsp;&nbsp;</span>3. Add a column for PricePerSqFt</a></span></li><li><span><a href=\"#4.-Subset-the-data-to-2021-sales-only.\" data-toc-modified-id=\"4.-Subset-the-data-to-2021-sales-only.-1.6.4\"><span class=\"toc-item-num\">1.6.4&nbsp;&nbsp;</span>4. Subset the data to 2021 sales only.</a></span></li><li><span><a href=\"#5.-What-is-the-mean-price-per-square-foot-for-a-house-sold-in-Seattle-in-2021?\" data-toc-modified-id=\"5.-What-is-the-mean-price-per-square-foot-for-a-house-sold-in-Seattle-in-2021?-1.6.5\"><span class=\"toc-item-num\">1.6.5&nbsp;&nbsp;</span>5. What is the mean price per square foot for a house sold in Seattle in 2021?</a></span></li></ul></li><li><span><a href=\"#Level-Up:-pandas.set_option()\" data-toc-modified-id=\"Level-Up:-pandas.set_option()-1.7\"><span class=\"toc-item-num\">1.7&nbsp;&nbsp;</span>Level Up: <code>pandas.set_option()</code></a></span><ul class=\"toc-item\"><li><span><a href=\"#Block-Scientific-Notation\" data-toc-modified-id=\"Block-Scientific-Notation-1.7.1\"><span class=\"toc-item-num\">1.7.1&nbsp;&nbsp;</span>Block Scientific Notation</a></span></li><li><span><a href=\"#See-More-Rows\" data-toc-modified-id=\"See-More-Rows-1.7.2\"><span class=\"toc-item-num\">1.7.2&nbsp;&nbsp;</span>See More Rows</a></span></li></ul></li></ul></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "# Aggregating and Combining `pandas` DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:20.745695Z",
     "start_time": "2022-08-04T14:14:20.741721Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests as rq\n",
    "from sklearn.preprocessing import OneHotEncoder\n",
    "from zipfile import ZipFile"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Learning Goals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "- Use GroupBy objects to organize and aggregate data\n",
    "- Create pivot tables from DataFrames\n",
    "- Combine DataFrames by merging and appending "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We'll work with the Austin Animal Center dataset and with data from King County's Department of Assessments (Seattle housing data)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Austin Animal Center Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.423970Z",
     "start_time": "2022-08-04T14:14:21.056065Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "data = rq.get('https://data.austintexas.gov/resource/9t4d-g238.json').text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.466284Z",
     "start_time": "2022-08-04T14:14:22.426449Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "animals = pd.read_json(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.477103Z",
     "start_time": "2022-08-04T14:14:22.469079Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "animals.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregating over DataFrames: `.groupby()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Those of you familiar with SQL have probably used the GROUP BY command. (And if you haven't, you'll see it very soon!) 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": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.481077Z",
     "start_time": "2022-08-04T14:14:22.478561Z"
    }
   },
   "outputs": [],
   "source": [
    "animals.groupby('animal_type')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.484289Z",
     "start_time": "2022-08-04T14:14:22.482027Z"
    }
   },
   "outputs": [],
   "source": [
    "animals.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can group by multiple columns, and also return a DataFrameGroupBy object\n",
    "\n",
    "Notice the object type [DataFrameGroupBy](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html) object. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `.groups` and `.get_group()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.487475Z",
     "start_time": "2022-08-04T14:14:22.485141Z"
    }
   },
   "outputs": [],
   "source": [
    "animals.groupby(['animal_type', 'outcome_type'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.493202Z",
     "start_time": "2022-08-04T14:14:22.488474Z"
    }
   },
   "outputs": [],
   "source": [
    "# This retuns each group indexed by the group name: I.E. 'Bird', along with the row indices of each value\n",
    "animals.groupby('animal_type').groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we know we are working with a type of object, it opens up a suite of attributes and methods. One attribute we can look at is groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.507630Z",
     "start_time": "2022-08-04T14:14:22.494169Z"
    }
   },
   "outputs": [],
   "source": [
    "# Once we know the group indices, we can return the groups using those indices.\n",
    "animals.groupby('animal_type').get_group('Dog')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How would you get the same dataframe using dataframe slicing?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Multi-Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.518917Z",
     "start_time": "2022-08-04T14:14:22.509726Z"
    }
   },
   "outputs": [],
   "source": [
    "# Same goes for multi index groupbys\n",
    "animal_outcome = animals.groupby(['animal_type', 'outcome_type'])\n",
    "animal_outcome.groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.522466Z",
     "start_time": "2022-08-04T14:14:22.520342Z"
    }
   },
   "outputs": [],
   "source": [
    "# animal_outcome.groups is a dictionary, so we can access the group names using keys()\n",
    "animal_outcome.groups.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.536715Z",
     "start_time": "2022-08-04T14:14:22.523355Z"
    }
   },
   "outputs": [],
   "source": [
    "# We can then get a specific group, such as Cats that were adopted\n",
    "animal_outcome.get_group(('Cat', 'Adoption'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregating"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, as we will see in SQL, groupby objects are intended to be used with aggregation. In SQL, we will see that our queries that include GROUP BY require aggregation performed on columns.\n",
    "\n",
    "We can use `.sum()`, `.mean()`, `.count()`, `.max()`, `.min()`, etc. Find a list of common aggregations [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.545026Z",
     "start_time": "2022-08-04T14:14:22.537525Z"
    }
   },
   "outputs": [],
   "source": [
    "animals.groupby('animal_type').count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Use `.groupby()` to find the most recently born of each (main) animal type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T13:01:00.583444Z",
     "start_time": "2022-08-04T13:01:00.536673Z"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>Answer</summary>\n",
    "    <code>animals.groupby('animal_type')['date_of_birth'].max()</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Pivoting a DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### `.pivot_table()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Those of you familiar with Excel have probably used Pivot Tables. Pandas has a similar functionality.\n",
    "\n",
    "Grouping by two different columns can be very helpful."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.562723Z",
     "start_time": "2022-08-04T14:14:22.545848Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "animals.groupby(by=['outcome_type', 'sex_upon_outcome']).agg(len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "But it has the unsavory side effect of creating a two-level index. This can be a good time to use `.pivot_table()`.\n",
    "\n",
    "(There is also a `.pivot()`. For the somewhat subtle differences, see [here](https://stackoverflow.com/questions/30960338/pandas-difference-between-pivot-and-pivot-table-why-is-only-pivot-table-workin).)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.570686Z",
     "start_time": "2022-08-04T14:14:22.563695Z"
    }
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame({\"sex\": [\"male\", \"male\", \"male\", \"male\", \"male\",\n",
    "                          \"female\", \"female\", \"female\", \"female\"],\n",
    "                    \"num_puppies\": [\"one\", \"one\", \"one\", \"two\", \"two\",\n",
    "                          \"one\", \"one\", \"two\", \"two\"],\n",
    "                    \"breed\": [\"terrier\", \"retriever\", \"retriever\", \"terrier\",\n",
    "                          \"terrier\", \"retriever\", \"terrier\", \"terrier\",\n",
    "                          \"retriever\"],\n",
    "                    \"past_owners\": [1, 2, 2, 3, 3, 4, 5, 6, 7],\n",
    "                    \"family_members\": [2, 4, 5, 5, 6, 6, 8, 9, 9]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.587936Z",
     "start_time": "2022-08-04T14:14:22.571641Z"
    }
   },
   "outputs": [],
   "source": [
    "# This first example aggregates values by taking the sum.\n",
    "\n",
    "table = pd.pivot_table(df, values='past_owners', index=['sex', 'num_puppies'],\n",
    "                     columns=['breed'], aggfunc=np.sum)\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Back to Austin animals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.613818Z",
     "start_time": "2022-08-04T14:14:22.588799Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "animals.pivot_table(index='outcome_type', columns='sex_upon_outcome', aggfunc=len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Use `.pivot_table()` to add up the number of my tasks by category. Hint: Use `sum()` as your aggregating function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.620078Z",
     "start_time": "2022-08-04T14:14:22.614814Z"
    }
   },
   "outputs": [],
   "source": [
    "tasks = pd.DataFrame({'category': ['house', 'house', 'school', 'school'],\n",
    "                      'descr': ['kitchen', 'laundry', 'git', 'Python'],\n",
    "                      'priority': [2, 3, 4, 1], 'num_tasks': [2, 1, 2, 3]})\n",
    "\n",
    "tasks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.627257Z",
     "start_time": "2022-08-04T14:14:22.621034Z"
    }
   },
   "outputs": [],
   "source": [
    "tasks.pivot_table(values='num_tasks',index='category',aggfunc=sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>Answer</summary>\n",
    "    <code>tasks.pivot_table(values='num_tasks', index='category', aggfunc=sum)</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Methods for Combining DataFrames: `.join()`, `.merge()`, `pd.concat()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### `.join()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.633501Z",
     "start_time": "2022-08-04T14:14:22.628875Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "toy1 = pd.DataFrame([[63, 142], [33, 47]], columns=['age', 'HP'])\n",
    "toy2 = pd.DataFrame([[63, 100], [33, 200]], columns=['age', 'MP'])\n",
    "\n",
    "toy1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.637742Z",
     "start_time": "2022-08-04T14:14:22.634566Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "toy2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.647056Z",
     "start_time": "2022-08-04T14:14:22.638646Z"
    }
   },
   "outputs": [],
   "source": [
    "# We can't just join these as they are, since we haven't specified our suffixes.\n",
    "\n",
    "toy1.join(toy2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.648138Z",
     "start_time": "2022-08-04T14:14:22.569Z"
    }
   },
   "outputs": [],
   "source": [
    "toy1.join(toy2, lsuffix='1', rsuffix='2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we don't want to keep both, we could set the overlapping column as the index in each DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.648788Z",
     "start_time": "2022-08-04T14:14:22.636Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "toy1.set_index('age').join(toy2.set_index('age'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "For more on this method, check out the [doc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html)!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### `.merge()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or we could use `.merge()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.877609Z",
     "start_time": "2022-08-04T14:14:22.859226Z"
    }
   },
   "outputs": [],
   "source": [
    "toy1.merge(toy2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.891287Z",
     "start_time": "2022-08-04T14:14:22.879499Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "ds_chars = pd.read_csv('data/ds_chars.csv', index_col=0)\n",
    "ds_chars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.898530Z",
     "start_time": "2022-08-04T14:14:22.892620Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "states = pd.read_csv('data/states.csv', index_col=0)\n",
    "states"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `how` Parameter\n",
    "\n",
    "This parameter in both `.join()` and `.merge()` tells the compiler what sort of join to effect. We'll cover this in detail when we discuss SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.931673Z",
     "start_time": "2022-08-04T14:14:22.925013Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "ds_chars.merge(states,\n",
    "               left_on='home_state',\n",
    "               right_on='state',\n",
    "               how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:22.940277Z",
     "start_time": "2022-08-04T14:14:22.932766Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "ds_chars.merge(states,\n",
    "               left_on='home_state',\n",
    "               right_on='state',\n",
    "               how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### `pd.concat()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "This method takes a *list* of pandas objects as arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:23.097381Z",
     "start_time": "2022-08-04T14:14:23.074686Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "ds_full = pd.concat([ds_chars, states])\n",
    "ds_full"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "`pd.concat()`–– and many other pandas operations –– make use of an `axis` parameter. For this particular method I need to specify whether I want to concatenate the DataFrames *row-wise* (`axis=0`) or *column-wise* (`axis=1`). The default is `axis=0`, so let's override that!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:23.159877Z",
     "start_time": "2022-08-04T14:14:23.142832Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "ds_full = pd.concat([ds_chars, states], axis=1)\n",
    "ds_full"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T15:24:53.894930Z",
     "start_time": "2022-08-04T15:24:53.880416Z"
    }
   },
   "source": [
    "### EXTRA"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## King County Assessments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "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": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### First, get the data!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Go [here](https://info.kingcounty.gov/assessor/DataDownload/default.aspx) and download two files: \"Real Property Sales\" and \"Residential Building\". Then unzip them. (Or you can run the cells below if you prefer.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:40:07.228440Z",
     "start_time": "2022-08-04T14:32:22.844667Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# %%bash\n",
    "# cd data\n",
    "# curl -o property_sales.zip https://aqua.kingcounty.gov/extranet/assessor/Real%20Property%20Sales.zip "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:41:25.606339Z",
     "start_time": "2022-08-04T14:40:07.276642Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# %%bash\n",
    "# cd data\n",
    "# curl -o res_bldg.zip https://aqua.kingcounty.gov/extranet/assessor/Residential%20Building.zip "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:41:28.109223Z",
     "start_time": "2022-08-04T14:41:25.675973Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# zf = ZipFile('data/property_sales.zip', 'r')\n",
    "# zf.extractall('data')\n",
    "# zf.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:41:28.609345Z",
     "start_time": "2022-08-04T14:41:28.126039Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# zf = ZipFile('data/res_bldg.zip', 'r')\n",
    "# zf.extractall('data')\n",
    "# # zf.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:47:34.073572Z",
     "start_time": "2022-08-04T14:47:31.055399Z"
    },
    "hidden": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# You'll need to use a new encoding here. List of all encodings here:\n",
    "# https://docs.python.org/3/library/codecs.html#standard-encodings\n",
    "\n",
    "# Both of these csv files have many columns, so we'll just pre-select\n",
    "# which ones we want to use.\n",
    "\n",
    "sales_df = pd.read_csv('data/EXTR_RPSale.csv',\n",
    "                       encoding='latin-1',\n",
    "                       usecols=['Major', 'Minor', 'DocumentDate', 'SalePrice'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:47:38.668647Z",
     "start_time": "2022-08-04T14:47:38.642426Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "sales_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:52:10.988012Z",
     "start_time": "2022-08-04T14:52:10.315847Z"
    },
    "hidden": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bldg_df = pd.read_csv('data/EXTR_ResBldg.csv',\n",
    "                     usecols=['Major', 'Minor', 'SqFtTotLiving', 'ZipCode'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:52:12.686958Z",
     "start_time": "2022-08-04T14:52:12.633579Z"
    },
    "hidden": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "bldg_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T15:27:01.489629Z",
     "start_time": "2022-08-04T15:27:00.418940Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "sales_data = pd.merge(sales_df, bldg_df, on=['Major', 'Minor'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T15:27:01.524906Z",
     "start_time": "2022-08-04T15:27:01.519105Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T15:27:03.249357Z",
     "start_time": "2022-08-04T15:27:02.833221Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "sales_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We can see right away that we're missing ZIP codes for many of the sales transactions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T15:27:05.966821Z",
     "start_time": "2022-08-04T15:27:05.852345Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "sales_data.loc[sales_data['ZipCode'].isna()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "What percentage of housing records are missing ZIP codes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>Answer</summary>\n",
    "    <code>sales_data['ZipCode'].isna().sum() / sales_data.shape[0]</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's drop the rows with missing zip codes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T15:30:03.711712Z",
     "start_time": "2022-08-04T15:30:03.442620Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "sales_data = sales_data.loc[~sales_data['ZipCode'].isna(), :]\n",
    "#sales_data2 = sales_data[~sales_data['ZipCode'].isna()]\n",
    "sales_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Time Permitting: Data Cleaning with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### 1. Investigate and drop rows with invalid values in the SalePrice and SqFtTotLiving columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>One possible answer here</summary>\n",
    "    <code>sales_data = sales_data[sales_data['SalePrice'] > 10000]</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### 2. Investigate and handle non-numeric ZipCode values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Can you find a way to shorten ZIP+4 codes to the first five digits?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:24.141400Z",
     "start_time": "2022-08-04T14:14:24.122253Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def is_integer(x):\n",
    "    try:\n",
    "        _ = int(x)\n",
    "    except ValueError:\n",
    "        return False\n",
    "    return True\n",
    "\n",
    "sales_data.loc[sales_data['ZipCode'].apply(is_integer) == False, 'ZipCode'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>One possible answer here</summary>\n",
    "    <code>def five_digit_ZIP(x):\n",
    "    try:\n",
    "        return int(str(x)[:5])\n",
    "    except:\n",
    "        return x\n",
    "sales_data['ZipCode'] = sales_data['ZipCode'].map(five_digit_ZIP)\n",
    "sales_data = sales_data.loc[sales_data['ZipCode'].apply(is_integer) == True, :]\n",
    "sales_data['ZipCode'] = sales_data['ZipCode'].map(int)</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### 3. Add a column for PricePerSqFt\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>Answer here</summary>\n",
    "    <code>sales_data['PricePerSqFt'] = sales_data['SalePrice'] / sales_data['SqFtTotLiving']</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### 4. Subset the data to 2021 sales only."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We can assume that the DocumentDate is approximately the sale date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>Answer here</summary>\n",
    "    <code>sales_data['DocumentDate'] = pd.to_datetime(sales_data['DocumentDate'])\n",
    "sales_data = sales_data.loc[sales_data['DocumentDate'] > '12/31/2020']</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### 5. What is the mean price per square foot for a house sold in Seattle in 2021?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<details>\n",
    "    <summary>Answer here</summary>\n",
    "    <code>sales_data['PricePerSqFt'].mean()</code>\n",
    "    </details>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "## Level Up: `pandas.set_option()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "We can adjust how `pandas` works by setting options in advance."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Block Scientific Notation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "For example, suppose we want to prevent numbers from being displayed in scientific notation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:25.026527Z",
     "start_time": "2022-08-04T14:14:25.011726Z"
    },
    "heading_collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df = pd.DataFrame([[1e9, 2e9], [3e9, 4e9]])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "Then we can use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:25.086189Z",
     "start_time": "2022-08-04T14:14:25.077582Z"
    },
    "heading_collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.float_format', '{:.2f}'.format)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### See More Rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "Or suppose we want `pandas` to show more rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:25.244237Z",
     "start_time": "2022-08-04T14:14:25.231303Z"
    },
    "heading_collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(np.array(range(100)))\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "In that case we can use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-08-04T14:14:25.322221Z",
     "start_time": "2022-08-04T14:14:25.306258Z"
    },
    "heading_collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', 100)\n",
    "\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "For complete documentation, see [here](https://pandas.pydata.org/pandas-docs/stable/user_guide/options.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "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.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "TOC",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}