update_ibi_scores_table.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Update IBI Scores Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from arcgis.gis import GIS\n",
    "from arcgis import features\n",
    "from arcgis.features import FeatureLayerCollection\n",
    "import ipywidgets as widgets\n",
    "import pandas as pd\n",
    "import io\n",
    "import warnings\n",
    "\n",
    "def update_table(b):    \n",
    "    with output:\n",
    "        # Clear any previous output\n",
    "        output.clear_output()\n",
    "        \n",
    "        # Initialize progress bar\n",
    "        progress = widgets.IntProgress(value=0, min=0, max=4, step=1, description='Progress:')\n",
    "        display(progress)\n",
    "        \n",
    "        # Check if file is uploaded\n",
    "        if uploader.value:\n",
    "            \n",
    "            try:\n",
    "                # Read in uploaded file as a csv\n",
    "                uploaded_file = next(iter(uploader.value.values()))\n",
    "                csv = io.BytesIO(uploaded_file['content'])\n",
    "\n",
    "                # Read uploaded file into a Pandas DataFrame\n",
    "                df = pd.read_csv(csv)\n",
    "                \n",
    "                # Drop null values (+ last 5 Salesforce lines)\n",
    "                df.dropna(subset=['Site Name'], inplace=True)\n",
    "                df['Season'] = df['Season'].str.split('-').str[1].str.capitalize()\n",
    "                \n",
    "                # Most recent ibi scores for each site\n",
    "                recent_ibi = df.copy()\n",
    "                recent_ibi['Session_Date'] = pd.to_datetime(recent_ibi['Mon_Date_Time'])\n",
    "                recent_ibi = recent_ibi[recent_ibi.groupby('WQMSite ID')['Session_Date'].transform('max') == recent_ibi['Session_Date']]\n",
    "                recent_ibi = recent_ibi.rename(columns={'WQMSite ID': 'WQMSite_ID', 'Avg_IBI_Score': 'Index_of_Biological_Integrity__'})\n",
    "                recent_ibi = recent_ibi[['WQMSite_ID', 'Index_of_Biological_Integrity__', 'Mon_Date_Time', 'Season']]\n",
    "\n",
    "                # Grab Sites layer from ArcGIS\n",
    "                items = gis.content.search(query='title:Sites', item_type='Feature Service')\n",
    "                for item in items:\n",
    "                    if item.title == \"Sites\":\n",
    "                        collection = FeatureLayerCollection.fromitem(item)\n",
    "                        sites_df = collection.layers[0].query().df\n",
    "\n",
    "                        # Merge the DataFrames on 'WQMSite_ID', keeping all rows from sites_df\n",
    "                        sites_df = pd.merge(sites_df.drop(columns=['Index_of_Biological_Integrity__', 'Mon_Date_Time', 'Season']), recent_ibi, how='left', on='WQMSite_ID')\n",
    "\n",
    "                        # Update progress\n",
    "                        progress.value = 1\n",
    "                        \n",
    "                        # Upload the csv to files\n",
    "                        sites_df.to_csv('sites.csv', index=False)\n",
    "                        csv_path = '/arcgis/sites.csv'\n",
    "                        collection.manager.overwrite(csv_path)\n",
    "                        os.remove('/arcgis/sites.csv')\n",
    "                        \n",
    "                        # Update progress\n",
    "                        progress.value = 2\n",
    "\n",
    "                # Rename IBI, add Score column        \n",
    "                df = df.rename(columns={'Avg_IBI_Score': 'Index_of_Biological_Integrity__'})\n",
    "                df['Session_Date'] = pd.to_datetime(df['Session_Date']).dt.strftime('%m/%d/%Y')\n",
    "                \n",
    "                # Upload the csv to files\n",
    "                df.to_csv('ibi_scores.csv', index=False)\n",
    "                \n",
    "                # Update progress\n",
    "                progress.value = 3\n",
    "                           \n",
    "                # Search for IBI Scores table\n",
    "                items = gis.content.search(query='title:IBI Scores', item_type='Feature Service')\n",
    "                ibi_found = False\n",
    "                for item in items:\n",
    "                    if item.title == \"IBI Scores\":\n",
    "                        ibi_found = True\n",
    "                        \n",
    "                        # Replace table item\n",
    "                        collection = FeatureLayerCollection.fromitem(item)\n",
    "                        csv_path = '/arcgis/ibi_scores.csv'\n",
    "                        collection.manager.overwrite(csv_path)\n",
    "                        \n",
    "                # IBI Scores table not found, create new table\n",
    "                if not ibi_found:\n",
    "                    item_properties = {\n",
    "                        \"title\": \"IBI Scores\",\n",
    "                        'type': 'CSV',\n",
    "                    }\n",
    "                    n_item = gis.content.add(item_properties=item_properties, data='/arcgis/ibi_scores.csv')\n",
    "                    p_item = n_item.publish()\n",
    "                \n",
    "                # Clean up temporary file\n",
    "                os.remove('/arcgis/ibi_scores.csv')\n",
    "            \n",
    "                # Update progress\n",
    "                progress.value = 4\n",
    "\n",
    "                print('IBI Scores table updated.')\n",
    "            \n",
    "            except Exception as e:\n",
    "                print(f\"Error: {str(e)}\")\n",
    "                        \n",
    "        else:\n",
    "            print(\"Error: No file uploaded.\")\n",
    "            \n",
    "# Suppress admin warning\n",
    "warnings.filterwarnings(\"ignore\", message=\"You are logged on as .* with an administrator role\")\n",
    "            \n",
    "# GIS connection\n",
    "gis = GIS(\"home\")            \n",
    "            \n",
    "# Initialize widgets and button click event\n",
    "output = widgets.Output()\n",
    "uploader = widgets.FileUpload(accept='.csv', multiple=False)\n",
    "button = widgets.Button(description='Update Table', tooltip='Click me')\n",
    "button.on_click(update_table)\n",
    "\n",
    "# Display widgets and button\n",
    "print('\\nUpload LimnoTech IBI Over Time report (csv format).')\n",
    "display(uploader)\n",
    "\n",
    "print('\\nClick the button to update the table.')\n",
    "display(button)\n",
    "display(output)"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}