{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Water Survey of Canada HYDAT" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Historical data for lake levels and stream flows in the Rainy River watershed are available from several public sources including the [HYDAT database](https://www.ec.gc.ca/rhc-wsc/default.asp?lang=En&n=9018B5EC-1) from the [Water Survey of Canada (WSC)](http://wateroffice.ec.gc.ca/). The [HYDAT database](https://www.ec.gc.ca/rhc-wsc/default.asp?lang=En&n=9018B5EC-1) database consists of Microsoft Access .mdb file providing data on daily water flows and levels throughout Canada.\n", "\n", "For the purposes of these notebooks, individual tables (STATIONS, DLY_FLOWS, and DLY_LEVELS) were extracted from the database as .csv files with [MDB Explorer](http://www.mdbexplorer.com/). This notebook then reads the ..csv file to extract information relevant to the Rainy River basin and stores the results in this repository's data directory. Due to size constraints , only the data relevant to the Rainy River Watershed is included in the repository. Neither the HYDAT database or extracted .csv files are included in this repository.\n", "\n", "This notebooks is run whenever a new version of the HYDAT database becomes available, normally on a quarterly basis, or if additional flow and level stations are needed in the repository data cache. \n", "\n", "\n", "### Table of Contents\n", "\n", "* [Initialization](#Initialization): Load needed libaries and data tables from the HYDAT database.\n", "* [Monitoring Stations in the Rainy River Watershed](#Monitoring Stations): Create pandas dataframes for all stations with a geographic box bounded by latitude and longitude. Produces a pickled dataframe WSC_STATIONS in the data directory.\n", "* [Pickle Level Data to WSC_LEVELS](#WSC_LEVELS): Extract level data from the HYDAT database and pickle to WSC_LEVELS data set. The pickled dataframe is indexed by date, with columns tagged by station name.\n", "* [Pickle Flow Data to WSC_FLOWS](#WSC_FLOWS): Extract level data from the HYDAT database and pickle to WSC_LEVELS data set. The pickled dataframe is indexed by date, with columns tagged by station name." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "## Initialization and Loading of HYDAT Data Tables" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": true, "jupyter": { "outputs_hidden": true } }, "outputs": [], "source": [ "# Standard Python modules\n", "import pandas as pd\n", "from IPython.core.display import display" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Stations in the HYDAT database = 7775\n" ] } ], "source": [ "# Directory where data files have been stored\n", "HYDAT_dir = \"../data/HYDAT/\"\n", "\n", "# Read data files as a pandas dataframes\n", "STATIONS = pd.read_csv(HYDAT_dir + 'STATIONS.csv', index_col = 0);\n", "DLY_LEVELS = pd.read_csv(HYDAT_dir + 'DLY_LEVELS.csv')\n", "DLY_FLOWS = pd.read_csv(HYDAT_dir + 'DLY_FLOWS.csv')\n", "\n", "print(\" Stations in the HYDAT database = \", len(STATIONS.index))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "## Monitoring Stations in the Rainy River Watershed\n", "\n", "The following cell creates a pandas dataframe of monitoring stations from the STATIONS.csv table extracted from the HYDAT database. The extaction searches for all stations with a specified region bounded by latitude and longitudes.\n", "\n", "For reference, this is a map of the [Rainy River drainage](http://www.ijc.org/files/tinymce/uploaded/rl_basinmap.pdf) basin available from the International Joint Commission. \n", "\n", "

" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Stations within region of interest = 34\n", "DLY_FLOWS records = 7965\n", "DLY_LEVELS records = 5913\n" ] }, { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
LevelFlowSTATION_NAMELATITUDELONGITUDE
STATION_NUMBER
05PA001TrueKETTLE RIVER ABOVE KETTLE FALLS48.49444-92.64444
05PA003TrueNAMAKAN LAKE ABOVE KETTLE FALLS DAM48.50000-92.63886
05PA005TrueNORTHERN LIGHT LAKE AT OUTLET48.24028-90.76889
05PA006TrueTrueNAMAKAN RIVER AT OUTLET OF LAC LA CROIX48.38256-92.17631
05PA007TrueCROOKED LAKE NEAR CURTAIN FALLS48.23750-91.90611
05PA010TrueFRENCH LAKE NEAR ATIKOKAN48.67222-91.13500
05PA011TrueLAC LA CROIX AT CAMPBELL'S CAMP48.35508-92.21744
05PA012TrueTrueBASSWOOD RIVER NEAR WINTON48.08256-91.65117
05PA013TrueNAMAKAN LAKE AT SQUIRREL ISLAND48.49686-92.65856
05PB001TrueSEINE RIVER NEAR LA SEINE48.75000-92.20000
05PB002TrueLITTLE TURTLE LAKE NEAR MINE CENTRE48.77222-92.60833
05PB003TrueMANITOU RIVER ABOVE DEVIL'S CASCADE48.97500-93.34167
05PB004TrueFOOTPRINT RIVER AT RAINY LAKE FALLS48.85833-93.57500
05PB007TrueRAINY LAKE NEAR FORT FRANCES48.64912-93.32068
05PB009TrueSEINE RIVER AT STURGEON FALLS GENERATING STATION48.74444-92.28472
05PB012TrueLAC DES MILLE LACS ABOVE OUTLET DAM48.98000-90.73000
05PB014TrueTrueTURTLE RIVER NEAR MINE CENTRE48.85022-92.72383
05PB015TruePIPESTONE RIVER ABOVE RAINY LAKE48.56861-92.52417
05PB018TrueTrueATIKOKAN RIVER AT ATIKOKAN48.75197-91.58408
05PB019TrueNORTHEAST TRIBUTARY TO DASHWA LAKE NEAR ATIKOKAN48.95083-91.71222
05PB020TrueEASTERN TRIBUTARY TO DASHWA LAKE NEAR ATIKOKAN48.94056-91.69833
05PB021TrueEYE RIVER NEAR HARDTACK LAKE NORTH OF ATIKOKAN48.92500-91.66222
05PB022TrueEYE RIVER NEAR COULSON LAKE NORTH OF ATIKOKAN48.89444-91.66750
05PB023TrueRAINY LAKE AT NORTHWEST BAY48.84167-93.62000
05PB024TrueRAINY LAKE NEAR BEAR PASS48.70058-92.95800
05PB025TrueRAINY LAKE AT STOKES BAY48.53611-92.56111
05PC009TrueLA VALLEE RIVER AT LA VALLEE48.62083-93.62500
05PC010TrueSTURGEON RIVER NEAR BARWICK48.68750-93.98333
05PC016TrueLA VALLEE RIVER NEAR DEVLIN48.59028-93.67278
05PC018TrueTrueRAINY RIVER AT MANITOU RAPIDS48.63447-93.91336
05PC019TrueRAINY RIVER AT FORT FRANCES48.60853-93.40344
05PC022TrueTrueLA VALLEE RIVER NEAR BURRISS48.67844-93.66522
05PC024TrueRAINY RIVER AT PITHERS POINT SITE NO.148.61389-93.35472
05PC025TrueRAINY RIVER AT PITHERS POINT SITE NO.248.61625-93.35992
\n", "
" ], "text/plain": [ " Level Flow STATION_NAME \\\n", "STATION_NUMBER \n", "05PA001 True KETTLE RIVER ABOVE KETTLE FALLS \n", "05PA003 True NAMAKAN LAKE ABOVE KETTLE FALLS DAM \n", "05PA005 True NORTHERN LIGHT LAKE AT OUTLET \n", "05PA006 True True NAMAKAN RIVER AT OUTLET OF LAC LA CROIX \n", "05PA007 True CROOKED LAKE NEAR CURTAIN FALLS \n", "05PA010 True FRENCH LAKE NEAR ATIKOKAN \n", "05PA011 True LAC LA CROIX AT CAMPBELL'S CAMP \n", "05PA012 True True BASSWOOD RIVER NEAR WINTON \n", "05PA013 True NAMAKAN LAKE AT SQUIRREL ISLAND \n", "05PB001 True SEINE RIVER NEAR LA SEINE \n", "05PB002 True LITTLE TURTLE LAKE NEAR MINE CENTRE \n", "05PB003 True MANITOU RIVER ABOVE DEVIL'S CASCADE \n", "05PB004 True FOOTPRINT RIVER AT RAINY LAKE FALLS \n", "05PB007 True RAINY LAKE NEAR FORT FRANCES \n", "05PB009 True SEINE RIVER AT STURGEON FALLS GENERATING STATION \n", "05PB012 True LAC DES MILLE LACS ABOVE OUTLET DAM \n", "05PB014 True True TURTLE RIVER NEAR MINE CENTRE \n", "05PB015 True PIPESTONE RIVER ABOVE RAINY LAKE \n", "05PB018 True True ATIKOKAN RIVER AT ATIKOKAN \n", "05PB019 True NORTHEAST TRIBUTARY TO DASHWA LAKE NEAR ATIKOKAN \n", "05PB020 True EASTERN TRIBUTARY TO DASHWA LAKE NEAR ATIKOKAN \n", "05PB021 True EYE RIVER NEAR HARDTACK LAKE NORTH OF ATIKOKAN \n", "05PB022 True EYE RIVER NEAR COULSON LAKE NORTH OF ATIKOKAN \n", "05PB023 True RAINY LAKE AT NORTHWEST BAY \n", "05PB024 True RAINY LAKE NEAR BEAR PASS \n", "05PB025 True RAINY LAKE AT STOKES BAY \n", "05PC009 True LA VALLEE RIVER AT LA VALLEE \n", "05PC010 True STURGEON RIVER NEAR BARWICK \n", "05PC016 True LA VALLEE RIVER NEAR DEVLIN \n", "05PC018 True True RAINY RIVER AT MANITOU RAPIDS \n", "05PC019 True RAINY RIVER AT FORT FRANCES \n", "05PC022 True True LA VALLEE RIVER NEAR BURRISS \n", "05PC024 True RAINY RIVER AT PITHERS POINT SITE NO.1 \n", "05PC025 True RAINY RIVER AT PITHERS POINT SITE NO.2 \n", "\n", " LATITUDE LONGITUDE \n", "STATION_NUMBER \n", "05PA001 48.49444 -92.64444 \n", "05PA003 48.50000 -92.63886 \n", "05PA005 48.24028 -90.76889 \n", "05PA006 48.38256 -92.17631 \n", "05PA007 48.23750 -91.90611 \n", "05PA010 48.67222 -91.13500 \n", "05PA011 48.35508 -92.21744 \n", "05PA012 48.08256 -91.65117 \n", "05PA013 48.49686 -92.65856 \n", "05PB001 48.75000 -92.20000 \n", "05PB002 48.77222 -92.60833 \n", "05PB003 48.97500 -93.34167 \n", "05PB004 48.85833 -93.57500 \n", "05PB007 48.64912 -93.32068 \n", "05PB009 48.74444 -92.28472 \n", "05PB012 48.98000 -90.73000 \n", "05PB014 48.85022 -92.72383 \n", "05PB015 48.56861 -92.52417 \n", "05PB018 48.75197 -91.58408 \n", "05PB019 48.95083 -91.71222 \n", "05PB020 48.94056 -91.69833 \n", "05PB021 48.92500 -91.66222 \n", "05PB022 48.89444 -91.66750 \n", "05PB023 48.84167 -93.62000 \n", "05PB024 48.70058 -92.95800 \n", "05PB025 48.53611 -92.56111 \n", "05PC009 48.62083 -93.62500 \n", "05PC010 48.68750 -93.98333 \n", "05PC016 48.59028 -93.67278 \n", "05PC018 48.63447 -93.91336 \n", "05PC019 48.60853 -93.40344 \n", "05PC022 48.67844 -93.66522 \n", "05PC024 48.61389 -93.35472 \n", "05PC025 48.61625 -93.35992 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# bounding region\n", "lngW = -94.0\n", "lngE = -90.5\n", "latS = 47.5\n", "latN = 49.0\n", "\n", "# locate monitoring stations within bounding region\n", "stationList = STATIONS[(STATIONS['LATITUDE'] >= latS) & \\\n", " (STATIONS['LATITUDE'] <= latN) & \\\n", " (STATIONS['LONGITUDE'] <= lngE) & \\\n", " (STATIONS['LONGITUDE'] >= lngW)].index\n", "\n", "# reduce data sets to the area of interest\n", "WSC_STATIONS = STATIONS.ix[stationList]\n", "DLY_LEVELS = DLY_LEVELS.ix[[idx for idx in DLY_LEVELS.index if DLY_LEVELS.ix[idx,'STATION_NUMBER'] in stationList]]\n", "DLY_FLOWS = DLY_FLOWS.ix[[idx for idx in DLY_FLOWS.index if DLY_FLOWS.ix[idx,'STATION_NUMBER'] in stationList]]\n", "\n", "# add columns to STATIONS for level and flow stations\n", "WSC_STATIONS['Level'] = ''\n", "WSC_STATIONS['Flow'] = ''\n", "\n", "# mark level and flow stations\n", "WSC_STATIONS.ix[set(DLY_LEVELS['STATION_NUMBER']).intersection(WSC_STATIONS.index),'Level'] = True\n", "WSC_STATIONS.ix[set(DLY_FLOWS['STATION_NUMBER']).intersection(WSC_STATIONS.index),'Flow'] = True\n", "\n", "print(\"Stations within region of interest = \", len(WSC_STATIONS.index))\n", "print(\"DLY_FLOWS records = \", len(DLY_FLOWS.index))\n", "print(\"DLY_LEVELS records = \", len(DLY_LEVELS.index))\n", "display(WSC_STATIONS.ix[:,['Level','Flow','STATION_NAME','LATITUDE','LONGITUDE',]])\n", "\n", "WSC_STATIONS.to_pickle('../data/WSC_STATIONS')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "## Pickle Level Data to WSC_LEVELS" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
05PA00305PA00505PA00605PA00705PA01005PA01105PA01205PA01305PB00205PB00705PB01205PB01405PB01805PB02305PB02405PB02505PC01805PC02205PC02405PC025
2014-12-27NaNNaN360.451NaNNaNNaNNaN339.987NaNNaNNaN0.915386.2968.433337.361NaNNaN10.775337.325337.324
2014-12-28NaNNaN360.449NaNNaNNaNNaN339.981NaNNaNNaN0.913386.2878.426337.356NaNNaN10.779337.318337.317
2014-12-29NaNNaN360.447NaNNaNNaNNaN339.975NaNNaNNaN0.910386.2958.421337.351NaNNaN10.788337.313337.312
2014-12-30NaNNaN360.445NaNNaNNaNNaN339.968NaNNaNNaN0.907386.2978.418337.346NaNNaN10.796337.308337.307
2014-12-31NaNNaN360.444NaNNaNNaNNaN339.962NaNNaNNaN0.904386.3038.418337.341NaNNaN10.795337.303337.301
\n", "
" ], "text/plain": [ " 05PA003 05PA005 05PA006 05PA007 05PA010 05PA011 05PA012 \\\n", "2014-12-27 NaN NaN 360.451 NaN NaN NaN NaN \n", "2014-12-28 NaN NaN 360.449 NaN NaN NaN NaN \n", "2014-12-29 NaN NaN 360.447 NaN NaN NaN NaN \n", "2014-12-30 NaN NaN 360.445 NaN NaN NaN NaN \n", "2014-12-31 NaN NaN 360.444 NaN NaN NaN NaN \n", "\n", " 05PA013 05PB002 05PB007 05PB012 05PB014 05PB018 05PB023 \\\n", "2014-12-27 339.987 NaN NaN NaN 0.915 386.296 8.433 \n", "2014-12-28 339.981 NaN NaN NaN 0.913 386.287 8.426 \n", "2014-12-29 339.975 NaN NaN NaN 0.910 386.295 8.421 \n", "2014-12-30 339.968 NaN NaN NaN 0.907 386.297 8.418 \n", "2014-12-31 339.962 NaN NaN NaN 0.904 386.303 8.418 \n", "\n", " 05PB024 05PB025 05PC018 05PC022 05PC024 05PC025 \n", "2014-12-27 337.361 NaN NaN 10.775 337.325 337.324 \n", "2014-12-28 337.356 NaN NaN 10.779 337.318 337.317 \n", "2014-12-29 337.351 NaN NaN 10.788 337.313 337.312 \n", "2014-12-30 337.346 NaN NaN 10.796 337.308 337.307 \n", "2014-12-31 337.341 NaN NaN 10.795 337.303 337.301 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def getLevelsWSC(s):\n", " '''Return a time series for levels at a given station.'''\n", " ts = {} \n", " data = DLY_LEVELS[DLY_LEVELS['STATION_NUMBER'] == s]\n", " for k in data.index:\n", " mo = str(data.ix[k,'MONTH'])\n", " yr = str(data.ix[k,'YEAR'])\n", " for n in range(1,data.ix[k,'NO_DAYS']+1):\n", " ts[pd.to_datetime(mo+'/'+str(n)+'/'+yr)] = data.ix[k,'LEVEL'+str(n)] \n", " ts = pd.Series(ts)\n", " #drop initial and terminal null entries\n", " j = 0\n", " while j=j and pd.isnull(ts.ix[k]):\n", " k += -1\n", " return ts[j:k+1]\n", "\n", "WSC_LEVELS = pd.DataFrame({s:getLevelsWSC(s) for s in WSC_STATIONS[WSC_STATIONS['Level']==True].index})\n", "WSC_LEVELS.to_pickle('../data/WSC_LEVELS')\n", "WSC_LEVELS.tail()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "## Pickle Flow Data to WSC_FLOWS\n", "\n", "The DLY_FLOW data is encoded in an irregular tabular format where rows are indexed by station code, year, and month, and columns are indexed by date. Given a station code, the following function decodes DLY_FLOW to produce a pandas times series of flow rates. The function is used to create a pandas dataframe for all flow stations in WSC_STATIONS, then pickles the results to a data file for use by other notebooks in this repository." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
05PA00105PA00605PA01205PB00105PB00305PB00405PB00905PB01405PB01505PB01805PB01905PB02005PB02105PB02205PC00905PC01005PC01605PC01805PC01905PC022
2014-12-27NaN36.7NaNNaNNaNNaN36.922.5NaN1.27NaNNaNNaNNaNNaNNaNNaNNaN170.00.027
2014-12-28NaN36.6NaNNaNNaNNaN37.422.4NaN1.26NaNNaNNaNNaNNaNNaNNaNNaN170.00.026
2014-12-29NaN36.5NaNNaNNaNNaN37.822.2NaN1.25NaNNaNNaNNaNNaNNaNNaNNaN169.00.026
2014-12-30NaN36.4NaNNaNNaNNaN37.122.0NaN1.24NaNNaNNaNNaNNaNNaNNaNNaN169.00.026
2014-12-31NaN36.3NaNNaNNaNNaN38.021.9NaN1.24NaNNaNNaNNaNNaNNaNNaNNaN169.00.026
\n", "
" ], "text/plain": [ " 05PA001 05PA006 05PA012 05PB001 05PB003 05PB004 05PB009 \\\n", "2014-12-27 NaN 36.7 NaN NaN NaN NaN 36.9 \n", "2014-12-28 NaN 36.6 NaN NaN NaN NaN 37.4 \n", "2014-12-29 NaN 36.5 NaN NaN NaN NaN 37.8 \n", "2014-12-30 NaN 36.4 NaN NaN NaN NaN 37.1 \n", "2014-12-31 NaN 36.3 NaN NaN NaN NaN 38.0 \n", "\n", " 05PB014 05PB015 05PB018 05PB019 05PB020 05PB021 05PB022 \\\n", "2014-12-27 22.5 NaN 1.27 NaN NaN NaN NaN \n", "2014-12-28 22.4 NaN 1.26 NaN NaN NaN NaN \n", "2014-12-29 22.2 NaN 1.25 NaN NaN NaN NaN \n", "2014-12-30 22.0 NaN 1.24 NaN NaN NaN NaN \n", "2014-12-31 21.9 NaN 1.24 NaN NaN NaN NaN \n", "\n", " 05PC009 05PC010 05PC016 05PC018 05PC019 05PC022 \n", "2014-12-27 NaN NaN NaN NaN 170.0 0.027 \n", "2014-12-28 NaN NaN NaN NaN 170.0 0.026 \n", "2014-12-29 NaN NaN NaN NaN 169.0 0.026 \n", "2014-12-30 NaN NaN NaN NaN 169.0 0.026 \n", "2014-12-31 NaN NaN NaN NaN 169.0 0.026 " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def getFlowsWSC(s):\n", " ts = {}\n", " data = DLY_FLOWS[DLY_FLOWS['STATION_NUMBER'] == s]\n", " for k in data.index:\n", " mo = str(data.ix[k,'MONTH'])\n", " yr = str(data.ix[k,'YEAR'])\n", " for n in range(1,data.ix[k,'NO_DAYS']+1):\n", " ts[pd.to_datetime(mo+'/'+str(n)+'/'+yr)] = data.ix[k,'FLOW'+str(n)] \n", " ts = pd.Series(ts)\n", " ts.name = s +': ' + STATIONS.ix[s,'STATION_NAME'] + \\\n", " ' from ' + '{0}'.format(ts.index[0].year) + \\\n", " ' to ' + '{0}'.format(ts.index[-1].year)\n", " #drop initial and terminal null entries\n", " j = 0\n", " while j=j and pd.isnull(ts.ix[k]):\n", " k += -1\n", " return ts[j:k+1]\n", "\n", "WSC_FLOWS = pd.DataFrame({s: getFlowsWSC(s) for s in WSC_STATIONS[WSC_STATIONS['Flow']==True].index})\n", "WSC_FLOWS.to_pickle('../data/WSC_FLOWS')\n", "WSC_FLOWS.tail()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.7" } }, "nbformat": 4, "nbformat_minor": 4 }