fuel_moisture_rf.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Predict Live Fuel Moisture with Random Forests Regression"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "by Dr. Michael Flaxman, HeavyAI"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Background - Fuel moisture is perhaps the most critical and difficult to measure parameter for fize hazard and fire spread modeling.  Unlike weather and topography which are directly observed by modern sensors, fuel moisture must be inferred from very limited sampling data which is mostly manually gathered. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook starts by replicating the results of a recent paper on live fuel moisture modeling by Rao et. all (2020)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rao, K., Williams, A.P., Fortin, J. & Konings, A.G. (2020). SAR-enhanced mapping of live fuel moisture content. Remote Sens. Environ., 245."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It then tests various extensions of the modeling approach, first with hyperparameter tuning and model selection, then by upgrading specific data sets which the modeling shows to be significant and which were relatively low resolution in the original work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    import heavyai\n",
    "    print(f\"Using heavyai library version {heavyai.__version__}\")\n",
    "except:\n",
    "    !pip install heavyai\n",
    "    import heavyai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    import geopandas as gpd\n",
    "    print(f\"Using geopandas library version {gpd.__version__}\")\n",
    "except:\n",
    "    !pip install geopandas \n",
    "    import geopandas as gpd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import geopandas as gpd\n",
    "import requests\n",
    "from tqdm import tqdm # optional, for progress bars"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Setup a database connection, here to the docker service name of the host - this may require adjustments to match your local setup."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con = heavyai.connect(host='heavyaiserver', user='admin', password='HyperInteractive', dbname='heavyai')\n",
    "con"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Path for tile storage (must be writeable by notebook and accessible in HeavyDB whitelist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = '/tmp'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get Cleaned Data from HeavyAI"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For simplicity, this should download a copy of the training data copied from Radiant MLHub "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://omnisci-flaxman.s3.amazonaws.com/su_sar_moisture_content.dump.gz?AWSAccessKeyId=AKIAIMWS6OXQPR6OZGRA&Signature=klTCXU8sQT8y375xtxnOumiYvqM%3D&Expires=1846796453"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List and Download Training Imagery and Label Datasets from Radiant MLHub"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is optional, since data can also be manually downloaded from that web site"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    from radiant_mlhub import Dataset\n",
    "except:\n",
    "    !pip install radiant_mlhub\n",
    "    from radiant_mlhub import Dataset    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If used, you need to obtain a free API key from Radiant MLHub"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Put this into a \".env\" file in your main jupyter directory with the contents:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "RAD_EARTH_KEY='your radiant eath key here'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install python-dotenv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from dotenv.main import load_dotenv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This tells the library above to load environment variables from you .env file, including RAD_EARTH_KEY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "load_dotenv()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If correctly set, this should get the metadata for this dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = Dataset.fetch('su_sar_moisture_content_main', api_key=RAD_EARTH_KEY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset.citation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Actual Data Download via API or file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "full_data_path = f\"{path}/su_sar_moisture_content_main.tar.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!ls -l {full_data_path}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not os.path.exists(full_data_path):\n",
    "    print(f\"Downloading using Radiant Hub API to {full_data_path}\")\n",
    "    dataset = Dataset.fetch('su_sar_moisture_content_main', api_key=RAD_EARTH_K)\n",
    "    dataset.download(output_dir=full_data_path)\n",
    "else:\n",
    "    print(f\"Data already downloaded to {full_data_path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "extracted = f\"{path}/su_sar_moisture_content\"\n",
    "print(f\"TAR extract will be called {extracted}\")\n",
    "if not os.path.exists(extracted):\n",
    "    print(f\"Extracting data from gzipped tar archive\")\n",
    "    !tar xvfz {full_data_path}\n",
    "else:\n",
    "    print(f\"TAR folder already extracted to {extracted}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#if not \"su_sar_moisture_content\" in ibis.list_tables('sar'):\n",
    "for f in tqdm(glob.glob(f\"{extracted}/su_sar_moisture_content*/labels.geojson\")):\n",
    "    q = f\"COPY su_sar_moisture_content FROM '{f}' WITH (source_type='geo_file')\"\n",
    "    try:\n",
    "        con.execute(q)\n",
    "    except:\n",
    "        print(f\"Problem loading soil moisture file {f} to database using: \\n {q}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from_file = False\n",
    "\n",
    "if from_file:\n",
    "    print(\"Go to radiant web site and download first\")\n",
    "    # https://mlhub.earth/data/su_sar_moisture_content_main\n",
    "    print('then extract from local gzipped tar archive')\n",
    "    #   !tar xvfz /freenas-home/datasets/su_sar_moisture_content.tar.gz\n",
    "    print('then move to a folder accessible to heavydb')\n",
    "    # !mv su_sar_moisture_content {path}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Cleanup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Presuming you've loaded data above into a table called 'su_sar_moisture_content'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t = ibis.table(\"su_sar_moisture_content\")\n",
    "# get column names as schema object\n",
    "s = t.schema()\n",
    "# get column names as a list\n",
    "sl = list(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t.count().execute()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First replace bad soils nodata values of -999.0 with nulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(\"UPDATE su_sar_moisture_content SET siltt = NULL WHERE siltt = -999.0\")\n",
    "con.execute(\"UPDATE su_sar_moisture_content SET sandt = NULL WHERE sandt = -999.0\")\n",
    "con.execute(\"UPDATE su_sar_moisture_content SET clayt = NULL WHERE clayt = -999.0\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Fix non-ISO dates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dates are in month/day/year format, but we need them in ISO format to type convert properly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, for modeling purposes the day of year is more useful that the date per se"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = '''\n",
    "select\n",
    "  date_,\n",
    "  try_cast(\n",
    "    '20' || split_part(date_, '/', 3) || '-' || split_part(date_, '/', 1) || '-' || split_part(date_, '/', 2) as date\n",
    "  ) as obs_date,\n",
    "  extract(doy from try_cast(\n",
    "    '20' || split_part(date_, '/', 3) || '-' || split_part(date_, '/', 1) || '-' || split_part(date_, '/', 2) as date\n",
    "  )) as obs_doy \n",
    "from\n",
    "    su_sar_moisture_content '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q.replace('\\n','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(q, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(\"ALTER TABLE su_sar_moisture_content ADD COLUMN obs_date TIMESTAMP\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(\"ALTER TABLE su_sar_moisture_content ADD COLUMN obs_doy SMALLINT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = '''\n",
    "update su_sar_moisture_content \n",
    "  set obs_date = \n",
    "  try_cast(\n",
    "    '20' || split_part(date_, '/', 3) || '-' || split_part(date_, '/', 1) || '-' || split_part(date_, '/', 2) as date\n",
    "  )\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q.replace('\\n','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = '''\n",
    "update su_sar_moisture_content \n",
    "  set obs_doy = \n",
    "  extract(doy from try_cast(\n",
    "    '20' || split_part(date_, '/', 3) || '-' || split_part(date_, '/', 1) || '-' || split_part(date_, '/', 2) as date\n",
    "  ))\n",
    "  '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q.replace('\\n','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Feature Enrichment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Emrich with H3 Codes for Fast GeoJoins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not 'hex08' in sl:\n",
    "    con.execute(\"ALTER TABLE su_sar_moisture_content ADD COLUMN hex08 BIGINT\")\n",
    "    q = 'update su_sar_moisture_content set hex08 = geoToH3(ST_X(geom),ST_Y(geom),8)'\n",
    "    con.execute(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute day of year derivative values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next compute derived variables from Julian date that wrap cleanly at calendar year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we normalize DOY to 2 * pi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not 'sin_doy' in sl:\n",
    "    con.execute(\"ALTER TABLE su_sar_moisture_content ADD COLUMN sin_doy FLOAT\")\n",
    "    norm_doy = \"2 * pi * obs_doy / 365.25\"\n",
    "    q = f'update su_sar_moisture_content set cos_doy = cos({norm_doy})'\n",
    "    con.execute(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not 'cos_doy' in sl:\n",
    "    con.execute(\"ALTER TABLE su_sar_moisture_content ADD COLUMN cos_doy FLOAT\")\n",
    "    norm_doy = \"2 * pi * obs_doy / 365.25\"\n",
    "    q = f'update su_sar_moisture_content set cos_doy = cos({norm_doy})'\n",
    "    con.execute(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute Solar Zenith at the DOY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a reasonable proxy to solar exposure available with just DOY and latitude"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "θ ≈ 90 - ϕ + 23.5 × sin[(360 × (284 + n)) / 365]"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Where:\n",
    "\n",
    "θ is the solar zenith angle in degrees.\n",
    "ϕ is the latitude of the location in degrees.\n",
    "n is the day of the year, with January 1st as day 1."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### A more-direct single variable - solar zenith"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(\"ALTER TABLE su_sar_moisture_content ADD COLUMN zenith FLOAT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = '''\n",
    "UPDATE su_sar_moisture_content \n",
    "SET zenith = 90 - ST_Y(geom) + 23.5 * sin((360 * (284 + obs_doy)) / 365.0)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.execute(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Update schema variables with feature enrichments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tn = \"su_sar_moisture_content\"\n",
    "tn in con.get_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_column_details = con.get_table_details('su_sar_moisture_content')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(table_column_details)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_names_list = list(df['name'])\n",
    "column_types_list = list(df['type'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df[\"type\"].values[df['name'] == 'site'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t = ibis.table(tn)\n",
    "# get column names as schema object\n",
    "s = t.schema()\n",
    "# get column names as a list\n",
    "sl = list(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Build a Model of LFM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Make a function to reorganize columns to meet 7.0 HeavyDB requirement (set to relax at 7.1).  This requires the column to be predicted first, followed by categorical columns and then continuous ones."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In general, we always want to get rid of extraneous columns.  Even if they are static and otherwise harmless they slow down model creation and confuse interpretation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specific to this dataset, the docs indicate that a set of duplicated static columns were added for LSTM modeling.  Since that is not needed here, we'll exlcude them as well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "blacklist = [ 'site', 'date_', 'obs_date', 'geom', 'geom0', 'Shape_Leng', 'Shape_Area', 'OBJECTID', 'hex08', 'hex08r6']\n",
    "static_cols = ['elevation', 'slope', 'aspect', 'sand', 'silt', 'clay', 'canopy_heightt', 'forest_cover']\n",
    "for c in column_names_list:\n",
    "    for sc in static_cols:\n",
    "        if (sc in c and \"t1\" in c) or (sc in c and \"t2\" in c) or (sc in c and \"t3\" in c):\n",
    "            blacklist.append(c)\n",
    "#blacklist"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This isn't required except for HeavyDB version 7.0.0 (all subsequent releases don't require column ordering)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def organize_predictor_cols(df):\n",
    "    valid_cols = []\n",
    "    cat_cols = []\n",
    "    cont_cols = []\n",
    "    for c in column_names_list:\n",
    "        if c not in blacklist:\n",
    "            col_type = df[\"type\"].values[df['name'] == c] #str(s[c])\n",
    "            if ('DOUBLE' in col_type) or  ('FLOAT' in col_type) or ('SMALLINT' in col_type):\n",
    "                cont_cols.append(c)\n",
    "            elif 'string' in col_type:\n",
    "                cat_cols.append(c)\n",
    "            else:\n",
    "                print(f\"Skipping unrecognized column type: {col_type}\")\n",
    "    varlist = column_names_list[0] + ', '\n",
    "    if len(cat_cols) > 0:\n",
    "        varlist += \", \".join(cat_cols) + ', '\n",
    "    if len(cont_cols) > 0:\n",
    "        varlist += \", \".join(cont_cols[1:])\n",
    "    else:\n",
    "        varlist += \"1 \" # dummy continuous since at least one required in 7.0\n",
    "    return(varlist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "varlist = organize_predictor_cols(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(varlist.split(','))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Random Forests Regression"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Simplest form:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = f'''\n",
    "CREATE OR REPLACE MODEL su_sar_rf OF TYPE random_forest_reg AS \n",
    "SELECT\n",
    "  {varlist}   \n",
    "FROM\n",
    "  {tn} \n",
    "WITH \n",
    "  (EVAL_FRACTION=0.20)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q.replace(\"\\n\",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "try:\n",
    "    con.execute(q)\n",
    "except:\n",
    "    print(f\"Failed to create model su_sar_rf with query\\n {q}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Base model evaluation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(\"EVALUATE MODEL su_sar_rf\", con)\n",
    "accuracy = df['r2'][0]\n",
    "print(f\"su_sar_rf r2 = {round(accuracy,3)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, withholding 20% of samples for evaluation, and increasing number of trees from 10 (default) to 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = f'''\n",
    "CREATE OR REPLACE MODEL su_sar_rf OF TYPE random_forest_reg AS \n",
    "SELECT\n",
    "  {varlist}   \n",
    "FROM\n",
    "  {tn} \n",
    "WITH \n",
    "  (EVAL_FRACTION=0.20, NUM_TREES=50)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "try:\n",
    "    con.execute(q)\n",
    "except:\n",
    "    print(f\"Failed to create model su_sar_rf with query\\n {q}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#con.execute(\"SHOW MODEL DETAILS su_sar_rf\")\n",
    "df = pd.read_sql_query(\"SHOW MODEL DETAILS su_sar_rf\", con)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(\"EVALUATE MODEL su_sar_rf\", con)\n",
    "accuracy = df['r2'][0]\n",
    "print(f\"su_sar_rf r2 = {round(accuracy,3)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Compute Feature Importance Scores"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q = \"SELECT * FROM TABLE(random_forest_reg_var_importance('su_sar_rf')) \"\n",
    "q += \"ORDER BY importance_score DESC\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql_query(q, con)\n",
    "df.head(15)"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}