gauge_analysis_automated.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pull in the data for the water flow "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import csv\n",
    "import requests\n",
    "import os\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# change to the path with the raw csv file\n",
    "path=r'C:\\Springboard\\Github\\River_Analysis'\n",
    "os.chdir(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# abstrated functions first before starting the loop. No need to redefine them every time\n",
    "# function for pulling the date range for reach USGS gage\n",
    "# takes in string of the gage number with USGS\n",
    "def pull_start(USGS):\n",
    "    link = \"https://waterdata.usgs.gov/nwis/dv?referred_module=sw&site_no=\" + USGS\n",
    "    response = requests.get(link)\n",
    "    soup = BeautifulSoup(response.text, \"html.parser\")\n",
    "    site_sum = soup.find_all(id='available_parameters_table')\n",
    "    rows_sum = site_sum[0].find_all('tr')\n",
    "    for row in rows_sum:\n",
    "        cell_sum = row.find_all('td')\n",
    "        for i, cell in enumerate(cell_sum):\n",
    "            if 'discharge' in cell.get_text().lower():\n",
    "                # get start date from the next cell\n",
    "                start_date = re.findall(r'\\d{4}-\\d{2}-\\d{2}', cell_sum[i+1].get_text())[0]\n",
    "                end_date = re.findall(r'\\d{4}-\\d{2}-\\d{2}', cell_sum[i+2].get_text())[0]\n",
    "                # re.findall(r'\\d{4}-\\d{2}-\\d{2}', cell[next].get_text())[0]\n",
    "                return start_date, end_date\n",
    "    return 0, 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this function pulls the water date from a given gage from start_date to end_date\n",
    "def pull_water(USGS, start_date, end_date):\n",
    "    link_begin = 'https://waterdata.usgs.gov/nwis/dv?cb_00060=on&format=rdb&site_no='\n",
    "    link_mid = '&referred_module=sw&period=&begin_date='\n",
    "    link_end = '&end_date='\n",
    "    link = link_begin + USGS + link_mid + start_date + link_end + end_date\n",
    "    response = requests.get(link)\n",
    "    # raw_file = USGS + '.csv'\n",
    "    with open(raw_file, 'wb') as f:\n",
    "        for chunk in response:\n",
    "            f.write(chunk)\n",
    "\n",
    "# this gets a raw file of the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cleaning_file(gage, raw):\n",
    "    # read the raw CSV back in and remove the commented lines\n",
    "    # open raw CSV\n",
    "    fi = open(raw, 'r')\n",
    "\n",
    "    # read raw CSV to clean CSV - eliminate comment rows with \"#\"\n",
    "    clean_file = gage + '_clean.csv'\n",
    "    with open(clean_file, 'w') as fo:\n",
    "        lines = fi.readlines()\n",
    "        for line in lines:\n",
    "            if \"#\" not in line:\n",
    "                fo.write(line)\n",
    "    fi.close()\n",
    "    return clean_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": false
   },
   "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>gages</th>\n",
       "      <th>flows</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09063000</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09064600</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>09067020</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>09070000</td>\n",
       "      <td>221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09065100</td>\n",
       "      <td>-999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>CATCANYON</td>\n",
       "      <td>4320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>RFBASALT</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>ESCCREEKCO</td>\n",
       "      <td>249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>SFPAYETTEID</td>\n",
       "      <td>1021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>244</th>\n",
       "      <td>SFPAYETTESTAIRID</td>\n",
       "      <td>2433</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>245 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                gages   flows\n",
       "0            09063000      32\n",
       "1            09064600      80\n",
       "2            09067020     146\n",
       "3            09070000     221\n",
       "4            09065100 -999999\n",
       "..                ...     ...\n",
       "240         CATCANYON    4320\n",
       "241          RFBASALT     123\n",
       "242        ESCCREEKCO     249\n",
       "243       SFPAYETTEID    1021\n",
       "244  SFPAYETTESTAIRID    2433\n",
       "\n",
       "[245 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pull in the list of USGS gages that I use on rivermaps.co\n",
    "USGS_list = pd.read_csv('USGS_list.csv', error_bad_lines=False, delimiter='=', names=['gages', 'flows'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create list of USGS Gages that I care about\n",
    "gage_list = USGS_list['gages'].tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Loop through USGS gages in the list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 842.4x595.44 with 0 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "for GAGE in gage_list:\n",
    "    # jump to a folder to hold the CSV\n",
    "    os.chdir(r'C:\\Springboard\\Github\\River_Analysis\\CSV_files')\n",
    "    #GAGE = '14372300'\n",
    "    raw_file = GAGE + '.csv'\n",
    "    try:\n",
    "        # pulls the data for just one gage\n",
    "        start_date, end_date = pull_start(GAGE)\n",
    "        pull_water(GAGE, start_date, end_date)\n",
    "        clean_file = cleaning_file(GAGE, raw_file)\n",
    "        flow = pd.read_csv(clean_file, error_bad_lines=False, delimiter='\\t')\n",
    "        # drop the first row - that looks like useless data in most gages\n",
    "        flow.drop(0, inplace=True)\n",
    "        # rename columns to make for easier dropping\n",
    "        flow.columns = ['agency', 'gage', 'date', 'y', 'useless']\n",
    "        # drop everything except the flow ('y') column and the date\n",
    "        flow.drop(['agency', 'gage', 'useless'], axis=1, inplace=True)\n",
    "        # make sure the flow is all a floating point number\n",
    "        flow['y'] = flow['y'].astype(float)\n",
    "        # let's automatically fill the NaN values with the more recent value (backfill)\n",
    "        flow['y'].fillna(method='backfill', inplace=True)\n",
    "        # rename date column for FB prophet (in case we decide to go back to use that analysis package)\n",
    "        flow.rename(columns={'date':'ds'}, inplace=True)\n",
    "        # create Month-Day column that is named 'MD'\n",
    "        # add day and month columns to the existing DF\n",
    "        flow[\"Month\"] = flow['ds'].str[5:7]\n",
    "        flow[\"Day\"] = flow['ds'].str[8:10]\n",
    "        flow['MD'] = flow[\"Month\"] + '-' + flow[\"Day\"]\n",
    "        # make sure month and day are the correct data types - in case we want to do future analysis on them\n",
    "        flow['Month'] = flow['Month'].astype(int)\n",
    "        flow['Day'] = flow['Day'].astype(int)\n",
    "        # do that group by analysis\n",
    "        summary = flow.groupby([\"MD\"], as_index=False)['y'].agg(['mean', 'min', 'median']) # 'max'\n",
    "        ##  plot the figure and save to file\n",
    "        # set the figure size\n",
    "        sns.set(rc={'figure.figsize':(11.7,8.27)})\n",
    "        # create the plot\n",
    "        os.chdir(r'C:\\Springboard\\Github\\River_Analysis\\Plots')\n",
    "        g  = sns.lineplot(data=summary) # , x='MD', y=['min', 'mean', 'max', 'median'])\n",
    "        _ = plt.xticks(['01-01', '02-01', '03-01', '04-01', '05-01', '06-01', '07-01', '08-01', '09-01', '10-01', '11-01', '12-01', '12-31'])\n",
    "        plt.xlabel('Month-Day')\n",
    "        plt.ylabel('Daily Average Flow (cfs)')\n",
    "        plt.title(GAGE)\n",
    "        plt.savefig(GAGE + '.png',bbox_inches='tight')\n",
    "        plt.close()\n",
    "        plt.cla()\n",
    "        plt.clf()\n",
    "    except:\n",
    "        print('Error ' + GAGE)\n",
    "        pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}