IFT_Step07_water_year_percentiles.ipynb

{
 "cells": [
  {
   "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",
    "path = os.path.join(outer_dir, 'IFT_files', 'IFT Results')\n",
    "\n",
    "stream_list = ['Indian', 'Sproul']\n",
    "hydro_list = ['unimp','mpof','mmpof','ncifp','tessman']\n",
    "version = 'V1_0_0'\n",
    "in_csv = os.path.join(path, 'All_hydrographs_{}.csv'.format(version))\n",
    "out_csv = os.path.join(path, 'Water_year_types_{}.csv'.format(version))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "238605\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>flow</th>\n",
       "      <th>ift</th>\n",
       "      <th>stream</th>\n",
       "      <th>version</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>water_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1950-10-01</td>\n",
       "      <td>33.839921</td>\n",
       "      <td>unimp</td>\n",
       "      <td>indian</td>\n",
       "      <td>V1_0_0</td>\n",
       "      <td>10</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1950-10-02</td>\n",
       "      <td>33.839921</td>\n",
       "      <td>unimp</td>\n",
       "      <td>indian</td>\n",
       "      <td>V1_0_0</td>\n",
       "      <td>10</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1950-10-03</td>\n",
       "      <td>33.839921</td>\n",
       "      <td>unimp</td>\n",
       "      <td>indian</td>\n",
       "      <td>V1_0_0</td>\n",
       "      <td>10</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1950-10-04</td>\n",
       "      <td>33.839921</td>\n",
       "      <td>unimp</td>\n",
       "      <td>indian</td>\n",
       "      <td>V1_0_0</td>\n",
       "      <td>10</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1950-10-05</td>\n",
       "      <td>33.839921</td>\n",
       "      <td>unimp</td>\n",
       "      <td>indian</td>\n",
       "      <td>V1_0_0</td>\n",
       "      <td>10</td>\n",
       "      <td>1950</td>\n",
       "      <td>1951</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date       flow    ift  stream version  month  year  water_year\n",
       "0  1950-10-01  33.839921  unimp  indian  V1_0_0     10  1950        1951\n",
       "1  1950-10-02  33.839921  unimp  indian  V1_0_0     10  1950        1951\n",
       "2  1950-10-03  33.839921  unimp  indian  V1_0_0     10  1950        1951\n",
       "3  1950-10-04  33.839921  unimp  indian  V1_0_0     10  1950        1951\n",
       "4  1950-10-05  33.839921  unimp  indian  V1_0_0     10  1950        1951"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import data\n",
    "df = pd.read_csv(in_csv)\n",
    "print(len(df))\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>date</th>\n",
       "      <th>flow</th>\n",
       "      <th>ift</th>\n",
       "      <th>stream</th>\n",
       "      <th>version</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>water_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [date, flow, ift, stream, version, month, year, water_year]\n",
       "Index: []"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check for missing values\n",
    "df[df.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "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>stream</th>\n",
       "      <th>water_year</th>\n",
       "      <th>wy_mean_flow_cfs</th>\n",
       "      <th>day_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>indian</td>\n",
       "      <td>1951</td>\n",
       "      <td>80.799484</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>indian</td>\n",
       "      <td>1952</td>\n",
       "      <td>97.464302</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>indian</td>\n",
       "      <td>1953</td>\n",
       "      <td>78.676068</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>indian</td>\n",
       "      <td>1954</td>\n",
       "      <td>67.919191</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>indian</td>\n",
       "      <td>1955</td>\n",
       "      <td>32.950972</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>indian</td>\n",
       "      <td>1956</td>\n",
       "      <td>116.140401</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>indian</td>\n",
       "      <td>1957</td>\n",
       "      <td>41.925103</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>indian</td>\n",
       "      <td>1958</td>\n",
       "      <td>124.676122</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>indian</td>\n",
       "      <td>1959</td>\n",
       "      <td>41.579588</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>indian</td>\n",
       "      <td>1960</td>\n",
       "      <td>43.260980</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>indian</td>\n",
       "      <td>1961</td>\n",
       "      <td>46.742975</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>indian</td>\n",
       "      <td>1962</td>\n",
       "      <td>49.370364</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>indian</td>\n",
       "      <td>1963</td>\n",
       "      <td>69.705711</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>indian</td>\n",
       "      <td>1964</td>\n",
       "      <td>27.424636</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>indian</td>\n",
       "      <td>1965</td>\n",
       "      <td>103.935032</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>indian</td>\n",
       "      <td>1966</td>\n",
       "      <td>49.821766</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>indian</td>\n",
       "      <td>1967</td>\n",
       "      <td>78.204506</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>indian</td>\n",
       "      <td>1968</td>\n",
       "      <td>40.423712</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>indian</td>\n",
       "      <td>1969</td>\n",
       "      <td>109.408495</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>indian</td>\n",
       "      <td>1970</td>\n",
       "      <td>92.852301</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>indian</td>\n",
       "      <td>1971</td>\n",
       "      <td>66.465648</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>indian</td>\n",
       "      <td>1972</td>\n",
       "      <td>24.492674</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>indian</td>\n",
       "      <td>1973</td>\n",
       "      <td>80.438832</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>indian</td>\n",
       "      <td>1974</td>\n",
       "      <td>140.727383</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>indian</td>\n",
       "      <td>1975</td>\n",
       "      <td>78.693342</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>indian</td>\n",
       "      <td>1976</td>\n",
       "      <td>15.963928</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>indian</td>\n",
       "      <td>1977</td>\n",
       "      <td>3.507721</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>indian</td>\n",
       "      <td>1978</td>\n",
       "      <td>104.860561</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>indian</td>\n",
       "      <td>1979</td>\n",
       "      <td>37.443202</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>indian</td>\n",
       "      <td>1980</td>\n",
       "      <td>76.832308</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1990</td>\n",
       "      <td>8.052878</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1991</td>\n",
       "      <td>6.001208</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1992</td>\n",
       "      <td>7.454817</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1993</td>\n",
       "      <td>17.562573</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1994</td>\n",
       "      <td>8.383805</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1995</td>\n",
       "      <td>27.580668</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1996</td>\n",
       "      <td>19.729783</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1997</td>\n",
       "      <td>22.877200</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1998</td>\n",
       "      <td>26.866201</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1999</td>\n",
       "      <td>19.591429</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2000</td>\n",
       "      <td>16.810757</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2001</td>\n",
       "      <td>6.871370</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2002</td>\n",
       "      <td>16.246748</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2003</td>\n",
       "      <td>22.857028</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2004</td>\n",
       "      <td>16.865942</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2005</td>\n",
       "      <td>15.582321</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2006</td>\n",
       "      <td>29.524876</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2007</td>\n",
       "      <td>12.006688</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2008</td>\n",
       "      <td>12.637617</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2009</td>\n",
       "      <td>9.402356</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2010</td>\n",
       "      <td>17.956125</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2011</td>\n",
       "      <td>19.126325</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2012</td>\n",
       "      <td>11.624554</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2013</td>\n",
       "      <td>13.437512</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.422825</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2015</td>\n",
       "      <td>12.519797</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2016</td>\n",
       "      <td>16.968470</td>\n",
       "      <td>366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2017</td>\n",
       "      <td>29.207655</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2018</td>\n",
       "      <td>9.552816</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2021</td>\n",
       "      <td>7.951467</td>\n",
       "      <td>362</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>130 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     stream  water_year  wy_mean_flow_cfs  day_count\n",
       "0    indian        1951         80.799484        365\n",
       "1    indian        1952         97.464302        366\n",
       "2    indian        1953         78.676068        365\n",
       "3    indian        1954         67.919191        365\n",
       "4    indian        1955         32.950972        365\n",
       "5    indian        1956        116.140401        366\n",
       "6    indian        1957         41.925103        365\n",
       "7    indian        1958        124.676122        365\n",
       "8    indian        1959         41.579588        365\n",
       "9    indian        1960         43.260980        366\n",
       "10   indian        1961         46.742975        365\n",
       "11   indian        1962         49.370364        365\n",
       "12   indian        1963         69.705711        365\n",
       "13   indian        1964         27.424636        366\n",
       "14   indian        1965        103.935032        365\n",
       "15   indian        1966         49.821766        365\n",
       "16   indian        1967         78.204506        365\n",
       "17   indian        1968         40.423712        366\n",
       "18   indian        1969        109.408495        365\n",
       "19   indian        1970         92.852301        365\n",
       "20   indian        1971         66.465648        365\n",
       "21   indian        1972         24.492674        366\n",
       "22   indian        1973         80.438832        365\n",
       "23   indian        1974        140.727383        365\n",
       "24   indian        1975         78.693342        365\n",
       "25   indian        1976         15.963928        366\n",
       "26   indian        1977          3.507721        365\n",
       "27   indian        1978        104.860561        365\n",
       "28   indian        1979         37.443202        365\n",
       "29   indian        1980         76.832308        366\n",
       "..      ...         ...               ...        ...\n",
       "100  sproul        1990          8.052878        365\n",
       "101  sproul        1991          6.001208        365\n",
       "102  sproul        1992          7.454817        366\n",
       "103  sproul        1993         17.562573        365\n",
       "104  sproul        1994          8.383805        365\n",
       "105  sproul        1995         27.580668        365\n",
       "106  sproul        1996         19.729783        366\n",
       "107  sproul        1997         22.877200        365\n",
       "108  sproul        1998         26.866201        365\n",
       "109  sproul        1999         19.591429        365\n",
       "110  sproul        2000         16.810757        366\n",
       "111  sproul        2001          6.871370        365\n",
       "112  sproul        2002         16.246748        365\n",
       "113  sproul        2003         22.857028        365\n",
       "114  sproul        2004         16.865942        366\n",
       "115  sproul        2005         15.582321        365\n",
       "116  sproul        2006         29.524876        365\n",
       "117  sproul        2007         12.006688        365\n",
       "118  sproul        2008         12.637617        366\n",
       "119  sproul        2009          9.402356        365\n",
       "120  sproul        2010         17.956125        365\n",
       "121  sproul        2011         19.126325        365\n",
       "122  sproul        2012         11.624554        366\n",
       "123  sproul        2013         13.437512        365\n",
       "124  sproul        2014          3.422825        365\n",
       "125  sproul        2015         12.519797        365\n",
       "126  sproul        2016         16.968470        366\n",
       "127  sproul        2017         29.207655        365\n",
       "128  sproul        2018          9.552816        365\n",
       "131  sproul        2021          7.951467        362\n",
       "\n",
       "[130 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = df.loc[df.ift == 'unimp'].copy()\n",
    "df2 = (df1.groupby(['stream', 'water_year'])\n",
    "           .agg({'flow':'mean', 'date':'count'})\n",
    "           .reset_index()\n",
    "           .rename(columns={'flow':'wy_mean_flow_cfs', 'date':'day_count'})\n",
    "          )\n",
    "# Filter out partial years\n",
    "df3 = df2.loc[df2.day_count >= 360].copy()\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "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>stream</th>\n",
       "      <th>water_year</th>\n",
       "      <th>wy_mean_flow_cfs</th>\n",
       "      <th>day_count</th>\n",
       "      <th>pct_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>indian</td>\n",
       "      <td>1951</td>\n",
       "      <td>80.799484</td>\n",
       "      <td>365</td>\n",
       "      <td>0.732394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>indian</td>\n",
       "      <td>1952</td>\n",
       "      <td>97.464302</td>\n",
       "      <td>366</td>\n",
       "      <td>0.830986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>indian</td>\n",
       "      <td>1953</td>\n",
       "      <td>78.676068</td>\n",
       "      <td>365</td>\n",
       "      <td>0.676056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>indian</td>\n",
       "      <td>1954</td>\n",
       "      <td>67.919191</td>\n",
       "      <td>365</td>\n",
       "      <td>0.577465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>indian</td>\n",
       "      <td>1955</td>\n",
       "      <td>32.950972</td>\n",
       "      <td>365</td>\n",
       "      <td>0.253521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>indian</td>\n",
       "      <td>1956</td>\n",
       "      <td>116.140401</td>\n",
       "      <td>366</td>\n",
       "      <td>0.887324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>indian</td>\n",
       "      <td>1957</td>\n",
       "      <td>41.925103</td>\n",
       "      <td>365</td>\n",
       "      <td>0.394366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>indian</td>\n",
       "      <td>1958</td>\n",
       "      <td>124.676122</td>\n",
       "      <td>365</td>\n",
       "      <td>0.901408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>indian</td>\n",
       "      <td>1959</td>\n",
       "      <td>41.579588</td>\n",
       "      <td>365</td>\n",
       "      <td>0.380282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>indian</td>\n",
       "      <td>1960</td>\n",
       "      <td>43.260980</td>\n",
       "      <td>366</td>\n",
       "      <td>0.422535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>indian</td>\n",
       "      <td>1961</td>\n",
       "      <td>46.742975</td>\n",
       "      <td>365</td>\n",
       "      <td>0.450704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>indian</td>\n",
       "      <td>1962</td>\n",
       "      <td>49.370364</td>\n",
       "      <td>365</td>\n",
       "      <td>0.464789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>indian</td>\n",
       "      <td>1963</td>\n",
       "      <td>69.705711</td>\n",
       "      <td>365</td>\n",
       "      <td>0.591549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>indian</td>\n",
       "      <td>1964</td>\n",
       "      <td>27.424636</td>\n",
       "      <td>366</td>\n",
       "      <td>0.211268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>indian</td>\n",
       "      <td>1965</td>\n",
       "      <td>103.935032</td>\n",
       "      <td>365</td>\n",
       "      <td>0.845070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>indian</td>\n",
       "      <td>1966</td>\n",
       "      <td>49.821766</td>\n",
       "      <td>365</td>\n",
       "      <td>0.478873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>indian</td>\n",
       "      <td>1967</td>\n",
       "      <td>78.204506</td>\n",
       "      <td>365</td>\n",
       "      <td>0.661972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>indian</td>\n",
       "      <td>1968</td>\n",
       "      <td>40.423712</td>\n",
       "      <td>366</td>\n",
       "      <td>0.366197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>indian</td>\n",
       "      <td>1969</td>\n",
       "      <td>109.408495</td>\n",
       "      <td>365</td>\n",
       "      <td>0.873239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>indian</td>\n",
       "      <td>1970</td>\n",
       "      <td>92.852301</td>\n",
       "      <td>365</td>\n",
       "      <td>0.816901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>indian</td>\n",
       "      <td>1971</td>\n",
       "      <td>66.465648</td>\n",
       "      <td>365</td>\n",
       "      <td>0.563380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>indian</td>\n",
       "      <td>1972</td>\n",
       "      <td>24.492674</td>\n",
       "      <td>366</td>\n",
       "      <td>0.154930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>indian</td>\n",
       "      <td>1973</td>\n",
       "      <td>80.438832</td>\n",
       "      <td>365</td>\n",
       "      <td>0.718310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>indian</td>\n",
       "      <td>1974</td>\n",
       "      <td>140.727383</td>\n",
       "      <td>365</td>\n",
       "      <td>0.971831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>indian</td>\n",
       "      <td>1975</td>\n",
       "      <td>78.693342</td>\n",
       "      <td>365</td>\n",
       "      <td>0.690141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>indian</td>\n",
       "      <td>1976</td>\n",
       "      <td>15.963928</td>\n",
       "      <td>366</td>\n",
       "      <td>0.070423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>indian</td>\n",
       "      <td>1977</td>\n",
       "      <td>3.507721</td>\n",
       "      <td>365</td>\n",
       "      <td>0.014085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>indian</td>\n",
       "      <td>1978</td>\n",
       "      <td>104.860561</td>\n",
       "      <td>365</td>\n",
       "      <td>0.859155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>indian</td>\n",
       "      <td>1979</td>\n",
       "      <td>37.443202</td>\n",
       "      <td>365</td>\n",
       "      <td>0.323944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>indian</td>\n",
       "      <td>1980</td>\n",
       "      <td>76.832308</td>\n",
       "      <td>366</td>\n",
       "      <td>0.647887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1990</td>\n",
       "      <td>8.052878</td>\n",
       "      <td>365</td>\n",
       "      <td>0.118644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1991</td>\n",
       "      <td>6.001208</td>\n",
       "      <td>365</td>\n",
       "      <td>0.050847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1992</td>\n",
       "      <td>7.454817</td>\n",
       "      <td>366</td>\n",
       "      <td>0.084746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1993</td>\n",
       "      <td>17.562573</td>\n",
       "      <td>365</td>\n",
       "      <td>0.559322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1994</td>\n",
       "      <td>8.383805</td>\n",
       "      <td>365</td>\n",
       "      <td>0.135593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1995</td>\n",
       "      <td>27.580668</td>\n",
       "      <td>365</td>\n",
       "      <td>0.881356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1996</td>\n",
       "      <td>19.729783</td>\n",
       "      <td>366</td>\n",
       "      <td>0.677966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1997</td>\n",
       "      <td>22.877200</td>\n",
       "      <td>365</td>\n",
       "      <td>0.796610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1998</td>\n",
       "      <td>26.866201</td>\n",
       "      <td>365</td>\n",
       "      <td>0.847458</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>sproul</td>\n",
       "      <td>1999</td>\n",
       "      <td>19.591429</td>\n",
       "      <td>365</td>\n",
       "      <td>0.661017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2000</td>\n",
       "      <td>16.810757</td>\n",
       "      <td>366</td>\n",
       "      <td>0.508475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2001</td>\n",
       "      <td>6.871370</td>\n",
       "      <td>365</td>\n",
       "      <td>0.067797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2002</td>\n",
       "      <td>16.246748</td>\n",
       "      <td>365</td>\n",
       "      <td>0.491525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2003</td>\n",
       "      <td>22.857028</td>\n",
       "      <td>365</td>\n",
       "      <td>0.779661</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2004</td>\n",
       "      <td>16.865942</td>\n",
       "      <td>366</td>\n",
       "      <td>0.525424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2005</td>\n",
       "      <td>15.582321</td>\n",
       "      <td>365</td>\n",
       "      <td>0.440678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2006</td>\n",
       "      <td>29.524876</td>\n",
       "      <td>365</td>\n",
       "      <td>0.932203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2007</td>\n",
       "      <td>12.006688</td>\n",
       "      <td>365</td>\n",
       "      <td>0.305085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2008</td>\n",
       "      <td>12.637617</td>\n",
       "      <td>366</td>\n",
       "      <td>0.372881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2009</td>\n",
       "      <td>9.402356</td>\n",
       "      <td>365</td>\n",
       "      <td>0.169492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2010</td>\n",
       "      <td>17.956125</td>\n",
       "      <td>365</td>\n",
       "      <td>0.593220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2011</td>\n",
       "      <td>19.126325</td>\n",
       "      <td>365</td>\n",
       "      <td>0.644068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2012</td>\n",
       "      <td>11.624554</td>\n",
       "      <td>366</td>\n",
       "      <td>0.254237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2013</td>\n",
       "      <td>13.437512</td>\n",
       "      <td>365</td>\n",
       "      <td>0.406780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.422825</td>\n",
       "      <td>365</td>\n",
       "      <td>0.033898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2015</td>\n",
       "      <td>12.519797</td>\n",
       "      <td>365</td>\n",
       "      <td>0.355932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2016</td>\n",
       "      <td>16.968470</td>\n",
       "      <td>366</td>\n",
       "      <td>0.542373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2017</td>\n",
       "      <td>29.207655</td>\n",
       "      <td>365</td>\n",
       "      <td>0.915254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2018</td>\n",
       "      <td>9.552816</td>\n",
       "      <td>365</td>\n",
       "      <td>0.186441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>sproul</td>\n",
       "      <td>2021</td>\n",
       "      <td>7.951467</td>\n",
       "      <td>362</td>\n",
       "      <td>0.101695</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>130 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     stream  water_year  wy_mean_flow_cfs  day_count  pct_rank\n",
       "0    indian        1951         80.799484        365  0.732394\n",
       "1    indian        1952         97.464302        366  0.830986\n",
       "2    indian        1953         78.676068        365  0.676056\n",
       "3    indian        1954         67.919191        365  0.577465\n",
       "4    indian        1955         32.950972        365  0.253521\n",
       "5    indian        1956        116.140401        366  0.887324\n",
       "6    indian        1957         41.925103        365  0.394366\n",
       "7    indian        1958        124.676122        365  0.901408\n",
       "8    indian        1959         41.579588        365  0.380282\n",
       "9    indian        1960         43.260980        366  0.422535\n",
       "10   indian        1961         46.742975        365  0.450704\n",
       "11   indian        1962         49.370364        365  0.464789\n",
       "12   indian        1963         69.705711        365  0.591549\n",
       "13   indian        1964         27.424636        366  0.211268\n",
       "14   indian        1965        103.935032        365  0.845070\n",
       "15   indian        1966         49.821766        365  0.478873\n",
       "16   indian        1967         78.204506        365  0.661972\n",
       "17   indian        1968         40.423712        366  0.366197\n",
       "18   indian        1969        109.408495        365  0.873239\n",
       "19   indian        1970         92.852301        365  0.816901\n",
       "20   indian        1971         66.465648        365  0.563380\n",
       "21   indian        1972         24.492674        366  0.154930\n",
       "22   indian        1973         80.438832        365  0.718310\n",
       "23   indian        1974        140.727383        365  0.971831\n",
       "24   indian        1975         78.693342        365  0.690141\n",
       "25   indian        1976         15.963928        366  0.070423\n",
       "26   indian        1977          3.507721        365  0.014085\n",
       "27   indian        1978        104.860561        365  0.859155\n",
       "28   indian        1979         37.443202        365  0.323944\n",
       "29   indian        1980         76.832308        366  0.647887\n",
       "..      ...         ...               ...        ...       ...\n",
       "100  sproul        1990          8.052878        365  0.118644\n",
       "101  sproul        1991          6.001208        365  0.050847\n",
       "102  sproul        1992          7.454817        366  0.084746\n",
       "103  sproul        1993         17.562573        365  0.559322\n",
       "104  sproul        1994          8.383805        365  0.135593\n",
       "105  sproul        1995         27.580668        365  0.881356\n",
       "106  sproul        1996         19.729783        366  0.677966\n",
       "107  sproul        1997         22.877200        365  0.796610\n",
       "108  sproul        1998         26.866201        365  0.847458\n",
       "109  sproul        1999         19.591429        365  0.661017\n",
       "110  sproul        2000         16.810757        366  0.508475\n",
       "111  sproul        2001          6.871370        365  0.067797\n",
       "112  sproul        2002         16.246748        365  0.491525\n",
       "113  sproul        2003         22.857028        365  0.779661\n",
       "114  sproul        2004         16.865942        366  0.525424\n",
       "115  sproul        2005         15.582321        365  0.440678\n",
       "116  sproul        2006         29.524876        365  0.932203\n",
       "117  sproul        2007         12.006688        365  0.305085\n",
       "118  sproul        2008         12.637617        366  0.372881\n",
       "119  sproul        2009          9.402356        365  0.169492\n",
       "120  sproul        2010         17.956125        365  0.593220\n",
       "121  sproul        2011         19.126325        365  0.644068\n",
       "122  sproul        2012         11.624554        366  0.254237\n",
       "123  sproul        2013         13.437512        365  0.406780\n",
       "124  sproul        2014          3.422825        365  0.033898\n",
       "125  sproul        2015         12.519797        365  0.355932\n",
       "126  sproul        2016         16.968470        366  0.542373\n",
       "127  sproul        2017         29.207655        365  0.915254\n",
       "128  sproul        2018          9.552816        365  0.186441\n",
       "131  sproul        2021          7.951467        362  0.101695\n",
       "\n",
       "[130 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3['pct_rank'] = df3.groupby('stream')['wy_mean_flow_cfs'].rank(pct=True)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export data to CSV\n",
    "\n",
    "df3.to_csv(out_csv, encoding='utf-8', index=False)"
   ]
  },
  {
   "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}