classes/Class 10.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "53fce03f",
   "metadata": {},
   "source": [
    "## Module 10 Class activities\n",
    "This notebook is a starting point for the exercises and activities that we'll do in class. \n",
    "\n",
    "We'll revisit our analysis of ADUs, and try and speed things up and reduce memory usage.\n",
    "\n",
    "Then, we'll have some practice with SQL.\n",
    "\n",
    "Before you attempt any of these activities, make sure to watch the video lectures for this module."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c092609b",
   "metadata": {},
   "source": [
    "### Optimizing data types and parsing csv files\n",
    "Let's revist the ADU data that we saw in Lecture 12 (classification). There are two csv files that we read in – one with the permit data, and one with the parcel data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4133f155",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "permits = pd.read_csv('../lectures/data/ADUs/ADU_permits.csv')  # this file should be in your GitHub folder\n",
    "permits.info() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e096fa50",
   "metadata": {},
   "outputs": [],
   "source": [
    "parcels = pd.read_csv('../lectures/data/ADUs/parcels.csv')\n",
    "parcels.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f995697",
   "metadata": {},
   "source": [
    "For me, the permits data takes 492KB of memory. The parcels data uses 84MB. Imagine that you had a much bigger data file (perhaps more counties, or perhaps more columns). How can you reduce the memory requirements of these dataframes?\n",
    "\n",
    "*Hint*: I didn't mention this in the lecture, but pandas has a new \"nullable integer\" datatype which can take missing values (NaNs).\n",
    "\n",
    "To take advantage of this, use `Int16`, `Int32`, etc. rather than `int16`, `int32`, etc. The capital I is the nullable integer datatype; the lower-case i is the numpy datatype. [See here for a detailed explanation.](https://pandas.pydata.org/docs/user_guide/integer_na.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89523f4a",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<strong>Exercise:</strong> Reduce the memory usage of each dataframe as much as you can.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f39662ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10ea52f5",
   "metadata": {},
   "source": [
    "Now, imagine that the dataframes wouldn't even load into memory in the first place?\n",
    "\n",
    "Also imagine that you don't need the `Bathrooms1` column.\n",
    "\n",
    "Use additional arguments to `pd.read_csv()` and the datatypes you used in the answer above to load in the dataframes again."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34b920d0",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<strong>Exercise:</strong> Load the dataframes from disk in the most memory-efficient way possible.\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6a7d2a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b78b5b4f",
   "metadata": {},
   "source": [
    "### Speeding up joins\n",
    "\n",
    "Now let's try and join them together in an efficient way.\n",
    "\n",
    "Remember `%timeit` (for a line of code) and `%%timeit` (for a cell) will tell you how long your code takes to run.\n",
    "\n",
    "Before we join, we need to drop the rows with no parcel number, and create a concatenated column for the APN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aaf6f7f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "permits = permits[permits['Assessor Parcel']!='***']\n",
    "permits['APN'] = (permits['Assessor Book'].astype(int).astype(str).str.zfill(4) + '-' \n",
    "                   + permits['Assessor Page'].astype(int).astype(str).str.zfill(3) + '-'\n",
    "                   + permits['Assessor Parcel'].astype(int).astype(str).str.zfill(3))\n",
    "#drop the duplicates (take the first)\n",
    "permits = permits.groupby('APN').first()\n",
    "parcels = parcels.groupby('APN').first()\n",
    "\n",
    "permits.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d86f9390",
   "metadata": {},
   "source": [
    "This is how we joined the parcels in lecture."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4598912",
   "metadata": {},
   "outputs": [],
   "source": [
    "joinedDf = parcels.join(permits, how='left') # left is the default so we could omit that argument"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6ff9249",
   "metadata": {},
   "source": [
    "How long does that take?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d125ada",
   "metadata": {},
   "outputs": [],
   "source": [
    "%timeit joinedDf = parcels.join(permits, how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e43893f",
   "metadata": {},
   "source": [
    "What if we use `pd.merge()` on a column, rather than the index? \n",
    "\n",
    "Let's first reset the indexes, so that `APN` becomes a regular column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "915ea077",
   "metadata": {},
   "outputs": [],
   "source": [
    "parcels.reset_index(inplace=True)\n",
    "permits.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24327cee",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<strong>Exercise:</strong> How long does it take to merge using the columns?</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1924fbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f31c9043",
   "metadata": {},
   "source": [
    "If you are curious, indexes are faster because they are [stored as hashes](https://stackoverflow.com/questions/27238066/what-is-the-point-of-indexing-in-pandas).\n",
    "\n",
    "Before we move on, let's create the `has_adu` column as we did before."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf0736fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "joinedDf['has_adu'] = joinedDf['# of Accessory Dwelling Units']>=1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b5e0b78",
   "metadata": {},
   "source": [
    "## SQL\n",
    "\n",
    "Finally, let's do some simple SQL queries on the joined dataframe.\n",
    "\n",
    "Implement the following queries as SQL, using `pandasql`.\n",
    "\n",
    "1. What's the total land value of all the parcels? (The column `Roll_LandValue`.)\n",
    "\n",
    "2. How many parcels have ADUs? \n",
    "\n",
    "3. What's the average area of the building size (`SQFTmain1`), with and without an ADU? (Hint: use `GROUP BY`.)\n",
    "\n",
    "4. Same as above, but for residential parcels only (`UseType='Residential'`)\n",
    "\n",
    "Compare your results to a typical `pandas` query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b37c0ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandasql import PandaSQL\n",
    "pdsql = PandaSQL()\n",
    "\n",
    "# your code here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2818a26",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<h3>You should now be able to:</h3>\n",
    "<ul>\n",
    "  <li>Deal with larger datasets on a standard laptop computer</li>\n",
    "  <li>Profile your code to identify bottlenecks</li>\n",
    "  <li>Write simple SQL queries</li>\n",
    "  <li>Go forth and do data science!</li>\n",
    "</ul>\n",
    "</div>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}