01_clean_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9423f173-0b59-45d2-9024-297cc628489a",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d6c17c10-acab-4164-9267-4a7ba405be2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "abdb557a-962b-4a61-a15a-3ea0c6c827c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "import geopandas as gpd\n",
    "import pygeos"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a890d160-7733-4143-ab99-2b8edfdf57ed",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Township/range geo file\n",
    "Data is grouped into township/range regions for the maps."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a63d4ac9-b074-4b6e-bd7e-84956b0afaa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# load the plss shapefile (these only include TRS areas that are within the San Joaquin subbasin)\n",
    "plss = gpd.read_file(\"./data/plss_subbasin.geojson\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2fb998c7-71b1-41b5-a432-3fe1c291a75d",
   "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>OBJECTID</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Meridian</th>\n",
       "      <th>Source</th>\n",
       "      <th>Section</th>\n",
       "      <th>MTRS</th>\n",
       "      <th>TownshipRange</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3656</th>\n",
       "      <td>26230</td>\n",
       "      <td>T07S</td>\n",
       "      <td>R15E</td>\n",
       "      <td>MDM</td>\n",
       "      <td>BLM</td>\n",
       "      <td>17</td>\n",
       "      <td>MDM-T07S-R15E-17</td>\n",
       "      <td>T07S R15E</td>\n",
       "      <td>MULTIPOLYGON (((-120.34246 37.33193, -120.3603...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      OBJECTID Township Range Meridian Source  Section              MTRS  \\\n",
       "3656     26230     T07S  R15E      MDM    BLM       17  MDM-T07S-R15E-17   \n",
       "\n",
       "     TownshipRange                                           geometry  \n",
       "3656     T07S R15E  MULTIPOLYGON (((-120.34246 37.33193, -120.3603...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plss.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2f9cade8-07b9-4216-a824-f070bbc87889",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create merged range geofile\n",
    "plss_range = plss.dissolve(by='TownshipRange').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4cabe3ed-f0a2-45a5-94e3-d2a68b63b85b",
   "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>TownshipRange</th>\n",
       "      <th>geometry</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Meridian</th>\n",
       "      <th>Source</th>\n",
       "      <th>Section</th>\n",
       "      <th>MTRS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>T02N R11E</td>\n",
       "      <td>POLYGON ((-120.79782 37.98835, -120.80247 37.9...</td>\n",
       "      <td>11497</td>\n",
       "      <td>T02N</td>\n",
       "      <td>R11E</td>\n",
       "      <td>MDM</td>\n",
       "      <td>BLM</td>\n",
       "      <td>31</td>\n",
       "      <td>MDM-T02N-R11E-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TownshipRange                                           geometry  OBJECTID  \\\n",
       "31     T02N R11E  POLYGON ((-120.79782 37.98835, -120.80247 37.9...     11497   \n",
       "\n",
       "   Township Range Meridian Source  Section              MTRS  \n",
       "31     T02N  R11E      MDM    BLM       31  MDM-T02N-R11E-31  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "plss_range.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aca59c14-3118-4a1d-b4ac-7afc190f9ad7",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Well completion reports"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce049d99-89d0-4ab0-9296-58f4f153b0f2",
   "metadata": {},
   "source": [
    "https://data.cnra.ca.gov/dataset/well-completion-reports"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "262ddc8a-c0b8-46a0-967f-9aafc2a119d9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Get the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c42505e4-4526-43b6-bc1d-db044a96b85a",
   "metadata": {},
   "outputs": [],
   "source": [
    "completion_api = requests.get('https://data.cnra.ca.gov/api/3/action/datastore_search?resource_id=8da7b93b-4e69-495d-9caa-335691a1896b&limit=32000').json()\n",
    "data_completion = completion_api['result']['records']\n",
    "while completion_api['result']['records']:\n",
    "    completion_api = requests.get('https://data.cnra.ca.gov'+completion_api['result']['_links'][\"next\"]).json()\n",
    "    data_completion.extend(completion_api['result']['records'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a7596199-54a8-4bf4-8054-c2bde179d300",
   "metadata": {},
   "outputs": [],
   "source": [
    "completion = pd.DataFrame(data=data_completion)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "086f234f-f79d-4f67-aca1-13509a41b816",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1039964"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(completion)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34025bdc-749e-486e-93d6-9d5552de8322",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "04f0efab-2b2e-4417-a98d-4bb038ecf9da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1039964 entries, 0 to 1039963\n",
      "Data columns (total 46 columns):\n",
      " #   Column                        Non-Null Count    Dtype \n",
      "---  ------                        --------------    ----- \n",
      " 0   DECIMALLATITUDE               985331 non-null   object\n",
      " 1   WORKFLOWSTATUS                70667 non-null    object\n",
      " 2   LLACCURACY                    906531 non-null   object\n",
      " 3   PERMITDATE                    71212 non-null    object\n",
      " 4   PUMPTESTLENGTH                65925 non-null    object\n",
      " 5   SECTION                       1004150 non-null  object\n",
      " 6   REGIONOFFICE                  1039346 non-null  object\n",
      " 7   DRILLINGMETHOD                532836 non-null   object\n",
      " 8   OTHEROBSERVATIONS             25519 non-null    object\n",
      " 9   WELLYIELDUNITOFMEASURE        222013 non-null   object\n",
      " 10  WELLLOCATION                  508933 non-null   object\n",
      " 11  PERMITNUMBER                  180103 non-null   object\n",
      " 12  TOTALDRAWDOWN                 39985 non-null    object\n",
      " 13  VERTICALDATUM                 6445 non-null     object\n",
      " 14  BOTTOMOFPERFORATEDINTERVAL    398028 non-null   object\n",
      " 15  GROUNDSURFACEELEVATION        12321 non-null    object\n",
      " 16  STATICWATERLEVEL              203051 non-null   object\n",
      " 17  RECORDTYPE                    1039964 non-null  object\n",
      " 18  DRILLERLICENSENUMBER          858714 non-null   object\n",
      " 19  FLUID                         495348 non-null   object\n",
      " 20  TESTTYPE                      20101 non-null    object\n",
      " 21  APN                           384495 non-null   object\n",
      " 22  PLANNEDUSEFORMERUSE           827183 non-null   object\n",
      " 23  LOCALPERMITAGENCY             1035232 non-null  object\n",
      " 24  TOPOFPERFORATEDINTERVAL       398413 non-null   object\n",
      " 25  WCRNUMBER                     1039964 non-null  object\n",
      " 26  TOTALDRILLDEPTH               52246 non-null    object\n",
      " 27  LEGACYLOGNUMBER               951861 non-null   object\n",
      " 28  ELEVATIONDETERMINATIONMETHOD  9933 non-null     object\n",
      " 29  ELEVATIONACCURACY             9646 non-null     object\n",
      " 30  DECIMALLONGITUDE              985327 non-null   object\n",
      " 31  METHODOFDETERMINATIONLL       906566 non-null   object\n",
      " 32  HORIZONTALDATUM               144141 non-null   object\n",
      " 33  TOWNSHIP                      1012110 non-null  object\n",
      " 34  DATEWORKENDED                 855420 non-null   object\n",
      " 35  CITY                          579366 non-null   object\n",
      " 36  TOTALCOMPLETEDDEPTH           720689 non-null   object\n",
      " 37  OWNERASSIGNEDWELLNUMBER       313320 non-null   object\n",
      " 38  COUNTYNAME                    1038419 non-null  object\n",
      " 39  RANGE                         1011945 non-null  object\n",
      " 40  BASELINEMERIDIAN              1022583 non-null  object\n",
      " 41  RECEIVEDDATE                  79558 non-null    object\n",
      " 42  DRILLERNAME                   858632 non-null   object\n",
      " 43  WELLYIELD                     222013 non-null   object\n",
      " 44  _id                           1039964 non-null  int64 \n",
      " 45  CASINGDIAMETER                566968 non-null   object\n",
      "dtypes: int64(1), object(45)\n",
      "memory usage: 365.0+ MB\n"
     ]
    }
   ],
   "source": [
    "completion.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3cd6a716-63ec-4485-867c-14a0c1f6593f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter to just the columns we are interested in\n",
    "completion_trim = completion[[\"DECIMALLATITUDE\", \"DECIMALLONGITUDE\", \"TOWNSHIP\", \"RANGE\", \"SECTION\", \"WELLLOCATION\", \"CITY\", \"COUNTYNAME\", \n",
    "                              \"PERMITNUMBER\", \"BOTTOMOFPERFORATEDINTERVAL\", \"TOPOFPERFORATEDINTERVAL\", \"GROUNDSURFACEELEVATION\", \"STATICWATERLEVEL\", \n",
    "                              \"RECORDTYPE\",  \"PLANNEDUSEFORMERUSE\", \"LOCALPERMITAGENCY\", \"WCRNUMBER\", \"TOTALDRILLDEPTH\", \n",
    "                              \"TOTALCOMPLETEDDEPTH\", \"DATEWORKENDED\", \"DRILLERNAME\", \"DRILLERLICENSENUMBER\", \"CASINGDIAMETER\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "33ff6466-e771-4a90-a49d-66422bfee310",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# rename columns\n",
    "completion_trim.rename(columns={\"DECIMALLATITUDE\" : \"latitude\", \n",
    "                                \"DECIMALLONGITUDE\" : \"longitude\", \n",
    "                                \"SECTION\" : \"section\", \n",
    "                                \"TOWNSHIP\" : \"township\", \n",
    "                                \"RANGE\" : \"range\", \n",
    "                                \"WELLLOCATION\" : \"location\", \n",
    "                                \"CITY\" : \"city\", \n",
    "                                \"COUNTYNAME\" : \"county\", \n",
    "                                \"PERMITNUMBER\" : \"permit_no\", \n",
    "                                \"BOTTOMOFPERFORATEDINTERVAL\" : \"bottom_perf_interval\", \n",
    "                                \"TOPOFPERFORATEDINTERVAL\" : \"top_perf_interval\", \n",
    "                                \"GROUNDSURFACEELEVATION\" : \"gse\", \n",
    "                                \"STATICWATERLEVEL\" : \"static_water_level\", \n",
    "                                \"RECORDTYPE\" : \"type\", \n",
    "                                \"PLANNEDUSEFORMERUSE\" : \"use\", \n",
    "                                \"LOCALPERMITAGENCY\" : \"permit_agency\", \n",
    "                                \"WCRNUMBER\" : \"wcr_no\", \n",
    "                                \"TOTALDRILLDEPTH\" : \"drill_depth\", \n",
    "                                \"TOTALCOMPLETEDDEPTH\" : \"completed_depth\", \n",
    "                                \"DATEWORKENDED\" : \"date_work_ended\", \n",
    "                                \"DRILLERNAME\" : \"driller\", \n",
    "                                \"DRILLERLICENSENUMBER\": \"driller_license_no\",\n",
    "                                \"CASINGDIAMETER\" : \"casing_diameter\"\n",
    "                               }, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "67b9e297-e34d-4102-b766-5acaa3ba31bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter to only include new well completion\n",
    "completion_trim = completion_trim.loc[completion_trim['type'] == 'WellCompletion/New/Production or Monitoring/NA']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "750d1328-3027-40e2-ba56-94c3089e5be2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter to only include agriculture, domestic, or public wells\n",
    "completion_trim['use'] = completion_trim['use'].apply(lambda x: \"\" if x is None else x)\n",
    "completion_trim['use'] = completion_trim['use'].apply(lambda x: \"agriculture\" if \"Agri\" in x else \n",
    "                                                                \"domestic\" if \"Domestic\" in x else\n",
    "                                                                \"public\" if \"Public\" in x else \"Other\")\n",
    "completion_trim = completion_trim[completion_trim[\"use\"].isin([\"agriculture\",\"domestic\",\"public\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3bbd6209-b786-4a54-9b52-4aafe292f3bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert depth to number\n",
    "completion_trim['completed_depth'] = pd.to_numeric(completion_trim['completed_depth'], errors=\"coerce\")\n",
    "# removes depth data that is incorrect in new column\n",
    "completion_trim['completed_depth_updated'] = completion_trim['completed_depth'].apply(lambda x: x if x >= 20 else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "34a8372d-d14e-40c3-a20c-086a84d57be9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert date work ended to datetime and filter to only include completed dates that are possible (not a future date) \n",
    "completion_trim['date_work_ended'] = pd.to_datetime(completion_trim['date_work_ended'], errors='coerce')\n",
    "completion_trim['date_work_ended_updated'] = completion_trim['date_work_ended'].apply(lambda x: x if x < datetime.now() else np.nan)\n",
    "# create simple year and month columns\n",
    "completion_trim['year_work_ended'] = pd.DatetimeIndex(completion_trim['date_work_ended_updated']).year\n",
    "completion_trim['month_work_ended'] = pd.DatetimeIndex(completion_trim['date_work_ended_updated']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1fa13117-cca6-4f56-b35c-1132b7aa115f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean longitude/latitude data\n",
    "completion_trim['longitude'] = completion_trim['longitude'].replace(to_replace= r'\\/', value= '', regex=True)\n",
    "completion_trim['latitude'] = completion_trim['latitude'].replace(to_replace= r'\\/', value= '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ac11cf25-e565-407c-bebe-12ae529a4c8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create wells geodataframe\n",
    "gdf_wells = gpd.GeoDataFrame(completion_trim, geometry=gpd.points_from_xy(completion_trim.longitude, completion_trim.latitude))\n",
    "gdf_wells = gdf_wells.dropna(subset=['latitude','longitude'])\n",
    "gdf_wells = gdf_wells.set_crs('epsg:4326')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a6f6af44-4226-4667-a11c-7075907d837d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# match up based on longitude/latitude\n",
    "join_wells_plss = gdf_wells.sjoin(plss, how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "4574c2fd-0846-473c-9c91-45017e740040",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the ones that aren't in the san joaquin valley basin\n",
    "join_wells_plss = join_wells_plss.dropna(subset=['MTRS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "32f33c04-f057-4b00-81d2-6da91181f6a5",
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>township</th>\n",
       "      <th>range</th>\n",
       "      <th>section</th>\n",
       "      <th>location</th>\n",
       "      <th>city</th>\n",
       "      <th>county</th>\n",
       "      <th>permit_no</th>\n",
       "      <th>bottom_perf_interval</th>\n",
       "      <th>...</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Meridian</th>\n",
       "      <th>Source</th>\n",
       "      <th>Section</th>\n",
       "      <th>MTRS</th>\n",
       "      <th>TownshipRange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>179139</th>\n",
       "      <td>36.85971</td>\n",
       "      <td>-119.70205</td>\n",
       "      <td>12S</td>\n",
       "      <td>21E</td>\n",
       "      <td>29</td>\n",
       "      <td>4891 E TEAGUE</td>\n",
       "      <td>CLOVIS</td>\n",
       "      <td>Fresno</td>\n",
       "      <td>None</td>\n",
       "      <td>110</td>\n",
       "      <td>...</td>\n",
       "      <td>POINT (-119.70205 36.85971)</td>\n",
       "      <td>5797.0</td>\n",
       "      <td>42159.0</td>\n",
       "      <td>T12S</td>\n",
       "      <td>R21E</td>\n",
       "      <td>MDM</td>\n",
       "      <td>BLM</td>\n",
       "      <td>29.0</td>\n",
       "      <td>MDM-T12S-R21E-29</td>\n",
       "      <td>T12S R21E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 37 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        latitude   longitude township range section       location    city  \\\n",
       "179139  36.85971  -119.70205      12S   21E      29  4891 E TEAGUE  CLOVIS   \n",
       "\n",
       "        county permit_no bottom_perf_interval  ...  \\\n",
       "179139  Fresno      None                  110  ...   \n",
       "\n",
       "                           geometry index_right OBJECTID Township Range  \\\n",
       "179139  POINT (-119.70205 36.85971)      5797.0  42159.0     T12S  R21E   \n",
       "\n",
       "       Meridian Source Section              MTRS TownshipRange  \n",
       "179139      MDM    BLM    29.0  MDM-T12S-R21E-29     T12S R21E  \n",
       "\n",
       "[1 rows x 37 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_wells_plss.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d4d50e00-8464-46a6-81e9-d59ee354c0c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save dataframe\n",
    "join_wells_plss.to_csv(\"./data/well_completion.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8d06dbb-a980-4bf5-931c-6b7df11f1f53",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Shortage reports\n",
    "https://data.ca.gov/dataset/household-water-supply-shortage-reporting-system-data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eee2ec40-3382-474f-869c-f3eb9c0921e1",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Get the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "82549430-27b7-4af0-a6bd-335756b17764",
   "metadata": {},
   "outputs": [],
   "source": [
    "shortage_api = requests.get('https://data.cnra.ca.gov/api/3/action/datastore_search?resource_id=e1fd9f48-a613-4567-8042-3d2e064d77c8&limit=32000').json()\n",
    "data_shortage = shortage_api['result']['records']\n",
    "while shortage_api['result']['records']:\n",
    "    shortage_api = requests.get('https://data.cnra.ca.gov'+shortage_api['result']['_links'][\"next\"]).json()\n",
    "    data_shortage.extend(shortage_api['result']['records'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "80c4e685-6369-45ec-9820-632ab8a24269",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_shortage = pd.DataFrame(data=data_shortage)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "079cb534-fd08-4c95-b915-5d98f45dbae8",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ebc2bf8d-ea0f-4dc1-8afc-cde30ca57e88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3753"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_shortage)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "fc4c7b0c-27a5-4741-a574-75a1558623c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert date to date time\n",
    "df_shortage['CreateDateUpdated'] = pd.to_datetime(df_shortage['CREATE DATE'], errors='coerce')\n",
    "\n",
    "# Create a simple year and month that report was created\n",
    "df_shortage['CreateDateYear'] = pd.DatetimeIndex(df_shortage['CreateDateUpdated']).year\n",
    "df_shortage['CreateDateMonth'] = pd.DatetimeIndex(df_shortage['CreateDateUpdated']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e42abe92-2e2c-42cf-95f6-0e4b0aee8a5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_shortage = df_shortage.dropna(subset=['LATITUDE','LONGITUDE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0b49848e-4ed5-40aa-a8cb-543b7148dfc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_shortage = gpd.GeoDataFrame(df_shortage, geometry=gpd.points_from_xy(df_shortage.LONGITUDE, df_shortage.LATITUDE))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "cec8dd15-452e-4ccc-8c79-2ec1b511da52",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_shortage = gdf_shortage.set_crs('epsg:4326')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "a84036ad-ee74-42a0-83cb-898361d3ef81",
   "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>Well Depth</th>\n",
       "      <th>StatusType</th>\n",
       "      <th>Status</th>\n",
       "      <th>Household Support</th>\n",
       "      <th>Pump Rate Reduction</th>\n",
       "      <th>Water Issues</th>\n",
       "      <th>Shortage Type</th>\n",
       "      <th>Was Issue Resolved?</th>\n",
       "      <th>CREATE DATE</th>\n",
       "      <th>Approximate Issue Start Date</th>\n",
       "      <th>...</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Meridian</th>\n",
       "      <th>Source</th>\n",
       "      <th>Section</th>\n",
       "      <th>MTRS</th>\n",
       "      <th>TownshipRange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2211</th>\n",
       "      <td>Dont know</td>\n",
       "      <td>Resolved</td>\n",
       "      <td>Resolved</td>\n",
       "      <td>None</td>\n",
       "      <td>Not sure</td>\n",
       "      <td>Reduction in water pressure, lower flows.\\r\\n,...</td>\n",
       "      <td>None</td>\n",
       "      <td>Yes, lowered the pump bowl\\r\\n</td>\n",
       "      <td>08/26/2020</td>\n",
       "      <td>08/06/2020</td>\n",
       "      <td>...</td>\n",
       "      <td>POINT (-122.35051 41.60554)</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>1 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Well Depth StatusType    Status Household Support Pump Rate Reduction  \\\n",
       "2211  Dont know   Resolved  Resolved              None            Not sure   \n",
       "\n",
       "                                           Water Issues Shortage Type  \\\n",
       "2211  Reduction in water pressure, lower flows.\\r\\n,...          None   \n",
       "\n",
       "                 Was Issue Resolved? CREATE DATE Approximate Issue Start Date  \\\n",
       "2211  Yes, lowered the pump bowl\\r\\n  08/26/2020                   08/06/2020   \n",
       "\n",
       "      ...                     geometry index_right OBJECTID  Township Range  \\\n",
       "2211  ...  POINT (-122.35051 41.60554)         NaN      NaN       NaN   NaN   \n",
       "\n",
       "     Meridian Source Section MTRS TownshipRange  \n",
       "2211      NaN    NaN     NaN  NaN           NaN  \n",
       "\n",
       "[1 rows x 36 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_shortage_plss = gdf_shortage.sjoin(plss, how=\"left\")\n",
    "join_shortage_plss.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c3662ed9-1150-4f28-86c9-c19af1014305",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the ones that aren't in a subbasin trs\n",
    "join_shortage_plss = join_shortage_plss.dropna(subset=['MTRS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "7ac60393-2a5c-4135-a469-c5b54d73f007",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2519"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(join_shortage_plss)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "bf6fe800-d891-4a1d-a906-635030507722",
   "metadata": {},
   "outputs": [],
   "source": [
    "# save dataframe\n",
    "join_shortage_plss.to_csv(\"./data/shortages.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3781dd53-2cec-4da9-83a5-9d3804d7c722",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Groundwater measurements\n",
    "https://data.cnra.ca.gov/dataset/periodic-groundwater-level-measurements"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38ec9d48-8f87-43f7-8826-a44c8ca5b3f6",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Get the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "af706bb8-c431-4c0f-84ba-dc5818297ba8",
   "metadata": {},
   "outputs": [],
   "source": [
    "measurements_api = requests.get('https://data.cnra.ca.gov/api/3/action/datastore_search?resource_id=bfa9f262-24a1-45bd-8dc8-138bc8107266&limit=32000').json()\n",
    "data_measurements = measurements_api['result']['records']\n",
    "while measurements_api['result']['records']:\n",
    "    measurements_api = requests.get('https://data.cnra.ca.gov'+measurements_api['result']['_links'][\"next\"]).json()\n",
    "    data_measurements.extend(measurements_api['result']['records'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "66abd2d0-2b09-4852-8d9e-177cf8b88a2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# select data from result section\n",
    "df_measurements = pd.DataFrame(data=data_measurements)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "452f010d-25aa-42a2-8d50-f206913c70df",
   "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>GWE</th>\n",
       "      <th>WLM_ORG_NAME</th>\n",
       "      <th>COOP_ORG_NAME</th>\n",
       "      <th>WLM_ACC_DESC</th>\n",
       "      <th>GSE_GWE</th>\n",
       "      <th>MSMT_DATE</th>\n",
       "      <th>SITE_CODE</th>\n",
       "      <th>WLM_GSE</th>\n",
       "      <th>MSMT_CMT</th>\n",
       "      <th>WLM_ID</th>\n",
       "      <th>MONITORING_PROGRAM</th>\n",
       "      <th>WLM_QA_DESC</th>\n",
       "      <th>_id</th>\n",
       "      <th>WLM_DESC</th>\n",
       "      <th>WLM_RPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>694251</th>\n",
       "      <td>193.7</td>\n",
       "      <td>Department of Water Resources</td>\n",
       "      <td>Department of Water Resources</td>\n",
       "      <td>Water level accuracy is unknown</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1970-03-20T00:00:00</td>\n",
       "      <td>353842N1194585W001</td>\n",
       "      <td>272.7</td>\n",
       "      <td>None</td>\n",
       "      <td>734846</td>\n",
       "      <td>VOLUNTARY</td>\n",
       "      <td>None</td>\n",
       "      <td>694198</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>273.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          GWE                   WLM_ORG_NAME                  COOP_ORG_NAME  \\\n",
       "694251  193.7  Department of Water Resources  Department of Water Resources   \n",
       "\n",
       "                           WLM_ACC_DESC  GSE_GWE            MSMT_DATE  \\\n",
       "694251  Water level accuracy is unknown     79.0  1970-03-20T00:00:00   \n",
       "\n",
       "                 SITE_CODE  WLM_GSE MSMT_CMT  WLM_ID MONITORING_PROGRAM  \\\n",
       "694251  353842N1194585W001    272.7     None  734846          VOLUNTARY   \n",
       "\n",
       "       WLM_QA_DESC     _id WLM_DESC  WLM_RPE  \n",
       "694251        None  694198  Unknown    273.7  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_measurements.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "aa56b29a-f05d-44e2-8cfe-2950fa1453d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# stations data has the location info for the measurement wells\n",
    "stations_api = requests.get('https://data.cnra.ca.gov/api/3/action/datastore_search?resource_id=af157380-fb42-4abf-b72a-6f9f98868077&limit=32000').json()\n",
    "data_stations = stations_api['result']['records']\n",
    "while stations_api['result']['records']:\n",
    "    stations_api = requests.get('https://data.cnra.ca.gov'+stations_api['result']['_links'][\"next\"]).json()\n",
    "    data_stations.extend(stations_api['result']['records'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5603a0fb-9d81-45f8-94e4-c28cc48dbcae",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_stations = pd.DataFrame(data=data_stations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "277b2314-cacf-4a80-9975-815c371909f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "simple_station_data = df_stations.loc[:,['SITE_CODE','LONGITUDE','LATITUDE','GSE','COUNTY_NAME','WELL_USE']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "d6dd3633-d11b-40e3-bd49-63627e3a189e",
   "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>LONGITUDE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>GSE</th>\n",
       "      <th>COUNTY_NAME</th>\n",
       "      <th>WELL_USE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2947</th>\n",
       "      <td>334339N1171412W003</td>\n",
       "      <td>-117.141</td>\n",
       "      <td>33.4339</td>\n",
       "      <td>1142.3</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>Unknown</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               SITE_CODE  LONGITUDE  LATITUDE     GSE COUNTY_NAME WELL_USE\n",
       "2947  334339N1171412W003   -117.141   33.4339  1142.3   Riverside  Unknown"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simple_station_data.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4680f6ee-cdf1-46c8-9bbf-0b92b9186288",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "63725bc9-46df-4246-8f35-ab8de1a02498",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create simple year and month columns\n",
    "df_measurements['year'] = pd.DatetimeIndex(df_measurements['MSMT_DATE']).year\n",
    "df_measurements['month'] = pd.DatetimeIndex(df_measurements['MSMT_DATE']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "f584eb4e-84c3-4d1d-8e39-9e6de9ab6753",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter for records that have the ground surface elevation to groundwater elevation measurements\n",
    "df_measurements = df_measurements[~pd.isna(df_measurements['GSE_GWE'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "804a81dd-0830-4b06-ab22-f47185f3f6f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter for just the spring measurements\n",
    "spring = [1,2,3,4]\n",
    "spring_measurements = df_measurements[df_measurements['month'].isin(spring)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "076a8213-5979-463f-873c-8ced550ca7b0",
   "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>GWE</th>\n",
       "      <th>WLM_ORG_NAME</th>\n",
       "      <th>COOP_ORG_NAME</th>\n",
       "      <th>WLM_ACC_DESC</th>\n",
       "      <th>GSE_GWE</th>\n",
       "      <th>MSMT_DATE</th>\n",
       "      <th>SITE_CODE</th>\n",
       "      <th>WLM_GSE</th>\n",
       "      <th>MSMT_CMT</th>\n",
       "      <th>WLM_ID</th>\n",
       "      <th>...</th>\n",
       "      <th>_id</th>\n",
       "      <th>WLM_DESC</th>\n",
       "      <th>WLM_RPE</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>GSE</th>\n",
       "      <th>COUNTY_NAME</th>\n",
       "      <th>WELL_USE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>665194</th>\n",
       "      <td>145.0</td>\n",
       "      <td>Department of Water Resources</td>\n",
       "      <td>Yolo County Flood Control and Water Conservati...</td>\n",
       "      <td>Water level accuracy is unknown</td>\n",
       "      <td>62.6</td>\n",
       "      <td>1990-03-19T00:00:00</td>\n",
       "      <td>387506N1220252W001</td>\n",
       "      <td>207.6</td>\n",
       "      <td>None</td>\n",
       "      <td>2773039</td>\n",
       "      <td>...</td>\n",
       "      <td>1808127</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>208.6</td>\n",
       "      <td>1990</td>\n",
       "      <td>3</td>\n",
       "      <td>-122.025</td>\n",
       "      <td>38.7506</td>\n",
       "      <td>208.79</td>\n",
       "      <td>Yolo</td>\n",
       "      <td>Irrigation</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          GWE                   WLM_ORG_NAME  \\\n",
       "665194  145.0  Department of Water Resources   \n",
       "\n",
       "                                            COOP_ORG_NAME  \\\n",
       "665194  Yolo County Flood Control and Water Conservati...   \n",
       "\n",
       "                           WLM_ACC_DESC  GSE_GWE            MSMT_DATE  \\\n",
       "665194  Water level accuracy is unknown     62.6  1990-03-19T00:00:00   \n",
       "\n",
       "                 SITE_CODE  WLM_GSE MSMT_CMT   WLM_ID  ...      _id WLM_DESC  \\\n",
       "665194  387506N1220252W001    207.6     None  2773039  ...  1808127  Unknown   \n",
       "\n",
       "        WLM_RPE  year  month  LONGITUDE  LATITUDE     GSE  COUNTY_NAME  \\\n",
       "665194    208.6  1990      3   -122.025   38.7506  208.79         Yolo   \n",
       "\n",
       "          WELL_USE  \n",
       "665194  Irrigation  \n",
       "\n",
       "[1 rows x 22 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge with station data for location info\n",
    "measurements_merge = spring_measurements.merge(simple_station_data, on='SITE_CODE')\n",
    "measurements_merge.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "70f18b7f-e27e-47a0-83f9-82b37dcda1c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the rows that have incorrect measurements that of 0 or less\n",
    "measurements_merge = measurements_merge[measurements_merge['GSE_GWE'] > 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d5fad847-ac4a-4600-a3eb-18217ba5c6a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_measurements = gpd.GeoDataFrame(\n",
    "    measurements_merge, \n",
    "    geometry=gpd.points_from_xy(\n",
    "        measurements_merge.LONGITUDE, \n",
    "        measurements_merge.LATITUDE\n",
    "    ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "26815aed-e14e-4135-9700-9d70b4ae091d",
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_measurements = gdf_measurements.set_crs('epsg:4326')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "c20308ae-cbc9-4037-8281-142cd218d8bd",
   "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>GWE</th>\n",
       "      <th>WLM_ORG_NAME</th>\n",
       "      <th>COOP_ORG_NAME</th>\n",
       "      <th>WLM_ACC_DESC</th>\n",
       "      <th>GSE_GWE</th>\n",
       "      <th>MSMT_DATE</th>\n",
       "      <th>SITE_CODE</th>\n",
       "      <th>WLM_GSE</th>\n",
       "      <th>MSMT_CMT</th>\n",
       "      <th>WLM_ID</th>\n",
       "      <th>...</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Meridian</th>\n",
       "      <th>Source</th>\n",
       "      <th>Section</th>\n",
       "      <th>MTRS</th>\n",
       "      <th>TownshipRange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>620036</th>\n",
       "      <td>58.78</td>\n",
       "      <td>Sonoma County PRMD</td>\n",
       "      <td>Sonoma County PRMD</td>\n",
       "      <td>Water level accuracy to nearest tenth of a foot</td>\n",
       "      <td>33.9</td>\n",
       "      <td>2011-01-09T00:00:00</td>\n",
       "      <td>384230N1228158W001</td>\n",
       "      <td>92.68</td>\n",
       "      <td>None</td>\n",
       "      <td>1832050</td>\n",
       "      <td>...</td>\n",
       "      <td>POINT (-122.81600 38.42300)</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>1 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          GWE        WLM_ORG_NAME       COOP_ORG_NAME  \\\n",
       "620036  58.78  Sonoma County PRMD  Sonoma County PRMD   \n",
       "\n",
       "                                           WLM_ACC_DESC  GSE_GWE  \\\n",
       "620036  Water level accuracy to nearest tenth of a foot     33.9   \n",
       "\n",
       "                  MSMT_DATE           SITE_CODE  WLM_GSE MSMT_CMT   WLM_ID  \\\n",
       "620036  2011-01-09T00:00:00  384230N1228158W001    92.68     None  1832050   \n",
       "\n",
       "        ...                     geometry index_right  OBJECTID Township  \\\n",
       "620036  ...  POINT (-122.81600 38.42300)         NaN       NaN      NaN   \n",
       "\n",
       "        Range  Meridian  Source  Section  MTRS  TownshipRange  \n",
       "620036    NaN       NaN     NaN      NaN   NaN            NaN  \n",
       "\n",
       "[1 rows x 32 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# match up based on longitude/latitude\n",
    "join_measurements_plss = gdf_measurements.sjoin(plss, how=\"left\")\n",
    "join_measurements_plss.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "9f667ae4-eca1-4803-8a49-9d8b73d8281f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop the ones that aren't in a subbasin trs\n",
    "join_measurements_plss = join_measurements_plss.dropna(subset=['MTRS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "57c9969c-8ea1-4be3-8749-f9cc68b9ed4b",
   "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>GWE</th>\n",
       "      <th>WLM_ORG_NAME</th>\n",
       "      <th>COOP_ORG_NAME</th>\n",
       "      <th>WLM_ACC_DESC</th>\n",
       "      <th>GSE_GWE</th>\n",
       "      <th>MSMT_DATE</th>\n",
       "      <th>SITE_CODE</th>\n",
       "      <th>WLM_GSE</th>\n",
       "      <th>MSMT_CMT</th>\n",
       "      <th>WLM_ID</th>\n",
       "      <th>...</th>\n",
       "      <th>geometry</th>\n",
       "      <th>index_right</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>Township</th>\n",
       "      <th>Range</th>\n",
       "      <th>Meridian</th>\n",
       "      <th>Source</th>\n",
       "      <th>Section</th>\n",
       "      <th>MTRS</th>\n",
       "      <th>TownshipRange</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>418044</th>\n",
       "      <td>257.95</td>\n",
       "      <td>Department of Water Resources</td>\n",
       "      <td>Panoche Water District</td>\n",
       "      <td>Water level accuracy is unknown</td>\n",
       "      <td>8.95</td>\n",
       "      <td>2006-04-20T00:00:00</td>\n",
       "      <td>367855N1206479W002</td>\n",
       "      <td>266.9</td>\n",
       "      <td>None</td>\n",
       "      <td>3032730</td>\n",
       "      <td>...</td>\n",
       "      <td>POINT (-120.64800 36.78550)</td>\n",
       "      <td>6016.0</td>\n",
       "      <td>44915.0</td>\n",
       "      <td>T13S</td>\n",
       "      <td>R12E</td>\n",
       "      <td>MDM</td>\n",
       "      <td>BLM</td>\n",
       "      <td>22.0</td>\n",
       "      <td>MDM-T13S-R12E-22</td>\n",
       "      <td>T13S R12E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           GWE                   WLM_ORG_NAME           COOP_ORG_NAME  \\\n",
       "418044  257.95  Department of Water Resources  Panoche Water District   \n",
       "\n",
       "                           WLM_ACC_DESC  GSE_GWE            MSMT_DATE  \\\n",
       "418044  Water level accuracy is unknown     8.95  2006-04-20T00:00:00   \n",
       "\n",
       "                 SITE_CODE  WLM_GSE MSMT_CMT   WLM_ID  ...  \\\n",
       "418044  367855N1206479W002    266.9     None  3032730  ...   \n",
       "\n",
       "                           geometry index_right  OBJECTID Township  Range  \\\n",
       "418044  POINT (-120.64800 36.78550)      6016.0   44915.0     T13S   R12E   \n",
       "\n",
       "        Meridian  Source  Section              MTRS  TownshipRange  \n",
       "418044       MDM     BLM     22.0  MDM-T13S-R12E-22      T13S R12E  \n",
       "\n",
       "[1 rows x 32 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_measurements_plss.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "8c8d0fa3-27a7-4731-9473-f7350d750684",
   "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</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>507789</th>\n",
       "      <td>372438N1206341W001</td>\n",
       "      <td>-120.634</td>\n",
       "      <td>37.2438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      site  longitude  latitude\n",
       "507789  372438N1206341W001   -120.634   37.2438"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "site_lon_lat = join_measurements_plss[[\"SITE_CODE\",\"LONGITUDE\",\"LATITUDE\"]]\n",
    "site_lon_lat.columns = [\"site\",\"longitude\",\"latitude\"]\n",
    "site_lon_lat.drop_duplicates(subset=\"site\", inplace=True)\n",
    "site_lon_lat.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "bfa3746e-b355-448d-8401-935d9cb92b81",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group wells that had multiple spring measurements in some years and get the average\n",
    "measurements_plss_group = join_measurements_plss.groupby(['SITE_CODE','MTRS','TownshipRange','COUNTY_NAME','year']).agg({\n",
    "    'GSE_GWE': ['mean'],\n",
    "}).reset_index()\n",
    "measurements_plss_group.columns = ['site','MTRS','TownshipRange','county','year','gse_gwe']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "faa84d09-9145-4abf-b80f-1e6299d89650",
   "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</th>\n",
       "      <th>MTRS</th>\n",
       "      <th>TownshipRange</th>\n",
       "      <th>county</th>\n",
       "      <th>year</th>\n",
       "      <th>gse_gwe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>344779N1192479W001</td>\n",
       "      <td>MDM-T28S-R25E-23</td>\n",
       "      <td>T28S R25E</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2012</td>\n",
       "      <td>254.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>344779N1192479W001</td>\n",
       "      <td>MDM-T28S-R25E-23</td>\n",
       "      <td>T28S R25E</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2013</td>\n",
       "      <td>262.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>344779N1192479W001</td>\n",
       "      <td>MDM-T28S-R25E-23</td>\n",
       "      <td>T28S R25E</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2014</td>\n",
       "      <td>272.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>344779N1192479W001</td>\n",
       "      <td>MDM-T28S-R25E-23</td>\n",
       "      <td>T28S R25E</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2015</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>344779N1192479W001</td>\n",
       "      <td>MDM-T28S-R25E-23</td>\n",
       "      <td>T28S R25E</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2016</td>\n",
       "      <td>292.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 site              MTRS TownshipRange county  year  gse_gwe\n",
       "0  344779N1192479W001  MDM-T28S-R25E-23     T28S R25E   Kern  2012    254.0\n",
       "1  344779N1192479W001  MDM-T28S-R25E-23     T28S R25E   Kern  2013    262.0\n",
       "2  344779N1192479W001  MDM-T28S-R25E-23     T28S R25E   Kern  2014    272.0\n",
       "3  344779N1192479W001  MDM-T28S-R25E-23     T28S R25E   Kern  2015    280.0\n",
       "4  344779N1192479W001  MDM-T28S-R25E-23     T28S R25E   Kern  2016    292.0"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "measurements_plss_group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "e611bc30-418f-4ecf-abfe-1578ccce6fb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# export all measurements for analysis\n",
    "measurements_plss_group.to_csv(\"./data/spring_water_levels.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eff81c1e-afb1-4be8-accc-ff13668f4937",
   "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.6"
  },
  "toc-autonumbering": false,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}