IFT_Step03_determine_IFT_ncifp.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Original filename: \"02_ncifp_flow_reqts_v2.py\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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": 48,
   "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",
    "\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 NCIFP IFTs_TNC.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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": 50,
   "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": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate bankful flow (Qbf) using the 1.5 year recurrance interval according to NCIFP\n",
    "def rec_int_flow(p,ri):\n",
    "    '''Function to estimate the closest max flow value for a given recurrence interval'''\n",
    "    df_daily = read_loi_paradigm_flow(p)\n",
    "    # Calculate max flow by water year\n",
    "    df = (df_daily.groupby('Water Year')\n",
    "               .agg({'flow':'max', 'Day':'count'})\n",
    "               .reset_index()\n",
    "               .rename(columns={'flow':'max_flow', 'Day':'day_count'})\n",
    "              )\n",
    "    # Remove partial years\n",
    "    df = df.loc[df['day_count'] >= 365]\n",
    "    # Calculate recurrence interval\n",
    "    df = df.sort_values(by=['max_flow'], ascending=[False]).reset_index()\n",
    "    df['flow_rank'] = df['max_flow'].rank(ascending = False)\n",
    "    num_years = len(df)\n",
    "    df['prob_exceedence'] = df.flow_rank / (num_years + 1)\n",
    "    df['recurrence_interval'] = (num_years + 1) / df.flow_rank\n",
    "    # Return flow for specified recurrence interval\n",
    "    row_num = df['recurrence_interval'].sub(ri).abs().idxmin()\n",
    "    Q = df.iloc[row_num]['max_flow']\n",
    "    return Q\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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",
    "    sfelois['Qbf'] = sfelois.apply(lambda row : rec_int_flow(str(row['LOI']),1.5), axis = 1)\n",
    "    subset = pd.read_excel(subset_xls)\n",
    "    sublois = sfelois.loc[subset['SWSID'],:]\n",
    "    return sublois, loi, sfelois"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "loitab, loi, fultab = get_all_sfe_lois()\n",
    "loitab = fultab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>1105.039921</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>201.685417</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  1105.039921  \n",
       "8285498                  4.992762  17.108696   201.685417  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loitab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyreqts = pd.DataFrame(index=alldates,columns=loi)  #set up IFT table for WEAP\n",
    "loitab['NCIFP MBF'] = np.zeros_like(loitab['MAF']) #initialize minimum bypass flow metric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MBF for Indian is: 98.82909621545929\n",
      "MBF for Sproul is: 70.7097621188737\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\Users\\kklausmeyer\\AppData\\Local\\Continuum\\anaconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1027: FutureWarning: \n",
      "Passing list-likes to .loc or [] with any missing label will raise\n",
      "KeyError in the future, you can use .reindex() as an alternative.\n",
      "\n",
      "See the documentation here:\n",
      "https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike\n",
      "  return getattr(section, self.name)[new_key]\n",
      "d:\\Users\\kklausmeyer\\AppData\\Local\\Continuum\\anaconda3\\envs\\freshwater\\lib\\site-packages\\pandas\\core\\indexing.py:1027: FutureWarning: \n",
      "Passing list-likes to .loc or [] with any missing label will raise\n",
      "KeyError in the future, you can use .reindex() as an alternative.\n",
      "\n",
      "See the documentation here:\n",
      "https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike\n",
      "  return getattr(section, self.name)[new_key]\n"
     ]
    }
   ],
   "source": [
    "for l in loi: #loop through lcoations\n",
    "    loimaf = loitab.loc[loitab.LOI == l,'MAF'].values[0] #extract LOI mean annual flow\n",
    "    loicta = loitab.loc[loitab.LOI == l,'Contributing Area (mi^2)'].values[0] #extract LOI contributing area\n",
    "    loiqbf = loitab.loc[loitab.LOI == l,'Qbf'].values[0] #extract LOI bankfull flow\n",
    "    loistr = l\n",
    "    unimp = read_loi_paradigm_flow(loistr)\n",
    "    #NCIFP calculations based on bin of Contributing area\n",
    "    if loicta <= 1:\n",
    "        loimbf = 9 * loimaf\n",
    "    elif (loicta > 1) & (loicta < 321):\n",
    "        loimbf = 8.8 * loimaf * loicta ** -0.47\n",
    "    else:\n",
    "        loimbf = 0.6 * loimaf\n",
    "    print(\"MBF for {} is: {}\".format(l,loimbf))\n",
    "    loidiv = loiqbf*0.05 #diversion allocation is 5% of bankfull\n",
    "    loitab.loc[loitab.LOI == l,'NCIFP MBF'] = loimbf #record MBF\n",
    "    unimp['NCIFP div aloc'] = np.zeros_like(unimp['flow'])\n",
    "    unimp['flow above mbf'] = unimp['flow']-loimbf #calculate flow above MBF\n",
    "    unimp.loc[unimp['flow above mbf']>loidiv,'flow above mbf'] = loidiv #allow full diversion amount when there is more than enough flow to divert\n",
    "    #get index of diversion period\n",
    "    divinds = (unimp['flow'] > loimbf) & ((unimp.index.month <= 3) | ((unimp.index.day >= 15) & (unimp.index.month == 12)))\n",
    "    unimp.loc[divinds,'NCIFP div aloc'] = unimp.loc[divinds,'flow above mbf'] #loidiv\n",
    "    unimp['NCIFP flow req'] = unimp['flow'] - unimp['NCIFP div aloc'] #IFT = flow - diversion\n",
    "    dailyreqts.loc[:,loistr] = unimp.loc[dailyreqts.index,'NCIFP flow req']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Water Year</th>\n",
       "      <th>flow</th>\n",
       "      <th>NCIFP div aloc</th>\n",
       "      <th>flow above mbf</th>\n",
       "      <th>NCIFP flow req</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DTTM</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1960-10-01</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-02</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-03</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-04</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-05</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-06</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-07</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-08</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-09</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>9</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-10</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.692813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.016950</td>\n",
       "      <td>0.692813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-11</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-12</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-13</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>13</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-14</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-15</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-16</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-17</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-18</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>18</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-19</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>19</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-20</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-21</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>21</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-22</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>22</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-23</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>23</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-24</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>24</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-25</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-26</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>26</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-27</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>27</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-28</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>28</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-29</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>29</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-10-30</th>\n",
       "      <td>1960</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>1961</td>\n",
       "      <td>0.615833</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.093929</td>\n",
       "      <td>0.615833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-01</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709711</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-02</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709711</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-03</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709711</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-04</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709711</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-05</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000013</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709750</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-06</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000013</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709750</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-07</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-08</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-09</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-10</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-11</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-12</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-13</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>13</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-14</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-15</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-16</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>16</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-17</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709762</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-18</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>18</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000378</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709385</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-19</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>19</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000378</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709385</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-20</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>20</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000294</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709468</td>\n",
       "      <td>0.000294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-21</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>21</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000294</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709468</td>\n",
       "      <td>0.000294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-22</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>22</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000220</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709543</td>\n",
       "      <td>0.000220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-23</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>23</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000154</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709608</td>\n",
       "      <td>0.000154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-24</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>24</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000098</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709664</td>\n",
       "      <td>0.000098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-25</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>25</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709711</td>\n",
       "      <td>0.000051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-26</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>26</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000013</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709750</td>\n",
       "      <td>0.000013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-27</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>27</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000220</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709543</td>\n",
       "      <td>0.000220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-28</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>28</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000378</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709385</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-29</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000378</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709385</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-09-30</th>\n",
       "      <td>2021</td>\n",
       "      <td>9</td>\n",
       "      <td>30</td>\n",
       "      <td>2021</td>\n",
       "      <td>0.000378</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-70.709385</td>\n",
       "      <td>0.000378</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21792 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Year  Month  Day  Water Year      flow  NCIFP div aloc  \\\n",
       "DTTM                                                                 \n",
       "1960-10-01  1960     10    1        1961  0.692813             0.0   \n",
       "1960-10-02  1960     10    2        1961  0.692813             0.0   \n",
       "1960-10-03  1960     10    3        1961  0.692813             0.0   \n",
       "1960-10-04  1960     10    4        1961  0.692813             0.0   \n",
       "1960-10-05  1960     10    5        1961  0.692813             0.0   \n",
       "1960-10-06  1960     10    6        1961  0.692813             0.0   \n",
       "1960-10-07  1960     10    7        1961  0.692813             0.0   \n",
       "1960-10-08  1960     10    8        1961  0.692813             0.0   \n",
       "1960-10-09  1960     10    9        1961  0.692813             0.0   \n",
       "1960-10-10  1960     10   10        1961  0.692813             0.0   \n",
       "1960-10-11  1960     10   11        1961  0.615833             0.0   \n",
       "1960-10-12  1960     10   12        1961  0.615833             0.0   \n",
       "1960-10-13  1960     10   13        1961  0.615833             0.0   \n",
       "1960-10-14  1960     10   14        1961  0.615833             0.0   \n",
       "1960-10-15  1960     10   15        1961  0.615833             0.0   \n",
       "1960-10-16  1960     10   16        1961  0.615833             0.0   \n",
       "1960-10-17  1960     10   17        1961  0.615833             0.0   \n",
       "1960-10-18  1960     10   18        1961  0.615833             0.0   \n",
       "1960-10-19  1960     10   19        1961  0.615833             0.0   \n",
       "1960-10-20  1960     10   20        1961  0.615833             0.0   \n",
       "1960-10-21  1960     10   21        1961  0.615833             0.0   \n",
       "1960-10-22  1960     10   22        1961  0.615833             0.0   \n",
       "1960-10-23  1960     10   23        1961  0.615833             0.0   \n",
       "1960-10-24  1960     10   24        1961  0.615833             0.0   \n",
       "1960-10-25  1960     10   25        1961  0.615833             0.0   \n",
       "1960-10-26  1960     10   26        1961  0.615833             0.0   \n",
       "1960-10-27  1960     10   27        1961  0.615833             0.0   \n",
       "1960-10-28  1960     10   28        1961  0.615833             0.0   \n",
       "1960-10-29  1960     10   29        1961  0.615833             0.0   \n",
       "1960-10-30  1960     10   30        1961  0.615833             0.0   \n",
       "...          ...    ...  ...         ...       ...             ...   \n",
       "2021-09-01  2021      9    1        2021  0.000051             0.0   \n",
       "2021-09-02  2021      9    2        2021  0.000051             0.0   \n",
       "2021-09-03  2021      9    3        2021  0.000051             0.0   \n",
       "2021-09-04  2021      9    4        2021  0.000051             0.0   \n",
       "2021-09-05  2021      9    5        2021  0.000013             0.0   \n",
       "2021-09-06  2021      9    6        2021  0.000013             0.0   \n",
       "2021-09-07  2021      9    7        2021  0.000000             0.0   \n",
       "2021-09-08  2021      9    8        2021  0.000000             0.0   \n",
       "2021-09-09  2021      9    9        2021  0.000000             0.0   \n",
       "2021-09-10  2021      9   10        2021  0.000000             0.0   \n",
       "2021-09-11  2021      9   11        2021  0.000000             0.0   \n",
       "2021-09-12  2021      9   12        2021  0.000000             0.0   \n",
       "2021-09-13  2021      9   13        2021  0.000000             0.0   \n",
       "2021-09-14  2021      9   14        2021  0.000000             0.0   \n",
       "2021-09-15  2021      9   15        2021  0.000000             0.0   \n",
       "2021-09-16  2021      9   16        2021  0.000000             0.0   \n",
       "2021-09-17  2021      9   17        2021  0.000000             0.0   \n",
       "2021-09-18  2021      9   18        2021  0.000378             0.0   \n",
       "2021-09-19  2021      9   19        2021  0.000378             0.0   \n",
       "2021-09-20  2021      9   20        2021  0.000294             0.0   \n",
       "2021-09-21  2021      9   21        2021  0.000294             0.0   \n",
       "2021-09-22  2021      9   22        2021  0.000220             0.0   \n",
       "2021-09-23  2021      9   23        2021  0.000154             0.0   \n",
       "2021-09-24  2021      9   24        2021  0.000098             0.0   \n",
       "2021-09-25  2021      9   25        2021  0.000051             0.0   \n",
       "2021-09-26  2021      9   26        2021  0.000013             0.0   \n",
       "2021-09-27  2021      9   27        2021  0.000220             0.0   \n",
       "2021-09-28  2021      9   28        2021  0.000378             0.0   \n",
       "2021-09-29  2021      9   29        2021  0.000378             0.0   \n",
       "2021-09-30  2021      9   30        2021  0.000378             0.0   \n",
       "\n",
       "            flow above mbf  NCIFP flow req  \n",
       "DTTM                                        \n",
       "1960-10-01      -70.016950        0.692813  \n",
       "1960-10-02      -70.016950        0.692813  \n",
       "1960-10-03      -70.016950        0.692813  \n",
       "1960-10-04      -70.016950        0.692813  \n",
       "1960-10-05      -70.016950        0.692813  \n",
       "1960-10-06      -70.016950        0.692813  \n",
       "1960-10-07      -70.016950        0.692813  \n",
       "1960-10-08      -70.016950        0.692813  \n",
       "1960-10-09      -70.016950        0.692813  \n",
       "1960-10-10      -70.016950        0.692813  \n",
       "1960-10-11      -70.093929        0.615833  \n",
       "1960-10-12      -70.093929        0.615833  \n",
       "1960-10-13      -70.093929        0.615833  \n",
       "1960-10-14      -70.093929        0.615833  \n",
       "1960-10-15      -70.093929        0.615833  \n",
       "1960-10-16      -70.093929        0.615833  \n",
       "1960-10-17      -70.093929        0.615833  \n",
       "1960-10-18      -70.093929        0.615833  \n",
       "1960-10-19      -70.093929        0.615833  \n",
       "1960-10-20      -70.093929        0.615833  \n",
       "1960-10-21      -70.093929        0.615833  \n",
       "1960-10-22      -70.093929        0.615833  \n",
       "1960-10-23      -70.093929        0.615833  \n",
       "1960-10-24      -70.093929        0.615833  \n",
       "1960-10-25      -70.093929        0.615833  \n",
       "1960-10-26      -70.093929        0.615833  \n",
       "1960-10-27      -70.093929        0.615833  \n",
       "1960-10-28      -70.093929        0.615833  \n",
       "1960-10-29      -70.093929        0.615833  \n",
       "1960-10-30      -70.093929        0.615833  \n",
       "...                    ...             ...  \n",
       "2021-09-01      -70.709711        0.000051  \n",
       "2021-09-02      -70.709711        0.000051  \n",
       "2021-09-03      -70.709711        0.000051  \n",
       "2021-09-04      -70.709711        0.000051  \n",
       "2021-09-05      -70.709750        0.000013  \n",
       "2021-09-06      -70.709750        0.000013  \n",
       "2021-09-07      -70.709762        0.000000  \n",
       "2021-09-08      -70.709762        0.000000  \n",
       "2021-09-09      -70.709762        0.000000  \n",
       "2021-09-10      -70.709762        0.000000  \n",
       "2021-09-11      -70.709762        0.000000  \n",
       "2021-09-12      -70.709762        0.000000  \n",
       "2021-09-13      -70.709762        0.000000  \n",
       "2021-09-14      -70.709762        0.000000  \n",
       "2021-09-15      -70.709762        0.000000  \n",
       "2021-09-16      -70.709762        0.000000  \n",
       "2021-09-17      -70.709762        0.000000  \n",
       "2021-09-18      -70.709385        0.000378  \n",
       "2021-09-19      -70.709385        0.000378  \n",
       "2021-09-20      -70.709468        0.000294  \n",
       "2021-09-21      -70.709468        0.000294  \n",
       "2021-09-22      -70.709543        0.000220  \n",
       "2021-09-23      -70.709608        0.000154  \n",
       "2021-09-24      -70.709664        0.000098  \n",
       "2021-09-25      -70.709711        0.000051  \n",
       "2021-09-26      -70.709750        0.000013  \n",
       "2021-09-27      -70.709543        0.000220  \n",
       "2021-09-28      -70.709385        0.000378  \n",
       "2021-09-29      -70.709385        0.000378  \n",
       "2021-09-30      -70.709385        0.000378  \n",
       "\n",
       "[21792 rows x 8 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unimp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "dailyreqts = add_ts_col(dailyreqts)\n",
    "dailyreqts.to_csv(out_csv)"
   ]
  }
 ],
 "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}