IFT_Step04_determine_IFT_tessman.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Original filename: \"02_tessman_flow_reqts.py\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Paths, input, and output file names\n",
    "\n",
    "outer_dir = os.path.abspath(os.path.join(os.getcwd() ,\"../..\"))\n",
    "refpth = os.path.join(outer_dir, 'IFT_files', 'Reference Files')\n",
    "sfe_char_csv = os.path.join(refpth, 'TNC SFE LOI Characteristics with MAF.csv')\n",
    "sfe_char_xls= os.path.join(refpth, 'TNC SFE LOI Characteristics.xlsx')\n",
    "subset_xls = os.path.join(refpth, 'TNC Subset SFE LOIs.xlsx')\n",
    "#sfe_char_csv = os.path.join(refpth, 'All SFE LOI Characteristics with MAF.csv')\n",
    "#sfe_char_xls= os.path.join(refpth, 'All SFE LOI Characteristics.xlsx')\n",
    "#subset_xls = os.path.join(refpth, 'Subset SFE LOIs.xlsx')\n",
    "unimpath = os.path.join(outer_dir, \"IFT_files\", 'Unimpaired Flow')\n",
    "#tesreqtsfile = os.path.join(refpth, 'Tessmann_min_flows_SWB.csv') #provided Tessmann table\n",
    "\n",
    "startdir = os.path.join(outer_dir, 'IFT_files', 'IFT Results')\n",
    "alldates = pd.date_range(dt.datetime(1950,10,1),dt.datetime(2021,9,30))\n",
    "out_csv = os.path.join(startdir, 'All LOI Tessmann Flow Requirements_TNC.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Original filename: \"00_get_all_sfe_lois.py\"\n",
    "# Read in SFE LOI characteristics table and calculate bankfull flow using cont. Area relation\n",
    "\n",
    "def get_all_sfe_lois():\n",
    "    sfelois = pd.read_csv(sfe_char_csv,index_col=0)\n",
    "    loi = [str(i) for i in sfelois['LOI']]\n",
    "    sfelois['Outlet LOI'] = sfelois['LOI']\n",
    "    sfelois['Contributing Area (mi^2)'] = sfelois['Contributing Area']\n",
    "    sfelois['MAF'] = sfelois['Mean Annual Flow (cfs)']\n",
    "    sfelois['Qbf'] = 71.5 * sfelois['Contributing Area (mi^2)'] #71.5 cfs/mi^2 according to Darren\n",
    "\n",
    "    subset = pd.read_excel(subset_xls)\n",
    "    sublois = sfelois.loc[subset['SWSID'],:]\n",
    "    return sublois, loi, sfelois"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Original filename: \"00_read_loi_paradigm_flow_v3.py\"\n",
    "def read_loi_paradigm_flow(p):\n",
    "    unimpflowfile = os.path.join(unimpath, str(p) + '.csv')\n",
    "    unimp = pd.read_csv(unimpflowfile,index_col=0)\n",
    "    unimp.index = pd.DatetimeIndex(unimp.index)\n",
    "    return unimp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Original filename: \"00_add_ts_col.py\"\n",
    "def add_ts_col(tab):\n",
    "#adds column for TS as first column of DataFrame as formatted date\n",
    "    tab['TS'] = tab.index.strftime('%m/%d/%Y')\n",
    "    cols = tab.columns.tolist()\n",
    "    cols = cols[-1:] + cols[:-1]\n",
    "    tab = tab[cols]\n",
    "    return tab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#tesreqts = pd.read_csv(tesreqtsfile)\n",
    "loitab, loi, fultab = get_all_sfe_lois()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>LOI</th>\n",
       "      <th>dnLOI</th>\n",
       "      <th>Lat</th>\n",
       "      <th>Lon</th>\n",
       "      <th>Contributing Area</th>\n",
       "      <th>Geomorphic Class</th>\n",
       "      <th>Geologic Setting</th>\n",
       "      <th>Mean Annual Flow (cfs)</th>\n",
       "      <th>COMID</th>\n",
       "      <th>Outlet LOI</th>\n",
       "      <th>Contributing Area (mi^2)</th>\n",
       "      <th>MAF</th>\n",
       "      <th>Qbf</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2664961</th>\n",
       "      <td>Indian</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39.486536</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>63.202346</td>\n",
       "      <td>2664961</td>\n",
       "      <td>Indian</td>\n",
       "      <td>39.486536</td>\n",
       "      <td>63.202346</td>\n",
       "      <td>2823.287303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8285498</th>\n",
       "      <td>Sproul</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.992762</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.108696</td>\n",
       "      <td>8285498</td>\n",
       "      <td>Sproul</td>\n",
       "      <td>4.992762</td>\n",
       "      <td>17.108696</td>\n",
       "      <td>356.982496</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            LOI  dnLOI  Lat  Lon  Contributing Area  Geomorphic Class  \\\n",
       "2664961  Indian    NaN  NaN  NaN          39.486536               NaN   \n",
       "8285498  Sproul    NaN  NaN  NaN           4.992762               NaN   \n",
       "\n",
       "         Geologic Setting  Mean Annual Flow (cfs)    COMID Outlet LOI  \\\n",
       "2664961               NaN               63.202346  2664961     Indian   \n",
       "8285498               NaN               17.108696  8285498     Sproul   \n",
       "\n",
       "         Contributing Area (mi^2)        MAF          Qbf  \n",
       "2664961                 39.486536  63.202346  2823.287303  \n",
       "8285498                  4.992762  17.108696   356.982496  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loitab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyreqts = pd.DataFrame(index=alldates,columns=loi)  #set up IFT table for WEAP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Indian\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "\"[Timestamp('2021-03-10 00:00:00'), Timestamp('2021-07-21 00:00:00'), Timestamp('2021-07-22 00:00:00'), Timestamp('2021-07-23 00:00:00')] not in index\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[9], line 41\u001b[0m\n\u001b[0;32m     39\u001b[0m df \u001b[38;5;241m=\u001b[39m df\u001b[38;5;241m.\u001b[39mset_index(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdatetime\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m     40\u001b[0m df\u001b[38;5;241m.\u001b[39mindex \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mDatetimeIndex(df\u001b[38;5;241m.\u001b[39mindex)\n\u001b[1;32m---> 41\u001b[0m dailyreqts\u001b[38;5;241m.\u001b[39mloc[:,l] \u001b[38;5;241m=\u001b[39m \u001b[43mdf\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mloc\u001b[49m\u001b[43m[\u001b[49m\u001b[43mdailyreqts\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mindex\u001b[49m\u001b[43m,\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mTessman flow req\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1067\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1065\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_is_scalar_access(key):\n\u001b[0;32m   1066\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_value(\u001b[38;5;241m*\u001b[39mkey, takeable\u001b[38;5;241m=\u001b[39m\u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_takeable)\n\u001b[1;32m-> 1067\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_getitem_tuple\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1068\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   1069\u001b[0m     \u001b[38;5;66;03m# we by definition only have the 0th axis\u001b[39;00m\n\u001b[0;32m   1070\u001b[0m     axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39maxis \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;241m0\u001b[39m\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1247\u001b[0m, in \u001b[0;36m_LocIndexer._getitem_tuple\u001b[1;34m(self, tup)\u001b[0m\n\u001b[0;32m   1245\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m suppress(IndexingError):\n\u001b[0;32m   1246\u001b[0m     tup \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_expand_ellipsis(tup)\n\u001b[1;32m-> 1247\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_getitem_lowerdim\u001b[49m\u001b[43m(\u001b[49m\u001b[43mtup\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1249\u001b[0m \u001b[38;5;66;03m# no multi-index, so validate all of the indexers\u001b[39;00m\n\u001b[0;32m   1250\u001b[0m tup \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_tuple_indexer(tup)\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:991\u001b[0m, in \u001b[0;36m_LocationIndexer._getitem_lowerdim\u001b[1;34m(self, tup)\u001b[0m\n\u001b[0;32m    989\u001b[0m             \u001b[38;5;28;01mreturn\u001b[39;00m section\n\u001b[0;32m    990\u001b[0m         \u001b[38;5;66;03m# This is an elided recursive call to iloc/loc\u001b[39;00m\n\u001b[1;32m--> 991\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mgetattr\u001b[39;49m\u001b[43m(\u001b[49m\u001b[43msection\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mname\u001b[49m\u001b[43m)\u001b[49m\u001b[43m[\u001b[49m\u001b[43mnew_key\u001b[49m\u001b[43m]\u001b[49m\n\u001b[0;32m    993\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m IndexingError(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnot applicable\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1073\u001b[0m, in \u001b[0;36m_LocationIndexer.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   1070\u001b[0m axis \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39maxis \u001b[38;5;129;01mor\u001b[39;00m \u001b[38;5;241m0\u001b[39m\n\u001b[0;32m   1072\u001b[0m maybe_callable \u001b[38;5;241m=\u001b[39m com\u001b[38;5;241m.\u001b[39mapply_if_callable(key, \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj)\n\u001b[1;32m-> 1073\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_getitem_axis\u001b[49m\u001b[43m(\u001b[49m\u001b[43mmaybe_callable\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43maxis\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1301\u001b[0m, in \u001b[0;36m_LocIndexer._getitem_axis\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1298\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mhasattr\u001b[39m(key, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mndim\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;129;01mand\u001b[39;00m key\u001b[38;5;241m.\u001b[39mndim \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[0;32m   1299\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mCannot index with multidimensional key\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[1;32m-> 1301\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_getitem_iterable\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43maxis\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1303\u001b[0m \u001b[38;5;66;03m# nested tuple slicing\u001b[39;00m\n\u001b[0;32m   1304\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_nested_tuple(key, labels):\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1239\u001b[0m, in \u001b[0;36m_LocIndexer._getitem_iterable\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1236\u001b[0m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_validate_key(key, axis)\n\u001b[0;32m   1238\u001b[0m \u001b[38;5;66;03m# A collection of keys\u001b[39;00m\n\u001b[1;32m-> 1239\u001b[0m keyarr, indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_get_listlike_indexer\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1240\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_reindex_with_indexers(\n\u001b[0;32m   1241\u001b[0m     {axis: [keyarr, indexer]}, copy\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m, allow_dups\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m\n\u001b[0;32m   1242\u001b[0m )\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1432\u001b[0m, in \u001b[0;36m_LocIndexer._get_listlike_indexer\u001b[1;34m(self, key, axis)\u001b[0m\n\u001b[0;32m   1429\u001b[0m ax \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_axis(axis)\n\u001b[0;32m   1430\u001b[0m axis_name \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mobj\u001b[38;5;241m.\u001b[39m_get_axis_name(axis)\n\u001b[1;32m-> 1432\u001b[0m keyarr, indexer \u001b[38;5;241m=\u001b[39m \u001b[43max\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_get_indexer_strict\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis_name\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1434\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m keyarr, indexer\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexes\\base.py:6113\u001b[0m, in \u001b[0;36mIndex._get_indexer_strict\u001b[1;34m(self, key, axis_name)\u001b[0m\n\u001b[0;32m   6110\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[0;32m   6111\u001b[0m     keyarr, indexer, new_indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_reindex_non_unique(keyarr)\n\u001b[1;32m-> 6113\u001b[0m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_raise_if_missing\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkeyarr\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mindexer\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis_name\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   6115\u001b[0m keyarr \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mtake(indexer)\n\u001b[0;32m   6116\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(key, Index):\n\u001b[0;32m   6117\u001b[0m     \u001b[38;5;66;03m# GH 42790 - Preserve name from an Index\u001b[39;00m\n",
      "File \u001b[1;32m~\\Miniconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexes\\base.py:6176\u001b[0m, in \u001b[0;36mIndex._raise_if_missing\u001b[1;34m(self, key, indexer, axis_name)\u001b[0m\n\u001b[0;32m   6173\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mNone of [\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mkey\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m] are in the [\u001b[39m\u001b[38;5;132;01m{\u001b[39;00maxis_name\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m]\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n\u001b[0;32m   6175\u001b[0m not_found \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mlist\u001b[39m(ensure_index(key)[missing_mask\u001b[38;5;241m.\u001b[39mnonzero()[\u001b[38;5;241m0\u001b[39m]]\u001b[38;5;241m.\u001b[39munique())\n\u001b[1;32m-> 6176\u001b[0m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mnot_found\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m not in index\u001b[39m\u001b[38;5;124m\"\u001b[39m)\n",
      "\u001b[1;31mKeyError\u001b[0m: \"[Timestamp('2021-03-10 00:00:00'), Timestamp('2021-07-21 00:00:00'), Timestamp('2021-07-22 00:00:00'), Timestamp('2021-07-23 00:00:00')] not in index\""
     ]
    }
   ],
   "source": [
    "min_days_month = 20\n",
    "min_days_year = 360\n",
    "\n",
    "for l in loi[:]:\n",
    "    print(l)\n",
    "    df = read_loi_paradigm_flow(l)\n",
    "    df['datetime'] = df.index\n",
    "    # Calculate mean monthly flow, screening out months with too much missing data\n",
    "    df_month = (df.groupby(['Water Year', 'Month'])\n",
    "           .agg({'Day':'count', 'flow':'mean'})\n",
    "           .reset_index()\n",
    "           .rename(columns={'Day':'count_days','flow':'monthly_mean_flow'})\n",
    "          )\n",
    "    df_month = df_month.loc[df_month['count_days'] >= min_days_month]\n",
    "    df_mmf = (df_month.groupby(['Month'])\n",
    "           .agg({'Water Year':'count', 'monthly_mean_flow':'mean'})\n",
    "           .reset_index()\n",
    "           .rename(columns={'Water Year':'count_months','monthly_mean_flow':'mean_monthly_flow'})\n",
    "          )\n",
    "    # Calculate mean annual, screening out years with too much missing data\n",
    "    df_wy = (df.groupby(['Water Year'])\n",
    "           .agg({'Day':'count', 'flow':'mean'})\n",
    "           .reset_index()\n",
    "           .rename(columns={'Day':'count_days','flow':'annual_mean_flow'})\n",
    "          )\n",
    "    df_wy = df_wy.loc[df_wy['count_days'] >= min_days_year]\n",
    "    maf = df_wy['annual_mean_flow'].mean()\n",
    "    # Calculate Tessman monthly bypass flows\n",
    "    df_mmf.loc[df_mmf['mean_monthly_flow'] < 0.4 * maf, 'tessman_mbf'] = df_mmf['mean_monthly_flow']\n",
    "    df_mmf.loc[0.4 * df_mmf['mean_monthly_flow'] >= 0.4 * maf, 'tessman_mbf'] = 0.4 * df_mmf['mean_monthly_flow']\n",
    "    df_mmf.loc[(df_mmf['mean_monthly_flow'] >= 0.4 * maf) & (0.4 * df_mmf['mean_monthly_flow'] < 0.4 * maf), 'tessman_mbf'] = 0.4 * maf\n",
    "    # Calculate the hydrograph assuming full diversion\n",
    "    df = pd.merge(df,df_mmf, on=\"Month\", how=\"left\")\n",
    "    max_div_allowed = df.flow.max() # placeholder until we develop a loi specific max diversion amount\n",
    "    df['flow above mbf'] = np.where(df['flow'] > df['tessman_mbf'], df['flow'] - df['tessman_mbf'], 0)\n",
    "    #df.loc[df['flow'] > df['tessman_mbf'], 'flow above mbf'] = df['flow'] - df['tessman_mbf']\n",
    "    df['max diverted'] = np.where(df['flow above mbf']>max_div_allowed, max_div_allowed, df['flow above mbf'])\n",
    "    df['Tessman flow req'] = df['flow'] - df['max diverted']\n",
    "    df = df.set_index('datetime')\n",
    "    df.index = pd.DatetimeIndex(df.index)\n",
    "    dailyreqts.loc[:,l] = df.loc[dailyreqts.index,'Tessman flow req']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>Indian</th>\n",
       "      <th>Sproul</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1950-10-01</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-02</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-03</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-04</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-05</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-06</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-07</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-08</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-09</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-10</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-11</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-12</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-13</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-14</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-15</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-16</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-17</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-18</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-19</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-20</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-21</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-22</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-23</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-24</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-25</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-26</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-27</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-28</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-29</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950-10-30</th>\n",
       "      <td>4.254344</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-01</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-02</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-03</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-04</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-05</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-06</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-07</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-08</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-09</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-10</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-11</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-12</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-13</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-14</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-15</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-16</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-17</th>\n",
       "      <td>0.587153</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-18</th>\n",
       "      <td>0.615753</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-19</th>\n",
       "      <td>0.731453</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-20</th>\n",
       "      <td>0.654753</td>\n",
       "      <td>0.000294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-21</th>\n",
       "      <td>0.607953</td>\n",
       "      <td>0.000294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-22</th>\n",
       "      <td>0.593653</td>\n",
       "      <td>0.000220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-23</th>\n",
       "      <td>0.588453</td>\n",
       "      <td>0.000154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-24</th>\n",
       "      <td>0.588453</td>\n",
       "      <td>0.000098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-25</th>\n",
       "      <td>0.588453</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-26</th>\n",
       "      <td>0.588453</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-27</th>\n",
       "      <td>0.598853</td>\n",
       "      <td>0.000220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-28</th>\n",
       "      <td>0.632653</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-29</th>\n",
       "      <td>0.620953</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-30</th>\n",
       "      <td>0.605353</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25933 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              Indian    Sproul\n",
       "1950-10-01  4.254344       NaN\n",
       "1950-10-02  4.254344       NaN\n",
       "1950-10-03  4.254344       NaN\n",
       "1950-10-04  4.254344       NaN\n",
       "1950-10-05  4.254344       NaN\n",
       "1950-10-06  4.254344       NaN\n",
       "1950-10-07  4.254344       NaN\n",
       "1950-10-08  4.254344       NaN\n",
       "1950-10-09  4.254344       NaN\n",
       "1950-10-10  4.254344       NaN\n",
       "1950-10-11  4.254344       NaN\n",
       "1950-10-12  4.254344       NaN\n",
       "1950-10-13  4.254344       NaN\n",
       "1950-10-14  4.254344       NaN\n",
       "1950-10-15  4.254344       NaN\n",
       "1950-10-16  4.254344       NaN\n",
       "1950-10-17  4.254344       NaN\n",
       "1950-10-18  4.254344       NaN\n",
       "1950-10-19  4.254344       NaN\n",
       "1950-10-20  4.254344       NaN\n",
       "1950-10-21  4.254344       NaN\n",
       "1950-10-22  4.254344       NaN\n",
       "1950-10-23  4.254344       NaN\n",
       "1950-10-24  4.254344       NaN\n",
       "1950-10-25  4.254344       NaN\n",
       "1950-10-26  4.254344       NaN\n",
       "1950-10-27  4.254344       NaN\n",
       "1950-10-28  4.254344       NaN\n",
       "1950-10-29  4.254344       NaN\n",
       "1950-10-30  4.254344       NaN\n",
       "...              ...       ...\n",
       "2021-09-01  0.587153  0.000051\n",
       "2021-09-02  0.587153  0.000051\n",
       "2021-09-03  0.587153  0.000051\n",
       "2021-09-04  0.587153  0.000051\n",
       "2021-09-05  0.587153  0.000013\n",
       "2021-09-06  0.587153  0.000013\n",
       "2021-09-07  0.587153  0.000000\n",
       "2021-09-08  0.587153  0.000000\n",
       "2021-09-09  0.587153  0.000000\n",
       "2021-09-10  0.587153  0.000000\n",
       "2021-09-11  0.587153  0.000000\n",
       "2021-09-12  0.587153  0.000000\n",
       "2021-09-13  0.587153  0.000000\n",
       "2021-09-14  0.587153  0.000000\n",
       "2021-09-15  0.587153  0.000000\n",
       "2021-09-16  0.587153  0.000000\n",
       "2021-09-17  0.587153  0.000000\n",
       "2021-09-18  0.615753  0.000378\n",
       "2021-09-19  0.731453  0.000378\n",
       "2021-09-20  0.654753  0.000294\n",
       "2021-09-21  0.607953  0.000294\n",
       "2021-09-22  0.593653  0.000220\n",
       "2021-09-23  0.588453  0.000154\n",
       "2021-09-24  0.588453  0.000098\n",
       "2021-09-25  0.588453  0.000051\n",
       "2021-09-26  0.588453  0.000013\n",
       "2021-09-27  0.598853  0.000220\n",
       "2021-09-28  0.632653  0.000378\n",
       "2021-09-29  0.620953  0.000378\n",
       "2021-09-30  0.605353  0.000378\n",
       "\n",
       "[25933 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "dailyreqts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyreqts = add_ts_col(dailyreqts)\n",
    "dailyreqts.to_csv(out_csv)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (freshwater)",
   "language": "python",
   "name": "freshwater"
  },
  "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.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}