LAT_DryDomesticWellSusceptibilityAnalysisPythonNotebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Modification of DWR Python Notebook for Dry Domestic Well Susceptibility Analysis v1.1\n",
    "Gabrielle LaMarr LeMee"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a modification of a Department of Water Resources (DWR) analysis that identifies areas within groundwater basins throughout the State that may be prone to water supply shortages due to domestic drinking water wells going dry. The dashboard identifies the density of \"susceptible” domestic wells per square mile based on recent groundwater level measurements and modeled future depth to water. If the modeled future depth to water falls below the dry well depth of a domestic well, the well is labelled susceptible.\n",
    "\n",
    "DWR notebook: https://data.cnra.ca.gov/dataset/calgw-live/resource/c1c30d5f-c4bf-43fa-94d3-e7c9d5037df9\n",
    "\n",
    "*Their data note: The dashboard and this underlying analysis provide a density map of domestic wells that are susceptible to going dry if recent groundwater trends continue. The map can be used to evaluate the relative density distribution within groundwater basins. However, the map should not be used to estimate the absolute number of domestic wells that are susceptible to going dry for any area or groundwater level scenario. While the applied groundwater level scenario is based on best available datasets, the scenario is hypothetical, and is chosen to resolve regional differences in the density of domestic wells that are susceptible to going dry. Available groundwater level data are interpolated and projected to domestic wells locations. To achieve near statewide coverage for this analysis, groundwater level measurements are projected up to 10 miles in areas where no local monitoring exists. Filling these groundwater level data gaps with new monitoring stations will improve the reliability of the Dry Well Susceptibility Analysis. The applied model for the depth at which a domestic well is susceptible to going dry is simplified and uncalibrated and is not intended to represent any specific well failure mode. Future calibration of this model, and consideration of well construction details will improve the reliability of the Dry Well Susceptibility Analysis.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import matplotlib\n",
    "from datetime import datetime as dt\n",
    "import numpy as np\n",
    "import itertools\n",
    "from itertools import combinations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.float_format', '{:.2f}'.format)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download Groundwater Level Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read groundwater level\n",
    "# For dataset info, see https://data.cnra.ca.gov/dataset/periodic-groundwater-level-measurements\n",
    "url=\"https://data.cnra.ca.gov/dataset/dd9b15f5-6d08-4d8c-bace-37dc761a9c08/resource/bfa9f262-24a1-45bd-8dc8-138bc8107266/download/measurements.csv\"\n",
    "# If you have already downloaded the measurements dataset, read in the local copy\n",
    "# url=\".../measurements.csv\"\n",
    "df_gwld=pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keep only relevant fields\n",
    "df_gwld = df_gwld[['site_code','msmt_date','gse_gwe','wlm_qa_desc']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert column types\n",
    "df_gwld['msmt_date'] = df_gwld['msmt_date'].astype('datetime64[ns]')\n",
    "df_gwld['gse_gwe'] = df_gwld['gse_gwe'].astype(np.float64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add  measurement Year Column\n",
    "df_gwld['msmt_year'] = df_gwld['msmt_date'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Only keep data from a specified year onwards\n",
    "lowcutoffyear = 2011\n",
    "highcutoffyear = 2021\n",
    "df_gwld = df_gwld[(df_gwld.msmt_year >= lowcutoffyear) & (df_gwld.msmt_year <= highcutoffyear)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use only those measurements with\n",
    "# WLM_QA_DESC is NULL (None), or\n",
    "# WLM_QA_DESC=’Oil or foreign substance in casing’ or\n",
    "# WLM_QA_DESC=’Acoustical sounder’\n",
    "df_gwld['wlm_qa_desc'].fillna(\"No Water Level Measurement Quality Description\", inplace = True)\n",
    "df_gwld=df_gwld[df_gwld['wlm_qa_desc'].isin(['Oil or foreign substance in casing', 'Acoustical sounder', 'No Water Level Measurement Quality Description'])]\n",
    "df_gwld.drop(columns=['wlm_qa_desc'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "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>site_code</th>\n",
       "      <th>msmt_date</th>\n",
       "      <th>gse_gwe</th>\n",
       "      <th>msmt_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>2021-08-26 20:00:00</td>\n",
       "      <td>412.00</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>2021-07-29 19:00:00</td>\n",
       "      <td>410.00</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>2021-06-24 18:00:00</td>\n",
       "      <td>411.00</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>2021-05-27 17:00:00</td>\n",
       "      <td>411.00</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>2021-04-29 16:00:00</td>\n",
       "      <td>409.00</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            site_code           msmt_date  gse_gwe  msmt_year\n",
       "0  320000N1140000W001 2021-08-26 20:00:00   412.00       2021\n",
       "1  320000N1140000W001 2021-07-29 19:00:00   410.00       2021\n",
       "2  320000N1140000W001 2021-06-24 18:00:00   411.00       2021\n",
       "3  320000N1140000W001 2021-05-27 17:00:00   411.00       2021\n",
       "4  320000N1140000W001 2021-04-29 16:00:00   409.00       2021"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gwld.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find Spring Groundwater Levels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find spring (high) water levels/minimum depth to water\n",
    "# Specify Year from which to use Spring WLs\n",
    "SpringWLYear = 2021\n",
    "# Only keep measurements from SpringWLYear\n",
    "df_springWL = df_gwld[df_gwld.msmt_year == SpringWLYear]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1x/hkrq_4px1_x314p6_8yb37m80000gn/T/ipykernel_2090/409520231.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_springWL.loc[mask_Spring, 'msmt_season'] = 'Spring'\n",
      "/var/folders/1x/hkrq_4px1_x314p6_8yb37m80000gn/T/ipykernel_2090/409520231.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_springWL.dropna(subset=['msmt_season'],inplace=True)\n"
     ]
    }
   ],
   "source": [
    "# Only keep spring (january-may) measurements\n",
    "mask_Spring = (df_gwld['msmt_date'].dt.month>=1) & (df_gwld['msmt_date'].dt.month<=5)\n",
    "df_springWL.loc[mask_Spring, 'msmt_season'] = 'Spring'\n",
    "df_springWL.dropna(subset=['msmt_season'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find min. depth to water value for each site code\n",
    "df_springWL=df_springWL.groupby(['site_code'])['gse_gwe'].min().reset_index().round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To filter out noise, only use those wells where\n",
    "# SpringMinDepth IS NOT NULL AND\n",
    "# SpringMinDepth <1000 AND SpringMinDepth >0\n",
    "df_springWL = df_springWL[(df_springWL['gse_gwe'] > 0) & (df_springWL['gse_gwe'] < 1000)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "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>site_code</th>\n",
       "      <th>gse_gwe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>409.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>325536N1170608W001</td>\n",
       "      <td>13.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>325910N1170835W006</td>\n",
       "      <td>12.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>326821N1171124W006</td>\n",
       "      <td>8.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>327781N1171209W006</td>\n",
       "      <td>18.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            site_code  gse_gwe\n",
       "0  320000N1140000W001   409.00\n",
       "1  325536N1170608W001    13.83\n",
       "2  325910N1170835W006    12.95\n",
       "3  326821N1171124W006     8.98\n",
       "4  327781N1171209W006    18.02"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_springWL.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find 5-year water level variability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the min/max depth to water value for each year\n",
    "# To filter out noise, instead of true min/max, use\n",
    "# 5th and 95th percentile instead\n",
    "df_gwl_annual_min_max=df_gwld.groupby(['site_code','msmt_year'])['gse_gwe'].describe(percentiles=[.05,.95])[['5%','95%']].reset_index().round(2)\n",
    "df_gwl_annual_min_max.rename(columns={'5%':'max','95%':'min'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create unique list of Site COdes\n",
    "lst_SITE_CODES = df_gwl_annual_min_max['site_code'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create list of years to consider\n",
    "lst_Years = [i for i in range(lowcutoffyear,highcutoffyear+1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create 2D list of all site code and year combinations\n",
    "lst_all_Site_Codes_and_Years = list(itertools.product(lst_SITE_CODES, lst_Years))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert list of site codes and years to dataframe\n",
    "df_all_Site_Codes_and_Years = pd.DataFrame (lst_all_Site_Codes_and_Years, columns = ['site_code','msmt_year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge dataframe of all site codes and years with actual data table, to\n",
    "# have rows for every year, even if there is no data, so \n",
    "# that rolling min/max can be calculated\n",
    "df_gwl_annual_min_max_MERGED = pd.merge(df_gwl_annual_min_max, df_all_Site_Codes_and_Years,  how='right', left_on=['msmt_year','site_code'], right_on = ['msmt_year','site_code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "# To calculate 5 year variability, run rolling min/max, calculate drop, and pick max drop\n",
    "# Define the time window in years to calculate WL drop from\n",
    "VariabilityWindowInYears=5\n",
    "# Limit the maximim drop to be considered (to filter out noisy data)\n",
    "MaxDrop=300\n",
    "\n",
    "indexer = pd.api.indexers.FixedForwardWindowIndexer(window_size=VariabilityWindowInYears)\n",
    "df_gwl_annual_min_max_MERGED['movingMin'] = df_gwl_annual_min_max_MERGED.groupby('site_code')['min'].transform(lambda x: x.rolling(window=indexer, min_periods=VariabilityWindowInYears).max())\n",
    "df_gwl_annual_min_max_MERGED['movingMax'] = df_gwl_annual_min_max_MERGED.groupby('site_code')['max'].transform(lambda x: x.rolling(window=indexer, min_periods=VariabilityWindowInYears).min())\n",
    "# calculate rolling 5 year drop\n",
    "df_gwl_annual_min_max_MERGED['Drop']=df_gwl_annual_min_max_MERGED['movingMin']-df_gwl_annual_min_max_MERGED['movingMax']\n",
    "# find max 5-year drop for each site\n",
    "df_Drop=df_gwl_annual_min_max_MERGED.groupby(['site_code'])['Drop'].max().reset_index()\n",
    "# remove rows with no Drop values\n",
    "df_Drop.dropna(subset=['Drop'],inplace=True)\n",
    "# remove rows with noisy/erroneous Drop values\n",
    "df_Drop = df_Drop[(df_Drop['Drop'] > 0) & (df_Drop['Drop'] < MaxDrop)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>site_code</th>\n",
       "      <th>Drop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>320000N1140000W001</td>\n",
       "      <td>57.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>325536N1170608W001</td>\n",
       "      <td>17.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>325910N1170835W006</td>\n",
       "      <td>5.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>326821N1171124W006</td>\n",
       "      <td>6.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>327781N1171209W006</td>\n",
       "      <td>2.24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            site_code  Drop\n",
       "0  320000N1140000W001 57.70\n",
       "1  325536N1170608W001 17.10\n",
       "2  325910N1170835W006  5.43\n",
       "3  326821N1171124W006  6.28\n",
       "5  327781N1171209W006  2.24"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_Drop.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download GWL Station data  \n",
    "and join Spring WL and 5-year level drop attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read groundwater level stations\n",
    "# For dataset info, see https://data.cnra.ca.gov/dataset/gspmd/resource/38dc5a77-0428-4d8b-970a-51797ed2cd36\n",
    "url=\"https://data.cnra.ca.gov/dataset/536dc423-01b3-4094-bdcd-903df84f6768/resource/38dc5a77-0428-4d8b-970a-51797ed2cd36/download/groundwater_level_sites.csv\"\n",
    "# If you have already downloaded the measurements dataset, read in the local copy\n",
    "# url=\".../stations.csv\"\n",
    "df_gwls=pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>STN_ID</th>\n",
       "      <th>SITE_CODE</th>\n",
       "      <th>WELL_NAME</th>\n",
       "      <th>BASIN_NAME</th>\n",
       "      <th>GSA_NAME</th>\n",
       "      <th>GSP_NAME</th>\n",
       "      <th>MONITORING_NETWORK_TYPE</th>\n",
       "      <th>SUSTAINABILITY_INDICATORS</th>\n",
       "      <th>PRINCIPAL_AQUIFER</th>\n",
       "      <th>SWN</th>\n",
       "      <th>...</th>\n",
       "      <th>SMC_START_DATE</th>\n",
       "      <th>SMC_MT</th>\n",
       "      <th>SMC_IM_5_YR</th>\n",
       "      <th>SMC_IM_10_YR</th>\n",
       "      <th>SMC_IM_15_YR</th>\n",
       "      <th>SMC_MO</th>\n",
       "      <th>COMMENTS</th>\n",
       "      <th>FIRST_MSMT_DATE</th>\n",
       "      <th>LAST_MSMT_DATE</th>\n",
       "      <th>MSMT_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2294</td>\n",
       "      <td>344089N1187801W001</td>\n",
       "      <td>04N18W20R01S</td>\n",
       "      <td>4-004.06 Piru</td>\n",
       "      <td>Fillmore and Piru Basins GSA - Piru</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SGMA Representative</td>\n",
       "      <td>Groundwater Storage, Groundwater Levels</td>\n",
       "      <td>B</td>\n",
       "      <td>04N18W20R001S</td>\n",
       "      <td>...</td>\n",
       "      <td>2022-02-01 00:00:00</td>\n",
       "      <td>344.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>620.00</td>\n",
       "      <td>Basin is not in long term overdraft so no inte...</td>\n",
       "      <td>1972-10-09 00:00:00</td>\n",
       "      <td>2022-05-17 12:00:00</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STN_ID           SITE_CODE     WELL_NAME     BASIN_NAME  \\\n",
       "0    2294  344089N1187801W001  04N18W20R01S  4-004.06 Piru   \n",
       "\n",
       "                              GSA_NAME GSP_NAME MONITORING_NETWORK_TYPE  \\\n",
       "0  Fillmore and Piru Basins GSA - Piru      NaN     SGMA Representative   \n",
       "\n",
       "                 SUSTAINABILITY_INDICATORS PRINCIPAL_AQUIFER            SWN  \\\n",
       "0  Groundwater Storage, Groundwater Levels                 B  04N18W20R001S   \n",
       "\n",
       "   ...       SMC_START_DATE  SMC_MT  SMC_IM_5_YR SMC_IM_10_YR SMC_IM_15_YR  \\\n",
       "0  ...  2022-02-01 00:00:00  344.00          NaN          NaN          NaN   \n",
       "\n",
       "   SMC_MO                                           COMMENTS  \\\n",
       "0  620.00  Basin is not in long term overdraft so no inte...   \n",
       "\n",
       "       FIRST_MSMT_DATE       LAST_MSMT_DATE  MSMT_COUNT  \n",
       "0  1972-10-09 00:00:00  2022-05-17 12:00:00         476  \n",
       "\n",
       "[1 rows x 30 columns]"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_gwls.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove wells with total well depth<100 and top_perf>400\n",
    "df_gwls = df_gwls[(df_gwls[\"WELL_DEPTH\"] >= 100) | pd.isna(df_gwls[\"WELL_DEPTH\"])]\n",
    "df_gwls = df_gwls[(df_gwls[\"TOP_PRF\"] >= 400) | pd.isna(df_gwls[\"TOP_PRF\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "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>site_code</th>\n",
       "      <th>basin_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>gse_gwe</th>\n",
       "      <th>Drop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>343939N1187920W001</td>\n",
       "      <td>4-004.06 Piru</td>\n",
       "      <td>34.39</td>\n",
       "      <td>-118.79</td>\n",
       "      <td>66.30</td>\n",
       "      <td>54.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>342533N1192690W001</td>\n",
       "      <td>4-004.03 Mound</td>\n",
       "      <td>34.25</td>\n",
       "      <td>-119.27</td>\n",
       "      <td>11.49</td>\n",
       "      <td>19.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>343089N1189415W001</td>\n",
       "      <td>4-008 Las Posas Valley</td>\n",
       "      <td>34.31</td>\n",
       "      <td>-118.94</td>\n",
       "      <td>576.30</td>\n",
       "      <td>47.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>341242N1191331W001</td>\n",
       "      <td>4-004.02 Oxnard</td>\n",
       "      <td>34.12</td>\n",
       "      <td>-119.13</td>\n",
       "      <td>56.05</td>\n",
       "      <td>90.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>341242N1191331W003</td>\n",
       "      <td>4-004.02 Oxnard</td>\n",
       "      <td>34.12</td>\n",
       "      <td>-119.13</td>\n",
       "      <td>51.79</td>\n",
       "      <td>90.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            site_code              basin_name  latitude  longitude  gse_gwe  \\\n",
       "0  343939N1187920W001           4-004.06 Piru     34.39    -118.79    66.30   \n",
       "1  342533N1192690W001          4-004.03 Mound     34.25    -119.27    11.49   \n",
       "2  343089N1189415W001  4-008 Las Posas Valley     34.31    -118.94   576.30   \n",
       "3  341242N1191331W001         4-004.02 Oxnard     34.12    -119.13    56.05   \n",
       "4  341242N1191331W003         4-004.02 Oxnard     34.12    -119.13    51.79   \n",
       "\n",
       "   Drop  \n",
       "0 54.52  \n",
       "1 19.75  \n",
       "2 47.74  \n",
       "3 90.04  \n",
       "4 90.94  "
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add Spring Depth to Water and 5yr-level WL drop fields\n",
    "df_gwls = df_gwls[[\"SITE_CODE\",\"BASIN_NAME\",\"LATITUDE\",\"LONGITUDE\"]]\n",
    "df_gwls.columns = df_gwls.columns.str.lower()\n",
    "df_merge = df_gwls.merge(df_springWL, on=\"site_code\", how=\"left\")\n",
    "df_merge = df_merge.merge(df_Drop, on=\"site_code\", how=\"left\")\n",
    "df_merge = df_merge.dropna(subset=['gse_gwe'])\n",
    "df_merge.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create water level and drops geodataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = gpd.GeoDataFrame(df_merge, geometry=gpd.points_from_xy(df_merge.longitude, df_merge.latitude))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "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>site_code</th>\n",
       "      <th>basin_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>gse_gwe</th>\n",
       "      <th>Drop</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>343939N1187920W001</td>\n",
       "      <td>4-004.06 Piru</td>\n",
       "      <td>34.39</td>\n",
       "      <td>-118.79</td>\n",
       "      <td>66.30</td>\n",
       "      <td>54.52</td>\n",
       "      <td>POINT (-118.78900 34.39480)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>342533N1192690W001</td>\n",
       "      <td>4-004.03 Mound</td>\n",
       "      <td>34.25</td>\n",
       "      <td>-119.27</td>\n",
       "      <td>11.49</td>\n",
       "      <td>19.75</td>\n",
       "      <td>POINT (-119.26800 34.25370)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>343089N1189415W001</td>\n",
       "      <td>4-008 Las Posas Valley</td>\n",
       "      <td>34.31</td>\n",
       "      <td>-118.94</td>\n",
       "      <td>576.30</td>\n",
       "      <td>47.74</td>\n",
       "      <td>POINT (-118.94100 34.30870)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>341242N1191331W001</td>\n",
       "      <td>4-004.02 Oxnard</td>\n",
       "      <td>34.12</td>\n",
       "      <td>-119.13</td>\n",
       "      <td>56.05</td>\n",
       "      <td>90.04</td>\n",
       "      <td>POINT (-119.13300 34.12430)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>341242N1191331W003</td>\n",
       "      <td>4-004.02 Oxnard</td>\n",
       "      <td>34.12</td>\n",
       "      <td>-119.13</td>\n",
       "      <td>51.79</td>\n",
       "      <td>90.94</td>\n",
       "      <td>POINT (-119.13300 34.12430)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            site_code              basin_name  latitude  longitude  gse_gwe  \\\n",
       "0  343939N1187920W001           4-004.06 Piru     34.39    -118.79    66.30   \n",
       "1  342533N1192690W001          4-004.03 Mound     34.25    -119.27    11.49   \n",
       "2  343089N1189415W001  4-008 Las Posas Valley     34.31    -118.94   576.30   \n",
       "3  341242N1191331W001         4-004.02 Oxnard     34.12    -119.13    56.05   \n",
       "4  341242N1191331W003         4-004.02 Oxnard     34.12    -119.13    51.79   \n",
       "\n",
       "   Drop                     geometry  \n",
       "0 54.52  POINT (-118.78900 34.39480)  \n",
       "1 19.75  POINT (-119.26800 34.25370)  \n",
       "2 47.74  POINT (-118.94100 34.30870)  \n",
       "3 90.04  POINT (-119.13300 34.12430)  \n",
       "4 90.94  POINT (-119.13300 34.12430)  "
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf = gdf.set_crs(epsg=6933)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download Well Completion Reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1x/hkrq_4px1_x314p6_8yb37m80000gn/T/ipykernel_2090/2676508439.py:6: DtypeWarning: Columns (14,15) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  df_wcr=pd.read_csv(url)\n"
     ]
    }
   ],
   "source": [
    "# Read well completion reports\n",
    "# For dataset info, see https://data.cnra.ca.gov/dataset/periodic-groundwater-level-measurements\n",
    "url=\"https://data.cnra.ca.gov/dataset/647afc02-8954-426d-aabd-eff418d2652c/resource/8da7b93b-4e69-495d-9caa-335691a1896b/download/wellcompletionreports.csv\"\n",
    "# If you have already downloaded the measurements dataset, read in the local copy\n",
    "# url=\".../measurements.csv\"\n",
    "df_wcr=pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "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>WCRNUMBER</th>\n",
       "      <th>LEGACYLOGNUMBER</th>\n",
       "      <th>REGIONOFFICE</th>\n",
       "      <th>COUNTYNAME</th>\n",
       "      <th>LOCALPERMITAGENCY</th>\n",
       "      <th>PERMITDATE</th>\n",
       "      <th>PERMITNUMBER</th>\n",
       "      <th>OWNERASSIGNEDWELLNUMBER</th>\n",
       "      <th>WELLLOCATION</th>\n",
       "      <th>CITY</th>\n",
       "      <th>...</th>\n",
       "      <th>CASINGDIAMETER</th>\n",
       "      <th>DRILLINGMETHOD</th>\n",
       "      <th>FLUID</th>\n",
       "      <th>STATICWATERLEVEL</th>\n",
       "      <th>TOTALDRAWDOWN</th>\n",
       "      <th>TESTTYPE</th>\n",
       "      <th>PUMPTESTLENGTH</th>\n",
       "      <th>WELLYIELD</th>\n",
       "      <th>WELLYIELDUNITOFMEASURE</th>\n",
       "      <th>OTHEROBSERVATIONS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WCR0237163</td>\n",
       "      <td>E0146494</td>\n",
       "      <td>DWR North Central Region Office</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Zone 7 Water Agency - Alameda County Flood Con...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WCR0245038</td>\n",
       "      <td>01-1476</td>\n",
       "      <td>DWR North Central Region Office</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Public Works Agency, Water Reso...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9318 CASTRO VALLEY BLVD</td>\n",
       "      <td>HAYWARD</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>WCR0244931</td>\n",
       "      <td>E000126</td>\n",
       "      <td>DWR North Central Region Office</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Zone 7 Water Agency - Alameda County Flood Con...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WCR0245618</td>\n",
       "      <td>14769</td>\n",
       "      <td>DWR North Central Region Office</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Zone 7 Water Agency - Alameda County Flood Con...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>8.00</td>\n",
       "      <td>Cable Tool</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.00</td>\n",
       "      <td>GPM</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WCR0245642</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DWR North Central Region Office</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda County Public Works Agency, Water Reso...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 45 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    WCRNUMBER LEGACYLOGNUMBER                     REGIONOFFICE COUNTYNAME  \\\n",
       "0  WCR0237163        E0146494  DWR North Central Region Office    Alameda   \n",
       "1  WCR0245038         01-1476  DWR North Central Region Office    Alameda   \n",
       "2  WCR0244931         E000126  DWR North Central Region Office    Alameda   \n",
       "3  WCR0245618           14769  DWR North Central Region Office    Alameda   \n",
       "4  WCR0245642             NaN  DWR North Central Region Office    Alameda   \n",
       "\n",
       "                                   LOCALPERMITAGENCY PERMITDATE PERMITNUMBER  \\\n",
       "0  Zone 7 Water Agency - Alameda County Flood Con...        NaN          NaN   \n",
       "1  Alameda County Public Works Agency, Water Reso...        NaN          NaN   \n",
       "2  Zone 7 Water Agency - Alameda County Flood Con...        NaN          NaN   \n",
       "3  Zone 7 Water Agency - Alameda County Flood Con...        NaN          NaN   \n",
       "4  Alameda County Public Works Agency, Water Reso...        NaN          NaN   \n",
       "\n",
       "  OWNERASSIGNEDWELLNUMBER             WELLLOCATION     CITY  ...  \\\n",
       "0                     NaN                      NaN      NaN  ...   \n",
       "1                     NaN  9318 CASTRO VALLEY BLVD  HAYWARD  ...   \n",
       "2                     NaN                      NaN      NaN  ...   \n",
       "3                     NaN                      NaN      NaN  ...   \n",
       "4                     NaN                      NaN      NaN  ...   \n",
       "\n",
       "  CASINGDIAMETER DRILLINGMETHOD  FLUID STATICWATERLEVEL TOTALDRAWDOWN  \\\n",
       "0            NaN            NaN    NaN              NaN           NaN   \n",
       "1            NaN          Other  Other              NaN           NaN   \n",
       "2            NaN            NaN    NaN              NaN           NaN   \n",
       "3           8.00     Cable Tool   None              NaN           NaN   \n",
       "4            NaN            NaN    NaN              NaN           NaN   \n",
       "\n",
       "  TESTTYPE PUMPTESTLENGTH WELLYIELD WELLYIELDUNITOFMEASURE  OTHEROBSERVATIONS  \n",
       "0      NaN            NaN       NaN                    NaN                NaN  \n",
       "1      NaN            NaN       NaN                    NaN                NaN  \n",
       "2      NaN            NaN       NaN                    NaN                NaN  \n",
       "3      NaN            NaN     20.00                    GPM                NaN  \n",
       "4      NaN            NaN       NaN                    NaN                NaN  \n",
       "\n",
       "[5 rows x 45 columns]"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wcr.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wcr = df_wcr[(df_wcr[\"DATEWORKENDED\"] >= \"1977-01-01\") & (df_wcr[\"PLANNEDUSEFORMERUSE\"].str.contains(\"Domestic\")) & (df_wcr[\"RECORDTYPE\"].isin(['WellCompletion/New/Production or Monitoring/NA', 'WellCompletion/Modification or Repair/Production or Monitoring/NA']))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define the AtRiskWellDepth value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wcr[\"SusceptibleWellDepth\"] = df_wcr[\"TOTALCOMPLETEDDEPTH\"] * 0.8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Find the closest monitoring well for each domestic well"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wcr[\"DECIMALLONGITUDE\"] = pd.to_numeric(df_wcr[\"DECIMALLONGITUDE\"], errors='coerce')\n",
    "df_wcr[\"DECIMALLATITUDE\"] = pd.to_numeric(df_wcr[\"DECIMALLATITUDE\"], errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wcr = df_wcr.dropna(subset=['DECIMALLONGITUDE', 'DECIMALLATITUDE', 'SusceptibleWellDepth'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_wcr = gpd.GeoDataFrame(df_wcr, geometry=gpd.points_from_xy(df_wcr.DECIMALLONGITUDE, df_wcr.DECIMALLATITUDE))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_wcr = gdf_wcr.set_crs(epsg=6933)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Find the closest monitoring well\n",
    "gdf_wcr_data = gpd.sjoin_nearest(gdf_wcr, gdf, how='left', distance_col=\"distance_meters\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['site_code', 'basin_name', 'latitude', 'longitude', 'gse_gwe', 'Drop',\n",
       "       'geometry'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_wcr_data_trim = gdf_wcr_data[['WCRNUMBER', 'DECIMALLONGITUDE', 'DECIMALLATITUDE', 'SusceptibleWellDepth', 'site_code', 'basin_name', \n",
    "                                  'latitude', 'longitude', 'gse_gwe', 'Drop', 'geometry', 'distance_meters']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set flag to identify those wells that are already dry given these assumptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_wcr_data_trim[\"IsAlreadyDry\"] = False\n",
    "gdf_wcr_data_trim.loc[gdf_wcr_data_trim['gse_gwe'] >= gdf_wcr_data_trim['SusceptibleWellDepth'], 'IsAlreadyDry'] = True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set flag to identify those wells that are susceptible to go dry given these assumptions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_wcr_data_trim[\"Susceptible\"] = False\n",
    "gdf_wcr_data_trim.loc[(gdf_wcr_data_trim['gse_gwe'] + gdf_wcr_data_trim['Drop']) >= gdf_wcr_data_trim['SusceptibleWellDepth'], 'Susceptible'] = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "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>WCRNUMBER</th>\n",
       "      <th>DECIMALLONGITUDE</th>\n",
       "      <th>DECIMALLATITUDE</th>\n",
       "      <th>SusceptibleWellDepth</th>\n",
       "      <th>site_code</th>\n",
       "      <th>basin_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>gse_gwe</th>\n",
       "      <th>Drop</th>\n",
       "      <th>geometry</th>\n",
       "      <th>distance_meters</th>\n",
       "      <th>IsAlreadyDry</th>\n",
       "      <th>Susceptible</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>367464</th>\n",
       "      <td>WCR2015-000207</td>\n",
       "      <td>-1233.55</td>\n",
       "      <td>39.80</td>\n",
       "      <td>108.00</td>\n",
       "      <td>392358N1232020W001</td>\n",
       "      <td>1-052 Ukiah Valley</td>\n",
       "      <td>39.24</td>\n",
       "      <td>-123.20</td>\n",
       "      <td>17.40</td>\n",
       "      <td>34.25</td>\n",
       "      <td>POINT (-1233.551 39.796)</td>\n",
       "      <td>1110.35</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588625</th>\n",
       "      <td>WCR1977-010789</td>\n",
       "      <td>-114.24</td>\n",
       "      <td>34.19</td>\n",
       "      <td>44.00</td>\n",
       "      <td>339034N1167436W001</td>\n",
       "      <td>7-021.04 San Gorgonio Pass</td>\n",
       "      <td>33.90</td>\n",
       "      <td>-116.74</td>\n",
       "      <td>311.88</td>\n",
       "      <td>20.78</td>\n",
       "      <td>POINT (-114.238 34.190)</td>\n",
       "      <td>2.52</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199072</th>\n",
       "      <td>WCR1971-002900</td>\n",
       "      <td>-114.48</td>\n",
       "      <td>32.88</td>\n",
       "      <td>68.80</td>\n",
       "      <td>330924N1169465W001</td>\n",
       "      <td>9-010 San Pasqual Valley</td>\n",
       "      <td>33.09</td>\n",
       "      <td>-116.95</td>\n",
       "      <td>64.65</td>\n",
       "      <td>45.34</td>\n",
       "      <td>POINT (-114.478 32.884)</td>\n",
       "      <td>2.48</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>609450</th>\n",
       "      <td>WCR1969-002005</td>\n",
       "      <td>-114.31</td>\n",
       "      <td>34.16</td>\n",
       "      <td>160.00</td>\n",
       "      <td>339034N1167436W001</td>\n",
       "      <td>7-021.04 San Gorgonio Pass</td>\n",
       "      <td>33.90</td>\n",
       "      <td>-116.74</td>\n",
       "      <td>311.88</td>\n",
       "      <td>20.78</td>\n",
       "      <td>POINT (-114.308 34.160)</td>\n",
       "      <td>2.45</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>597169</th>\n",
       "      <td>WCR1969-002007</td>\n",
       "      <td>-114.31</td>\n",
       "      <td>34.16</td>\n",
       "      <td>80.00</td>\n",
       "      <td>339034N1167436W001</td>\n",
       "      <td>7-021.04 San Gorgonio Pass</td>\n",
       "      <td>33.90</td>\n",
       "      <td>-116.74</td>\n",
       "      <td>311.88</td>\n",
       "      <td>20.78</td>\n",
       "      <td>POINT (-114.308 34.160)</td>\n",
       "      <td>2.45</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             WCRNUMBER  DECIMALLONGITUDE  DECIMALLATITUDE  \\\n",
       "367464  WCR2015-000207          -1233.55            39.80   \n",
       "588625  WCR1977-010789           -114.24            34.19   \n",
       "199072  WCR1971-002900           -114.48            32.88   \n",
       "609450  WCR1969-002005           -114.31            34.16   \n",
       "597169  WCR1969-002007           -114.31            34.16   \n",
       "\n",
       "        SusceptibleWellDepth           site_code                  basin_name  \\\n",
       "367464                108.00  392358N1232020W001          1-052 Ukiah Valley   \n",
       "588625                 44.00  339034N1167436W001  7-021.04 San Gorgonio Pass   \n",
       "199072                 68.80  330924N1169465W001    9-010 San Pasqual Valley   \n",
       "609450                160.00  339034N1167436W001  7-021.04 San Gorgonio Pass   \n",
       "597169                 80.00  339034N1167436W001  7-021.04 San Gorgonio Pass   \n",
       "\n",
       "        latitude  longitude  gse_gwe  Drop                  geometry  \\\n",
       "367464     39.24    -123.20    17.40 34.25  POINT (-1233.551 39.796)   \n",
       "588625     33.90    -116.74   311.88 20.78   POINT (-114.238 34.190)   \n",
       "199072     33.09    -116.95    64.65 45.34   POINT (-114.478 32.884)   \n",
       "609450     33.90    -116.74   311.88 20.78   POINT (-114.308 34.160)   \n",
       "597169     33.90    -116.74   311.88 20.78   POINT (-114.308 34.160)   \n",
       "\n",
       "        distance_meters  IsAlreadyDry  Susceptible  \n",
       "367464          1110.35         False        False  \n",
       "588625             2.52          True         True  \n",
       "199072             2.48         False         True  \n",
       "609450             2.45          True         True  \n",
       "597169             2.45          True         True  "
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf_wcr_data_trim.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What does this look like for not yet dry wells?\n",
    "On average, domestic wells that are not yet dry can tolerate 142 feet of water level decline. If conditions for the next 5 years are similar to the previous 5 years, these wells face an average decline of 27 feet."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [],
   "source": [
    "not_dry = gdf_wcr_data_trim.loc[gdf_wcr_data_trim[\"IsAlreadyDry\"] == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "234621"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(not_dry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/glamarrlemee/.local/share/virtualenvs/dwr-well-susceptibility-M0lscrqj/lib/python3.9/site-packages/geopandas/geodataframe.py:1443: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  super().__setitem__(key, value)\n"
     ]
    }
   ],
   "source": [
    "not_dry[\"difference\"] = not_dry[\"SusceptibleWellDepth\"] - not_dry[\"gse_gwe\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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>difference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>234621.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>142.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>129.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>54.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>106.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>188.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1592.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       difference\n",
       "count   234621.00\n",
       "mean       142.43\n",
       "std        129.41\n",
       "min          0.00\n",
       "25%         54.44\n",
       "50%        106.60\n",
       "75%        188.58\n",
       "max       1592.00"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_dry[['difference']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "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>Drop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>196624.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>27.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>24.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>11.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>22.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>32.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>281.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Drop\n",
       "count 196624.00\n",
       "mean      27.18\n",
       "std       24.04\n",
       "min        1.12\n",
       "25%       11.37\n",
       "50%       22.20\n",
       "75%       32.31\n",
       "max      281.44"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "not_dry[['Drop']].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What does this look like for susceptible wells?\n",
    "10% of not-yet-dry wells are susceptible to going dry in the next 5 years if water level declines are similar to the previous 5 years. These susceptible wells have, on average, 24 feet of depth remaining and face a 49 foot decline. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "susceptible = not_dry.loc[not_dry[\"Susceptible\"] == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24119"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(susceptible)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.10279983462690893"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(susceptible) / len(not_dry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "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>difference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>24119.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>24.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>24.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>8.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>16.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>32.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>278.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       difference\n",
       "count    24119.00\n",
       "mean        24.17\n",
       "std         24.62\n",
       "min          0.00\n",
       "25%          8.12\n",
       "50%         16.75\n",
       "75%         32.90\n",
       "max        278.21"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "susceptible[['difference']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "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>Drop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>24119.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>49.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>37.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>23.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>37.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>72.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>281.44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Drop\n",
       "count 24119.00\n",
       "mean     49.23\n",
       "std      37.60\n",
       "min       2.72\n",
       "25%      23.52\n",
       "50%      37.08\n",
       "75%      72.40\n",
       "max     281.44"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "susceptible[['Drop']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}