{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "b313a218-4778-4d5b-9036-f0370d4212a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "from ibis import _\n",
    "import streamlit as st\n",
    "\n",
    "conn = ibis.duckdb.connect(extensions=[\"spatial\"])\n",
    "\n",
    "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",
    "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(\"landvote.csv\")\n",
    "votes.count().execute()\n",
    "\n",
    "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",
    "       .mutate(log_amount=_.amount_n.log())\n",
    "       .mutate(year=_['Date'].year().cast('int32'))\n",
    "       .select('key', 'Status', 'yes', 'year', 'amount', 'log_amount', )\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",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "fa397626-6e94-4ab9-a3bb-2bcbd14e8d40",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e447c30d35374eb4a1d0a28b83b78159",
       "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"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b325fe101b644a36a48fb1ec7bc0fcd2",
       "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": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import leafmap.maplibregl as leafmap\n",
    "m = leafmap.Map(style=\"positron\")\n",
    "\n",
    "\n",
    "gdf = df.filter(_.year==2022).execute()\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\", \"log_amount\"], 5000],\n",
    "        }\n",
    "\n",
    "#m.add_geojson(\"vote.geojson\")\n",
    "m.add_gdf(gdf, \"fill-extrusion\", paint = paint)\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "4f46a96e-e8ca-4c38-9164-08ab159e3832",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "19.90228528487658"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np \n",
    "np.log(440000000)"
   ]
  }
 ],
 "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
}