{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b313a218-4778-4d5b-9036-f0370d4212a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "conn = ibis.duckdb.connect(extensions=[\"spatial\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "fbbd28ea-f1e3-4f3d-ab99-a2ae9b04f3d4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "74"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state_boundaries = \"https://data.source.coop/cboettig/us-boundaries/us-state-territory.parquet\"\n",
    "county_boundaries = \"https://data.source.coop/cboettig/us-boundaries/us-county.parquet\"\n",
    "\n",
    "states = conn.read_parquet(state_boundaries).rename(state_id = \"STUSPS\", state = \"NAME\")\n",
    "county = conn.read_parquet(county_boundaries).rename(county = \"NAMELSAD\", state = \"STATE_NAME\")\n",
    "\n",
    "votes = conn.read_csv(\"measures.csv\")\n",
    "#votes.execute()\n",
    "votes.count().execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "f766d5ea-1a1b-4113-b985-f2014c8935c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Jurisdiction Type</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>County</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Municipal</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Special District</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>State</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Jurisdiction Type   n\n",
       "0            County  23\n",
       "1         Municipal  48\n",
       "2  Special District   1\n",
       "3             State   2"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#votes.group_by(_.State).agg(n= _.count()).execute()\n",
    "votes.group_by(_[\"Jurisdiction Type\"]).agg(n= _.count()).execute()\n",
    "#states.head().execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "295b0b78-9c2a-436d-aa9c-34899858e637",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f05ac36b0a714711bf4dfaffa490e77a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "vote = (votes\n",
    "       .filter(_[\"Jurisdiction Type\"] == \"County\")\n",
    "       .rename(county = \"Jurisdiction Name\", state_id = \"State\")\n",
    "       .mutate(key = _.county + ibis.literal('-') + _.state_id)\n",
    "       .rename(amount = 'Conservation Funds at Stake', yes = '% Yes')\n",
    "       .mutate(amount_n=_.amount.replace('$', '').replace(',', '').cast('float'))\n",
    "       .select('key', 'amount_n', 'amount', 'Status', 'yes')\n",
    "       )\n",
    "df = (county\n",
    "      .join(states.select(\"state\", \"state_id\"), \"state\")\n",
    "      .mutate(key = _.county + ibis.literal('-') + _.state_id)\n",
    "      .select('key', 'geometry')\n",
    "      .right_join(vote, \"key\")\n",
    "     # .drop('key_right')\n",
    "     )\n",
    "# 20 of 23 counties have matches\n",
    "gdf = df.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "fa397626-6e94-4ab9-a3bb-2bcbd14e8d40",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "88b0a65f5667445aae3f92aee70c9109",
       "version_major": 2,
       "version_minor": 1
      },
      "text/plain": [
       "Map(height='600px', map_options={'bearing': 0, 'center': (0, 20), 'pitch': 0, 'style': 'https://tiles.openfree…"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import leafmap.maplibregl as leafmap\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "\n",
    "outcome = [\n",
    "      'match',\n",
    "      ['get', 'Status'], \n",
    "      \"Pass\", '#2E865F',\n",
    "      \"Fail\", '#FF3300', \n",
    "      '#ccc'\n",
    "    ]\n",
    "paint = {\"fill-extrusion-color\": outcome, \n",
    "         \"fill-extrusion-opacity\": 0.7,\n",
    "         \"fill-extrusion-height\": [\"*\", [\"get\", \"amount_n\"], .001],\n",
    "        }\n",
    "\n",
    "#m.add_geojson(\"vote.geojson\")\n",
    "m.add_gdf(gdf, \"fill-extrusion\", paint = paint)\n",
    "m"
   ]
  }
 ],
 "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.11.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}