notebooks/13-choropleth.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cc32808a",
   "metadata": {},
   "source": [
    "# Choropleth\n",
    "\n",
    "## Day 13 - 30 Day Map Challenge\n",
    "\n",
    "\n",
    "Today's map looks at median home value in the US using data from Zillow. Specifically, the change in median home value over the last 5 years per county using Zillow's Zillow Home Value Index (ZHVI). I used two data sources for this map:\n",
    "\n",
    "* Zillow's [home values dataset (ZHVI)](https://www.zillow.com/research/data/) - CSV\n",
    "* Natural Earth's [Admin 2 - Counties](https://www.naturalearthdata.com/downloads/10m-cultural-vectors/) - Shapefile\n",
    "\n",
    "![](https://raw.githubusercontent.com/johnymontana/30-day-map-challenge/625b4e474e83430a3f146f8017750a83533dcab2/img/choropleth/delta.png)\n",
    "\n",
    "To follow along create a free account in [Wherobots Cloud.](https://www.wherobots.services)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92dc162b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# All other packages are installed by default in Wherobots Cloud\n",
    "!pip install mapclassify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52f76b28",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sedona.spark import *\n",
    "from sedona.core.formatMapper.shapefileParser import ShapefileReader\n",
    "from sedona.utils.adapter import Adapter\n",
    "import geopandas\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38ea5198",
   "metadata": {},
   "outputs": [],
   "source": [
    "config = SedonaContext.builder().appName('zillowdata')\\\n",
    "    .config(\"spark.hadoop.fs.s3a.bucket.wherobots-geodata.aws.credentials.provider\",\"org.apache.hadoop.fs.s3a.AnonymousAWSCredentialsProvider\")\\\n",
    "    .getOrCreate()\n",
    "sedona = SedonaContext.create(config)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b786fee7",
   "metadata": {},
   "source": [
    "## Natural Earth Shapefile Import\n",
    "\n",
    "I uploaded the Shapefile _files_ to Wherobots Cloud using this structure:\n",
    "\n",
    "![](https://raw.githubusercontent.com/johnymontana/30-day-map-challenge/625b4e474e83430a3f146f8017750a83533dcab2/img/choropleth/files1.png)\n",
    "\n",
    "Note that since a Shapefile is composed of multiple files, the S3 URL we want to use is the location of the directory, not one of the individual files:\n",
    "\n",
    "![](https://raw.githubusercontent.com/johnymontana/30-day-map-challenge/625b4e474e83430a3f146f8017750a83533dcab2/img/choropleth/files2.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80d9779c",
   "metadata": {},
   "outputs": [],
   "source": [
    "S3_URL_NE = \"s3://wherobots-geodata/ne_10m_admin_2_counties/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d90fbd3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "spatialRDD = ShapefileReader.readToGeometryRDD(sedona, S3_URL_NE)\n",
    "counties_df = Adapter.toDf(spatialRDD, sedona)\n",
    "counties_df.createOrReplaceTempView(\"counties\")\n",
    "counties_df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69311d38",
   "metadata": {},
   "source": [
    "```\n",
    "root\n",
    " |-- geometry: geometry (nullable = true)\n",
    " |-- FEATURECLA: string (nullable = true)\n",
    " |-- SCALERANK: string (nullable = true)\n",
    " |-- ADM2_CODE: string (nullable = true)\n",
    " |-- ISO_3166_2: string (nullable = true)\n",
    " |-- ISO_A2: string (nullable = true)\n",
    " |-- ADM0_SR: string (nullable = true)\n",
    " |-- NAME: string (nullable = true)\n",
    " |-- NAME_ALT: string (nullable = true)\n",
    " |-- NAME_LOCAL: string (nullable = true)\n",
    " |-- TYPE: string (nullable = true)\n",
    " |-- TYPE_EN: string (nullable = true)\n",
    " |-- CODE_LOCAL: string (nullable = true)\n",
    " |-- REGION: string (nullable = true)\n",
    " |-- REGION_COD: string (nullable = true)\n",
    " |-- ABBREV: string (nullable = true)\n",
    " |-- AREA_SQKM: string (nullable = true)\n",
    " |-- SAMEASCITY: string (nullable = true)\n",
    " |-- LABELRANK: string (nullable = true)\n",
    " |-- NAME_LEN: string (nullable = true)\n",
    " |-- MAPCOLOR9: string (nullable = true)\n",
    " |-- MAPCOLOR13: string (nullable = true)\n",
    " |-- FIPS: string (nullable = true)\n",
    " |-- SOV_A3: string (nullable = true)\n",
    " |-- ADM0_A3: string (nullable = true)\n",
    " |-- ADM0_LABEL: string (nullable = true)\n",
    " |-- ADMIN: string (nullable = true)\n",
    " |-- GEONUNIT: string (nullable = true)\n",
    " |-- GU_A3: string (nullable = true)\n",
    " |-- MIN_LABEL: string (nullable = true)\n",
    " |-- MAX_LABEL: string (nullable = true)\n",
    " |-- MIN_ZOOM: string (nullable = true)\n",
    " |-- WIKIDATAID: string (nullable = true)\n",
    " |-- NE_ID: string (nullable = true)\n",
    " |-- latitude: string (nullable = true)\n",
    " |-- longitude: string (nullable = true)\n",
    " |-- NAME_AR: string (nullable = true)\n",
    " |-- NAME_BN: string (nullable = true)\n",
    " |-- NAME_DE: string (nullable = true)\n",
    " |-- NAME_EL: string (nullable = true)\n",
    " |-- NAME_EN: string (nullable = true)\n",
    " |-- NAME_ES: string (nullable = true)\n",
    " |-- NAME_FA: string (nullable = true)\n",
    " |-- NAME_FR: string (nullable = true)\n",
    " |-- NAME_HE: string (nullable = true)\n",
    " |-- NAME_HI: string (nullable = true)\n",
    " |-- NAME_HU: string (nullable = true)\n",
    " |-- NAME_ID: string (nullable = true)\n",
    " |-- NAME_IT: string (nullable = true)\n",
    " |-- NAME_JA: string (nullable = true)\n",
    " |-- NAME_KO: string (nullable = true)\n",
    " |-- NAME_NL: string (nullable = true)\n",
    " |-- NAME_PL: string (nullable = true)\n",
    " |-- NAME_PT: string (nullable = true)\n",
    " |-- NAME_RU: string (nullable = true)\n",
    " |-- NAME_SV: string (nullable = true)\n",
    " |-- NAME_TR: string (nullable = true)\n",
    " |-- NAME_UK: string (nullable = true)\n",
    " |-- NAME_UR: string (nullable = true)\n",
    " |-- NAME_VI: string (nullable = true)\n",
    " |-- NAME_ZH: string (nullable = true)\n",
    " |-- NAME_ZHT: string (nullable = true)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a07b9b80",
   "metadata": {},
   "outputs": [],
   "source": [
    "sedona.sql(\"\"\"\n",
    "SELECT geometry, FIPS AS fips, NAME_EN AS name, REGION AS state FROM counties LIMIT 10\n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48dbcb77",
   "metadata": {},
   "source": [
    "```\n",
    "+--------------------+-------+------------+-----+\n",
    "|            geometry|   fips|        name|state|\n",
    "+--------------------+-------+------------+-----+\n",
    "|MULTIPOLYGON (((-...|US53073|     Whatcom|   WA|\n",
    "|POLYGON ((-120.85...|US53047|    Okanogan|   WA|\n",
    "|POLYGON ((-118.83...|US53019|       Ferry|   WA|\n",
    "|POLYGON ((-118.21...|US53065|     Stevens|   WA|\n",
    "|POLYGON ((-117.42...|US53051|Pend Oreille|   WA|\n",
    "|POLYGON ((-117.03...|US16021|    Boundary|   ID|\n",
    "|POLYGON ((-116.04...|US30053|     Lincoln|   MT|\n",
    "|POLYGON ((-114.72...|US30029|    Flathead|   MT|\n",
    "|POLYGON ((-114.06...|US30035|     Glacier|   MT|\n",
    "|POLYGON ((-112.19...|US30101|       Toole|   MT|\n",
    "+--------------------+-------+------------+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a55fd109",
   "metadata": {},
   "source": [
    "## Load Zillow ZHVI CSV File\n",
    "\n",
    "Now that we have the geometries of each county we want to import the CSV file with the Zillow home value index data. I uploaded this file into Wherobots Cloud which I can access in my notebook environment using S3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba9aee07",
   "metadata": {},
   "outputs": [],
   "source": [
    "S3_URL_ZHVI = \"s3://wherobots-geodata/County_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edf631f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhvi_df = sedona.read.format('csv').option('header','true').option('delimiter', ',').load(S3_URL_ZHVI)\n",
    "zhvi_df.createOrReplaceTempView(\"zhvi\")\n",
    "zhvi_df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8121949",
   "metadata": {},
   "source": [
    "```\n",
    "root\n",
    " |-- RegionID: string (nullable = true)\n",
    " |-- SizeRank: string (nullable = true)\n",
    " |-- RegionName: string (nullable = true)\n",
    " |-- RegionType: string (nullable = true)\n",
    " |-- StateName: string (nullable = true)\n",
    " |-- State: string (nullable = true)\n",
    " |-- Metro: string (nullable = true)\n",
    " |-- StateCodeFIPS: string (nullable = true)\n",
    " |-- MunicipalCodeFIPS: string (nullable = true)\n",
    " |-- 2000-01-31: string (nullable = true)\n",
    " |-- 2000-02-29: string (nullable = true)\n",
    " |-- 2000-03-31: string (nullable = true)\n",
    " |-- 2000-04-30: string (nullable = true)\n",
    " |-- 2000-05-31: string (nullable = true)\n",
    " |-- 2000-06-30: string (nullable = true)\n",
    " |-- 2000-07-31: string (nullable = true)\n",
    " |-- 2000-08-31: string (nullable = true)\n",
    " |-- 2000-09-30: string (nullable = true)\n",
    " |-- 2000-10-31: string (nullable = true)\n",
    " |-- 2000-11-30: string (nullable = true)\n",
    " |-- 2000-12-31: string (nullable = true)\n",
    " |-- 2001-01-31: string (nullable = true)\n",
    " |-- 2001-02-28: string (nullable = true)\n",
    " |-- 2001-03-31: string (nullable = true)\n",
    " |-- 2001-04-30: string (nullable = true)\n",
    " |-- 2001-05-31: string (nullable = true)\n",
    " |-- 2001-06-30: string (nullable = true)\n",
    " |-- 2001-07-31: string (nullable = true)\n",
    " |-- 2001-08-31: string (nullable = true)\n",
    " |-- 2001-09-30: string (nullable = true)\n",
    " |-- 2001-10-31: string (nullable = true)\n",
    " |-- 2001-11-30: string (nullable = true)\n",
    " |-- 2001-12-31: string (nullable = true)\n",
    " |-- 2002-01-31: string (nullable = true)\n",
    " |-- 2002-02-28: string (nullable = true)\n",
    " |-- 2002-03-31: string (nullable = true)\n",
    " |-- 2002-04-30: string (nullable = true)\n",
    " |-- 2002-05-31: string (nullable = true)\n",
    " |-- 2002-06-30: string (nullable = true)\n",
    " |-- 2002-07-31: string (nullable = true)\n",
    " |-- 2002-08-31: string (nullable = true)\n",
    " |-- 2002-09-30: string (nullable = true)\n",
    " |-- 2002-10-31: string (nullable = true)\n",
    " |-- 2002-11-30: string (nullable = true)\n",
    " |-- 2002-12-31: string (nullable = true)\n",
    " |-- 2003-01-31: string (nullable = true)\n",
    " |-- 2003-02-28: string (nullable = true)\n",
    " |-- 2003-03-31: string (nullable = true)\n",
    " |-- 2003-04-30: string (nullable = true)\n",
    " |-- 2003-05-31: string (nullable = true)\n",
    " |-- 2003-06-30: string (nullable = true)\n",
    " |-- 2003-07-31: string (nullable = true)\n",
    " |-- 2003-08-31: string (nullable = true)\n",
    " |-- 2003-09-30: string (nullable = true)\n",
    " |-- 2003-10-31: string (nullable = true)\n",
    " |-- 2003-11-30: string (nullable = true)\n",
    " |-- 2003-12-31: string (nullable = true)\n",
    " |-- 2004-01-31: string (nullable = true)\n",
    " |-- 2004-02-29: string (nullable = true)\n",
    " |-- 2004-03-31: string (nullable = true)\n",
    " |-- 2004-04-30: string (nullable = true)\n",
    " |-- 2004-05-31: string (nullable = true)\n",
    " |-- 2004-06-30: string (nullable = true)\n",
    " |-- 2004-07-31: string (nullable = true)\n",
    " |-- 2004-08-31: string (nullable = true)\n",
    " |-- 2004-09-30: string (nullable = true)\n",
    " |-- 2004-10-31: string (nullable = true)\n",
    " |-- 2004-11-30: string (nullable = true)\n",
    " |-- 2004-12-31: string (nullable = true)\n",
    " |-- 2005-01-31: string (nullable = true)\n",
    " |-- 2005-02-28: string (nullable = true)\n",
    " |-- 2005-03-31: string (nullable = true)\n",
    " |-- 2005-04-30: string (nullable = true)\n",
    " |-- 2005-05-31: string (nullable = true)\n",
    " |-- 2005-06-30: string (nullable = true)\n",
    " |-- 2005-07-31: string (nullable = true)\n",
    " |-- 2005-08-31: string (nullable = true)\n",
    " |-- 2005-09-30: string (nullable = true)\n",
    " |-- 2005-10-31: string (nullable = true)\n",
    " |-- 2005-11-30: string (nullable = true)\n",
    " |-- 2005-12-31: string (nullable = true)\n",
    " |-- 2006-01-31: string (nullable = true)\n",
    " |-- 2006-02-28: string (nullable = true)\n",
    " |-- 2006-03-31: string (nullable = true)\n",
    " |-- 2006-04-30: string (nullable = true)\n",
    " |-- 2006-05-31: string (nullable = true)\n",
    " |-- 2006-06-30: string (nullable = true)\n",
    " |-- 2006-07-31: string (nullable = true)\n",
    " |-- 2006-08-31: string (nullable = true)\n",
    " |-- 2006-09-30: string (nullable = true)\n",
    " |-- 2006-10-31: string (nullable = true)\n",
    " |-- 2006-11-30: string (nullable = true)\n",
    " |-- 2006-12-31: string (nullable = true)\n",
    " |-- 2007-01-31: string (nullable = true)\n",
    " |-- 2007-02-28: string (nullable = true)\n",
    " |-- 2007-03-31: string (nullable = true)\n",
    " |-- 2007-04-30: string (nullable = true)\n",
    " |-- 2007-05-31: string (nullable = true)\n",
    " |-- 2007-06-30: string (nullable = true)\n",
    " |-- 2007-07-31: string (nullable = true)\n",
    " |-- 2007-08-31: string (nullable = true)\n",
    " |-- 2007-09-30: string (nullable = true)\n",
    " |-- 2007-10-31: string (nullable = true)\n",
    " |-- 2007-11-30: string (nullable = true)\n",
    " |-- 2007-12-31: string (nullable = true)\n",
    " |-- 2008-01-31: string (nullable = true)\n",
    " |-- 2008-02-29: string (nullable = true)\n",
    " |-- 2008-03-31: string (nullable = true)\n",
    " |-- 2008-04-30: string (nullable = true)\n",
    " |-- 2008-05-31: string (nullable = true)\n",
    " |-- 2008-06-30: string (nullable = true)\n",
    " |-- 2008-07-31: string (nullable = true)\n",
    " |-- 2008-08-31: string (nullable = true)\n",
    " |-- 2008-09-30: string (nullable = true)\n",
    " |-- 2008-10-31: string (nullable = true)\n",
    " |-- 2008-11-30: string (nullable = true)\n",
    " |-- 2008-12-31: string (nullable = true)\n",
    " |-- 2009-01-31: string (nullable = true)\n",
    " |-- 2009-02-28: string (nullable = true)\n",
    " |-- 2009-03-31: string (nullable = true)\n",
    " |-- 2009-04-30: string (nullable = true)\n",
    " |-- 2009-05-31: string (nullable = true)\n",
    " |-- 2009-06-30: string (nullable = true)\n",
    " |-- 2009-07-31: string (nullable = true)\n",
    " |-- 2009-08-31: string (nullable = true)\n",
    " |-- 2009-09-30: string (nullable = true)\n",
    " |-- 2009-10-31: string (nullable = true)\n",
    " |-- 2009-11-30: string (nullable = true)\n",
    " |-- 2009-12-31: string (nullable = true)\n",
    " |-- 2010-01-31: string (nullable = true)\n",
    " |-- 2010-02-28: string (nullable = true)\n",
    " |-- 2010-03-31: string (nullable = true)\n",
    " |-- 2010-04-30: string (nullable = true)\n",
    " |-- 2010-05-31: string (nullable = true)\n",
    " |-- 2010-06-30: string (nullable = true)\n",
    " |-- 2010-07-31: string (nullable = true)\n",
    " |-- 2010-08-31: string (nullable = true)\n",
    " |-- 2010-09-30: string (nullable = true)\n",
    " |-- 2010-10-31: string (nullable = true)\n",
    " |-- 2010-11-30: string (nullable = true)\n",
    " |-- 2010-12-31: string (nullable = true)\n",
    " |-- 2011-01-31: string (nullable = true)\n",
    " |-- 2011-02-28: string (nullable = true)\n",
    " |-- 2011-03-31: string (nullable = true)\n",
    " |-- 2011-04-30: string (nullable = true)\n",
    " |-- 2011-05-31: string (nullable = true)\n",
    " |-- 2011-06-30: string (nullable = true)\n",
    " |-- 2011-07-31: string (nullable = true)\n",
    " |-- 2011-08-31: string (nullable = true)\n",
    " |-- 2011-09-30: string (nullable = true)\n",
    " |-- 2011-10-31: string (nullable = true)\n",
    " |-- 2011-11-30: string (nullable = true)\n",
    " |-- 2011-12-31: string (nullable = true)\n",
    " |-- 2012-01-31: string (nullable = true)\n",
    " |-- 2012-02-29: string (nullable = true)\n",
    " |-- 2012-03-31: string (nullable = true)\n",
    " |-- 2012-04-30: string (nullable = true)\n",
    " |-- 2012-05-31: string (nullable = true)\n",
    " |-- 2012-06-30: string (nullable = true)\n",
    " |-- 2012-07-31: string (nullable = true)\n",
    " |-- 2012-08-31: string (nullable = true)\n",
    " |-- 2012-09-30: string (nullable = true)\n",
    " |-- 2012-10-31: string (nullable = true)\n",
    " |-- 2012-11-30: string (nullable = true)\n",
    " |-- 2012-12-31: string (nullable = true)\n",
    " |-- 2013-01-31: string (nullable = true)\n",
    " |-- 2013-02-28: string (nullable = true)\n",
    " |-- 2013-03-31: string (nullable = true)\n",
    " |-- 2013-04-30: string (nullable = true)\n",
    " |-- 2013-05-31: string (nullable = true)\n",
    " |-- 2013-06-30: string (nullable = true)\n",
    " |-- 2013-07-31: string (nullable = true)\n",
    " |-- 2013-08-31: string (nullable = true)\n",
    " |-- 2013-09-30: string (nullable = true)\n",
    " |-- 2013-10-31: string (nullable = true)\n",
    " |-- 2013-11-30: string (nullable = true)\n",
    " |-- 2013-12-31: string (nullable = true)\n",
    " |-- 2014-01-31: string (nullable = true)\n",
    " |-- 2014-02-28: string (nullable = true)\n",
    " |-- 2014-03-31: string (nullable = true)\n",
    " |-- 2014-04-30: string (nullable = true)\n",
    " |-- 2014-05-31: string (nullable = true)\n",
    " |-- 2014-06-30: string (nullable = true)\n",
    " |-- 2014-07-31: string (nullable = true)\n",
    " |-- 2014-08-31: string (nullable = true)\n",
    " |-- 2014-09-30: string (nullable = true)\n",
    " |-- 2014-10-31: string (nullable = true)\n",
    " |-- 2014-11-30: string (nullable = true)\n",
    " |-- 2014-12-31: string (nullable = true)\n",
    " |-- 2015-01-31: string (nullable = true)\n",
    " |-- 2015-02-28: string (nullable = true)\n",
    " |-- 2015-03-31: string (nullable = true)\n",
    " |-- 2015-04-30: string (nullable = true)\n",
    " |-- 2015-05-31: string (nullable = true)\n",
    " |-- 2015-06-30: string (nullable = true)\n",
    " |-- 2015-07-31: string (nullable = true)\n",
    " |-- 2015-08-31: string (nullable = true)\n",
    " |-- 2015-09-30: string (nullable = true)\n",
    " |-- 2015-10-31: string (nullable = true)\n",
    " |-- 2015-11-30: string (nullable = true)\n",
    " |-- 2015-12-31: string (nullable = true)\n",
    " |-- 2016-01-31: string (nullable = true)\n",
    " |-- 2016-02-29: string (nullable = true)\n",
    " |-- 2016-03-31: string (nullable = true)\n",
    " |-- 2016-04-30: string (nullable = true)\n",
    " |-- 2016-05-31: string (nullable = true)\n",
    " |-- 2016-06-30: string (nullable = true)\n",
    " |-- 2016-07-31: string (nullable = true)\n",
    " |-- 2016-08-31: string (nullable = true)\n",
    " |-- 2016-09-30: string (nullable = true)\n",
    " |-- 2016-10-31: string (nullable = true)\n",
    " |-- 2016-11-30: string (nullable = true)\n",
    " |-- 2016-12-31: string (nullable = true)\n",
    " |-- 2017-01-31: string (nullable = true)\n",
    " |-- 2017-02-28: string (nullable = true)\n",
    " |-- 2017-03-31: string (nullable = true)\n",
    " |-- 2017-04-30: string (nullable = true)\n",
    " |-- 2017-05-31: string (nullable = true)\n",
    " |-- 2017-06-30: string (nullable = true)\n",
    " |-- 2017-07-31: string (nullable = true)\n",
    " |-- 2017-08-31: string (nullable = true)\n",
    " |-- 2017-09-30: string (nullable = true)\n",
    " |-- 2017-10-31: string (nullable = true)\n",
    " |-- 2017-11-30: string (nullable = true)\n",
    " |-- 2017-12-31: string (nullable = true)\n",
    " |-- 2018-01-31: string (nullable = true)\n",
    " |-- 2018-02-28: string (nullable = true)\n",
    " |-- 2018-03-31: string (nullable = true)\n",
    " |-- 2018-04-30: string (nullable = true)\n",
    " |-- 2018-05-31: string (nullable = true)\n",
    " |-- 2018-06-30: string (nullable = true)\n",
    " |-- 2018-07-31: string (nullable = true)\n",
    " |-- 2018-08-31: string (nullable = true)\n",
    " |-- 2018-09-30: string (nullable = true)\n",
    " |-- 2018-10-31: string (nullable = true)\n",
    " |-- 2018-11-30: string (nullable = true)\n",
    " |-- 2018-12-31: string (nullable = true)\n",
    " |-- 2019-01-31: string (nullable = true)\n",
    " |-- 2019-02-28: string (nullable = true)\n",
    " |-- 2019-03-31: string (nullable = true)\n",
    " |-- 2019-04-30: string (nullable = true)\n",
    " |-- 2019-05-31: string (nullable = true)\n",
    " |-- 2019-06-30: string (nullable = true)\n",
    " |-- 2019-07-31: string (nullable = true)\n",
    " |-- 2019-08-31: string (nullable = true)\n",
    " |-- 2019-09-30: string (nullable = true)\n",
    " |-- 2019-10-31: string (nullable = true)\n",
    " |-- 2019-11-30: string (nullable = true)\n",
    " |-- 2019-12-31: string (nullable = true)\n",
    " |-- 2020-01-31: string (nullable = true)\n",
    " |-- 2020-02-29: string (nullable = true)\n",
    " |-- 2020-03-31: string (nullable = true)\n",
    " |-- 2020-04-30: string (nullable = true)\n",
    " |-- 2020-05-31: string (nullable = true)\n",
    " |-- 2020-06-30: string (nullable = true)\n",
    " |-- 2020-07-31: string (nullable = true)\n",
    " |-- 2020-08-31: string (nullable = true)\n",
    " |-- 2020-09-30: string (nullable = true)\n",
    " |-- 2020-10-31: string (nullable = true)\n",
    " |-- 2020-11-30: string (nullable = true)\n",
    " |-- 2020-12-31: string (nullable = true)\n",
    " |-- 2021-01-31: string (nullable = true)\n",
    " |-- 2021-02-28: string (nullable = true)\n",
    " |-- 2021-03-31: string (nullable = true)\n",
    " |-- 2021-04-30: string (nullable = true)\n",
    " |-- 2021-05-31: string (nullable = true)\n",
    " |-- 2021-06-30: string (nullable = true)\n",
    " |-- 2021-07-31: string (nullable = true)\n",
    " |-- 2021-08-31: string (nullable = true)\n",
    " |-- 2021-09-30: string (nullable = true)\n",
    " |-- 2021-10-31: string (nullable = true)\n",
    " |-- 2021-11-30: string (nullable = true)\n",
    " |-- 2021-12-31: string (nullable = true)\n",
    " |-- 2022-01-31: string (nullable = true)\n",
    " |-- 2022-02-28: string (nullable = true)\n",
    " |-- 2022-03-31: string (nullable = true)\n",
    " |-- 2022-04-30: string (nullable = true)\n",
    " |-- 2022-05-31: string (nullable = true)\n",
    " |-- 2022-06-30: string (nullable = true)\n",
    " |-- 2022-07-31: string (nullable = true)\n",
    " |-- 2022-08-31: string (nullable = true)\n",
    " |-- 2022-09-30: string (nullable = true)\n",
    " |-- 2022-10-31: string (nullable = true)\n",
    " |-- 2022-11-30: string (nullable = true)\n",
    " |-- 2022-12-31: string (nullable = true)\n",
    " |-- 2023-01-31: string (nullable = true)\n",
    " |-- 2023-02-28: string (nullable = true)\n",
    " |-- 2023-03-31: string (nullable = true)\n",
    " |-- 2023-04-30: string (nullable = true)\n",
    " |-- 2023-05-31: string (nullable = true)\n",
    " |-- 2023-06-30: string (nullable = true)\n",
    " |-- 2023-07-31: string (nullable = true)\n",
    " |-- 2023-08-31: string (nullable = true)\n",
    " |-- 2023-09-30: string (nullable = true)\n",
    " |-- 2023-10-31: string (nullable = true)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18fbc8e9",
   "metadata": {},
   "source": [
    "So our Zillow data has information about the county and a column for each monthly home value index going back to the year 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8dc120fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "sedona.sql(\"\"\"\n",
    "SELECT RegionName,StateName, State, Metro, StateCodeFIPS, MunicipalCodeFIPS, `2000-01-31` FROM zhvi LIMIT 10\n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d37ae6eb",
   "metadata": {},
   "source": [
    "```\n",
    "+------------------+---------+-----+----------+-------------+-----------------+-------------+\n",
    "|        RegionName|StateName|State|     Metro|StateCodeFIPS|MunicipalCodeFIPS|   2000-01-31|\n",
    "+------------------+---------+-----+----------+-------------+-----------------+-------------+\n",
    "|Los Angeles County|       CA|   CA|Los Ang...|           06|              037| 205974.69363|\n",
    "|       Cook County|       IL|   IL|Chicago...|           17|              031| 145499.51956|\n",
    "|     Harris County|       TX|   TX|Houston...|           48|              201|108350.203359|\n",
    "|   Maricopa County|       AZ|   AZ|Phoenix...|           04|              013|143352.154097|\n",
    "|  San Diego County|       CA|   CA|San Die...|           06|              073| 214883.41007|\n",
    "|     Orange County|       CA|   CA|Los Ang...|           06|              059|249870.608658|\n",
    "|      Kings County|       NY|   NY|New Yor...|           36|              047|200000.033597|\n",
    "| Miami-Dade County|       FL|   FL|Miami-F...|           12|              086| 119546.52618|\n",
    "|     Dallas County|       TX|   TX|Dallas-...|           48|              113| 94256.230613|\n",
    "|  Riverside County|       CA|   CA|Riversi...|           06|              065| 149474.98732|\n",
    "+------------------+---------+-----+----------+-------------+-----------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3df7d92",
   "metadata": {},
   "source": [
    "## Joining ZHVI And County Geometries\n",
    "\n",
    "So we now have two tables `zhvi` and `counties` - let's join them together so we can plot current home value per county. To do that we'll use the FIPS code which uniquely identifes each county. The `zhvi` table breaks the FIPS code out into the state and municipal components so we'll need to combine then so we can join against the `counties` table.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf276157",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhvi_county_df = sedona.sql(\"\"\"\n",
    "SELECT CAST(zhvi.`2023-10-31` AS float) AS value, zhvi.RegionName As name, counties.geometry\n",
    "FROM zhvi\n",
    "JOIN counties\n",
    "ON CONCAT('US', zhvi.StateCodeFIPS, zhvi.MunicipalCodeFIPS) = counties.FIPS\n",
    "WHERE NOT zhvi.State IN ('HI', 'AK')\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10e6023f",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhvi_county_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aff97050",
   "metadata": {},
   "source": [
    "```\n",
    "+---------+-------------------+--------------------+\n",
    "|    value|               name|            geometry|\n",
    "+---------+-------------------+--------------------+\n",
    "|582171.56|     Whatcom County|MULTIPOLYGON (((-...|\n",
    "|300434.03|    Okanogan County|POLYGON ((-120.85...|\n",
    "| 279648.9|       Ferry County|POLYGON ((-118.83...|\n",
    "|365007.16|     Stevens County|POLYGON ((-118.21...|\n",
    "|364851.38|Pend Oreille County|POLYGON ((-117.42...|\n",
    "+---------+-------------------+--------------------+\n",
    "only showing top 5 rows\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d62d4c0",
   "metadata": {},
   "source": [
    "Now that we have our home value index and the county geometry in a single DataFrame we can use a choropleth map to visualize the data geographically. The `SedonaPyDeck` package supports creating choropleths with the `create_choropleth_map` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc1dc953",
   "metadata": {},
   "outputs": [],
   "source": [
    "SedonaPyDeck.create_choropleth_map(zhvi_county_df,plot_col=\"value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0950ea5",
   "metadata": {},
   "source": [
    "![](https://raw.githubusercontent.com/johnymontana/30-day-map-challenge/625b4e474e83430a3f146f8017750a83533dcab2/img/choropleth/pydeck.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4fd4ad00",
   "metadata": {},
   "source": [
    "We can also use matplotlib to create a choropleth by converting our `zhvi_county_df` Sedona DataFrame to a GeoDataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5abeea9",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhvi_gdf = geopandas.GeoDataFrame(zhvi_county_df.toPandas(), geometry=\"geometry\")\n",
    "zhvi_gdf.crs = {\"init\": \"epsg:4326\"}\n",
    "zhvi_gdf.to_crs(epsg=6350)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cacdc3c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = zhvi_gdf.plot(\n",
    "    column=\"value\",\n",
    "    scheme=\"JenksCaspall\",\n",
    "    cmap=\"YlGn\",\n",
    "    legend=True,\n",
    "    legend_kwds={\"title\": \"Median Home Value($)\", \"fmt\": \"{:.0f}\", \"loc\": \"lower right\"},\n",
    "    missing_kwds={\n",
    "        \"color\": \"lightgrey\",\n",
    "        \"edgecolor\": \"red\",\n",
    "        \"hatch\": \"///\",\n",
    "        \"label\": \"Missing values\"\n",
    "    },\n",
    "    figsize=(12,9)\n",
    ")\n",
    "\n",
    "ax.set_axis_off()\n",
    "ax.set_title(\"Median Home Value By County, Oct 2023\")\n",
    "ax.annotate(\"Data from Zillow, 2023 ZHVI\",(-125,25))\n",
    "\n",
    "plt.savefig(\"zhvi.png\",dpi=300)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7b8160c",
   "metadata": {},
   "source": [
    "![](https://raw.githubusercontent.com/johnymontana/30-day-map-challenge/625b4e474e83430a3f146f8017750a83533dcab2/img/choropleth/zhvi.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa0965e3",
   "metadata": {},
   "source": [
    "## Visualizing Change In Home Value\n",
    "\n",
    "So far we've plotted the current home value, now let's calculate the change in home value over the last 5 years and visualize the spatial distribution.\n",
    "\n",
    "In our SQL query let's calculate the percent change in home value from 2018 to 2023. We'll also compute the centroid of each county so we can annotate the map.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "417c4792",
   "metadata": {},
   "outputs": [],
   "source": [
    "delta_county_df = sedona.sql(\"\"\"\n",
    "SELECT \n",
    "  ((CAST(zhvi.`2023-10-31` AS float) - CAST(zhvi.`2018-10-31` AS float)) / (CAST(zhvi.`2018-10-31` AS float)) * 100 )  AS delta,\n",
    "  zhvi.RegionName As name, zhvi.Statename AS state,counties.geometry, ST_Centroid(counties.geometry) AS centroid\n",
    "FROM zhvi\n",
    "JOIN counties\n",
    "ON CONCAT('US', zhvi.StateCodeFIPS, zhvi.MunicipalCodeFIPS) = counties.FIPS\n",
    "WHERE NOT zhvi.State IN ('HI', 'AK')\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "574ebfd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "delta_county_df.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cea55b2d",
   "metadata": {},
   "source": [
    "```\n",
    "+-----------------+-------------------+-----+--------------------+--------------------+\n",
    "|            delta|               name|state|            geometry|            centroid|\n",
    "+-----------------+-------------------+-----+--------------------+--------------------+\n",
    "|52.68140223225829|     Whatcom County|   WA|MULTIPOLYGON (((-...|POINT (-121.71238...|\n",
    "|55.31723313387478|    Okanogan County|   WA|POLYGON ((-120.85...|POINT (-119.73730...|\n",
    "|32.94520356645385|       Ferry County|   WA|POLYGON ((-118.83...|POINT (-118.51643...|\n",
    "|66.86739887826734|     Stevens County|   WA|POLYGON ((-118.21...|POINT (-117.85278...|\n",
    "|66.78068227229824|Pend Oreille County|   WA|POLYGON ((-117.42...|POINT (-117.27525...|\n",
    "+-----------------+-------------------+-----+--------------------+--------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a80d6799",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We'll convert to a GeoDataFrame\n",
    "\n",
    "delta_gdf = geopandas.GeoDataFrame(delta_county_df.toPandas(), geometry=\"geometry\")\n",
    "delta_gdf.crs = {\"init\": \"epsg:4326\"}\n",
    "delta_gdf.to_crs(epsg=6350)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90a402bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's find the counties with the most extreme changes in home value\n",
    "\n",
    "delta_gdf.iloc[delta_gdf['delta'].idxmax()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3beca89a",
   "metadata": {},
   "source": [
    "```\n",
    "delta                                              169.532379\n",
    "name                                          Petersburg City\n",
    "state                                                      VA\n",
    "geometry    POLYGON ((-77.40366881899992 37.23746578695217...\n",
    "centroid         POINT (-77.39189306391864 37.20426989798888)\n",
    "Name: 2937, dtype: object\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "391d9f7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "delta_gdf.iloc[delta_gdf['delta'].idxmin()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "917201c6",
   "metadata": {},
   "source": [
    "```\n",
    "delta                                              -44.794506\n",
    "name                                         Claiborne Parish\n",
    "state                                                      LA\n",
    "geometry    POLYGON ((-93.23859537841794 33.01169686180804...\n",
    "centroid         POINT (-92.99522452214245 32.81998668151367)\n",
    "Name: 944, dtype: object\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "611fb864",
   "metadata": {},
   "outputs": [],
   "source": [
    "ax = delta_gdf.plot(\n",
    "    column=\"delta\",\n",
    "    scheme=\"User_Defined\",\n",
    "    cmap=\"RdYlGn\",\n",
    "    legend=True,\n",
    "    legend_kwds={\"title\": \"Median Home Value % Change\", \"loc\": \"lower right\"},\n",
    "    classification_kwds={\"bins\":[0,20,40,60,80,100,170]},\n",
    "    missing_kwds={\n",
    "        \"color\": \"lightgrey\",\n",
    "        #\"edgecolor\": \"red\",\n",
    "        #\"hatch\": \"///\",\n",
    "        \"label\": \"Missing values\"\n",
    "    },\n",
    "    figsize=(12,9)\n",
    ")\n",
    "\n",
    "ax.set_axis_off()\n",
    "ax.set_title(\"Median Home Value Percent Change By County, 2018-2023\")\n",
    "ax.annotate(\"Data from Zillow's ZHVI\",(-125,25))\n",
    "\n",
    "ax.annotate('Petersburg City, VA +169.5%',\n",
    "            xy=(-77.39189306391864, 37.20426989798888), xycoords='data',\n",
    "            xytext=(100, 10), textcoords='offset points',\n",
    "            arrowprops=dict(facecolor='black', shrink=0.05),\n",
    "            horizontalalignment='center', verticalalignment='bottom')\n",
    "\n",
    "ax.annotate('Claiborne Parish, LA -44.8%',\n",
    "            xy=(-92.99522452214245, 32.81998668151367), xycoords='data',\n",
    "            xytext=(50, -110), textcoords='offset points',\n",
    "            arrowprops=dict(facecolor='black', shrink=0.05),\n",
    "            horizontalalignment='center', verticalalignment='bottom')\n",
    "\n",
    "\n",
    "plt.savefig(\"delta.png\", dpi=300)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0033d6b7",
   "metadata": {},
   "source": [
    "![](https://raw.githubusercontent.com/johnymontana/30-day-map-challenge/625b4e474e83430a3f146f8017750a83533dcab2/img/choropleth/delta.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00107499",
   "metadata": {},
   "source": [
    "## Resources\n",
    "\n",
    "In addition to the data sources linked at the beginning of this notebook, I found the following resources helpful when creating this map:\n",
    "\n",
    "* [Choropleth Mapping chapter](https://geographicdata.science/book/notebooks/05_choropleth.html) from Geographic Data Science With Python book\n",
    "* GeoPandas [mapping and plotting tools documentation](https://geopandas.org/en/v0.14.1/docs/user_guide/mapping.html)\n",
    "* Matplotlib example of [annotating plots](https://matplotlib.org/stable/gallery/text_labels_and_annotations/annotation_demo.html)\n",
    "* StackOverlow answer on [using manual breakpoints for matplotlib classes](https://stackoverflow.com/a/56695238)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1639af23",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}