{
"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",
" Level | \n",
" Flow | \n",
" STATION_NAME | \n",
" LATITUDE | \n",
" LONGITUDE | \n",
"
\n",
" \n",
" STATION_NUMBER | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 05PA001 | \n",
" | \n",
" True | \n",
" KETTLE RIVER ABOVE KETTLE FALLS | \n",
" 48.49444 | \n",
" -92.64444 | \n",
"
\n",
" \n",
" 05PA003 | \n",
" True | \n",
" | \n",
" NAMAKAN LAKE ABOVE KETTLE FALLS DAM | \n",
" 48.50000 | \n",
" -92.63886 | \n",
"
\n",
" \n",
" 05PA005 | \n",
" True | \n",
" | \n",
" NORTHERN LIGHT LAKE AT OUTLET | \n",
" 48.24028 | \n",
" -90.76889 | \n",
"
\n",
" \n",
" 05PA006 | \n",
" True | \n",
" True | \n",
" NAMAKAN RIVER AT OUTLET OF LAC LA CROIX | \n",
" 48.38256 | \n",
" -92.17631 | \n",
"
\n",
" \n",
" 05PA007 | \n",
" True | \n",
" | \n",
" CROOKED LAKE NEAR CURTAIN FALLS | \n",
" 48.23750 | \n",
" -91.90611 | \n",
"
\n",
" \n",
" 05PA010 | \n",
" True | \n",
" | \n",
" FRENCH LAKE NEAR ATIKOKAN | \n",
" 48.67222 | \n",
" -91.13500 | \n",
"
\n",
" \n",
" 05PA011 | \n",
" True | \n",
" | \n",
" LAC LA CROIX AT CAMPBELL'S CAMP | \n",
" 48.35508 | \n",
" -92.21744 | \n",
"
\n",
" \n",
" 05PA012 | \n",
" True | \n",
" True | \n",
" BASSWOOD RIVER NEAR WINTON | \n",
" 48.08256 | \n",
" -91.65117 | \n",
"
\n",
" \n",
" 05PA013 | \n",
" True | \n",
" | \n",
" NAMAKAN LAKE AT SQUIRREL ISLAND | \n",
" 48.49686 | \n",
" -92.65856 | \n",
"
\n",
" \n",
" 05PB001 | \n",
" | \n",
" True | \n",
" SEINE RIVER NEAR LA SEINE | \n",
" 48.75000 | \n",
" -92.20000 | \n",
"
\n",
" \n",
" 05PB002 | \n",
" True | \n",
" | \n",
" LITTLE TURTLE LAKE NEAR MINE CENTRE | \n",
" 48.77222 | \n",
" -92.60833 | \n",
"
\n",
" \n",
" 05PB003 | \n",
" | \n",
" True | \n",
" MANITOU RIVER ABOVE DEVIL'S CASCADE | \n",
" 48.97500 | \n",
" -93.34167 | \n",
"
\n",
" \n",
" 05PB004 | \n",
" | \n",
" True | \n",
" FOOTPRINT RIVER AT RAINY LAKE FALLS | \n",
" 48.85833 | \n",
" -93.57500 | \n",
"
\n",
" \n",
" 05PB007 | \n",
" True | \n",
" | \n",
" RAINY LAKE NEAR FORT FRANCES | \n",
" 48.64912 | \n",
" -93.32068 | \n",
"
\n",
" \n",
" 05PB009 | \n",
" | \n",
" True | \n",
" SEINE RIVER AT STURGEON FALLS GENERATING STATION | \n",
" 48.74444 | \n",
" -92.28472 | \n",
"
\n",
" \n",
" 05PB012 | \n",
" True | \n",
" | \n",
" LAC DES MILLE LACS ABOVE OUTLET DAM | \n",
" 48.98000 | \n",
" -90.73000 | \n",
"
\n",
" \n",
" 05PB014 | \n",
" True | \n",
" True | \n",
" TURTLE RIVER NEAR MINE CENTRE | \n",
" 48.85022 | \n",
" -92.72383 | \n",
"
\n",
" \n",
" 05PB015 | \n",
" | \n",
" True | \n",
" PIPESTONE RIVER ABOVE RAINY LAKE | \n",
" 48.56861 | \n",
" -92.52417 | \n",
"
\n",
" \n",
" 05PB018 | \n",
" True | \n",
" True | \n",
" ATIKOKAN RIVER AT ATIKOKAN | \n",
" 48.75197 | \n",
" -91.58408 | \n",
"
\n",
" \n",
" 05PB019 | \n",
" | \n",
" True | \n",
" NORTHEAST TRIBUTARY TO DASHWA LAKE NEAR ATIKOKAN | \n",
" 48.95083 | \n",
" -91.71222 | \n",
"
\n",
" \n",
" 05PB020 | \n",
" | \n",
" True | \n",
" EASTERN TRIBUTARY TO DASHWA LAKE NEAR ATIKOKAN | \n",
" 48.94056 | \n",
" -91.69833 | \n",
"
\n",
" \n",
" 05PB021 | \n",
" | \n",
" True | \n",
" EYE RIVER NEAR HARDTACK LAKE NORTH OF ATIKOKAN | \n",
" 48.92500 | \n",
" -91.66222 | \n",
"
\n",
" \n",
" 05PB022 | \n",
" | \n",
" True | \n",
" EYE RIVER NEAR COULSON LAKE NORTH OF ATIKOKAN | \n",
" 48.89444 | \n",
" -91.66750 | \n",
"
\n",
" \n",
" 05PB023 | \n",
" True | \n",
" | \n",
" RAINY LAKE AT NORTHWEST BAY | \n",
" 48.84167 | \n",
" -93.62000 | \n",
"
\n",
" \n",
" 05PB024 | \n",
" True | \n",
" | \n",
" RAINY LAKE NEAR BEAR PASS | \n",
" 48.70058 | \n",
" -92.95800 | \n",
"
\n",
" \n",
" 05PB025 | \n",
" True | \n",
" | \n",
" RAINY LAKE AT STOKES BAY | \n",
" 48.53611 | \n",
" -92.56111 | \n",
"
\n",
" \n",
" 05PC009 | \n",
" | \n",
" True | \n",
" LA VALLEE RIVER AT LA VALLEE | \n",
" 48.62083 | \n",
" -93.62500 | \n",
"
\n",
" \n",
" 05PC010 | \n",
" | \n",
" True | \n",
" STURGEON RIVER NEAR BARWICK | \n",
" 48.68750 | \n",
" -93.98333 | \n",
"
\n",
" \n",
" 05PC016 | \n",
" | \n",
" True | \n",
" LA VALLEE RIVER NEAR DEVLIN | \n",
" 48.59028 | \n",
" -93.67278 | \n",
"
\n",
" \n",
" 05PC018 | \n",
" True | \n",
" True | \n",
" RAINY RIVER AT MANITOU RAPIDS | \n",
" 48.63447 | \n",
" -93.91336 | \n",
"
\n",
" \n",
" 05PC019 | \n",
" | \n",
" True | \n",
" RAINY RIVER AT FORT FRANCES | \n",
" 48.60853 | \n",
" -93.40344 | \n",
"
\n",
" \n",
" 05PC022 | \n",
" True | \n",
" True | \n",
" LA VALLEE RIVER NEAR BURRISS | \n",
" 48.67844 | \n",
" -93.66522 | \n",
"
\n",
" \n",
" 05PC024 | \n",
" True | \n",
" | \n",
" RAINY RIVER AT PITHERS POINT SITE NO.1 | \n",
" 48.61389 | \n",
" -93.35472 | \n",
"
\n",
" \n",
" 05PC025 | \n",
" True | \n",
" | \n",
" RAINY RIVER AT PITHERS POINT SITE NO.2 | \n",
" 48.61625 | \n",
" -93.35992 | \n",
"
\n",
" \n",
"
\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",
" 05PA003 | \n",
" 05PA005 | \n",
" 05PA006 | \n",
" 05PA007 | \n",
" 05PA010 | \n",
" 05PA011 | \n",
" 05PA012 | \n",
" 05PA013 | \n",
" 05PB002 | \n",
" 05PB007 | \n",
" 05PB012 | \n",
" 05PB014 | \n",
" 05PB018 | \n",
" 05PB023 | \n",
" 05PB024 | \n",
" 05PB025 | \n",
" 05PC018 | \n",
" 05PC022 | \n",
" 05PC024 | \n",
" 05PC025 | \n",
"
\n",
" \n",
" \n",
" \n",
" 2014-12-27 | \n",
" NaN | \n",
" NaN | \n",
" 360.451 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 339.987 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 0.915 | \n",
" 386.296 | \n",
" 8.433 | \n",
" 337.361 | \n",
" NaN | \n",
" NaN | \n",
" 10.775 | \n",
" 337.325 | \n",
" 337.324 | \n",
"
\n",
" \n",
" 2014-12-28 | \n",
" NaN | \n",
" NaN | \n",
" 360.449 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 339.981 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 0.913 | \n",
" 386.287 | \n",
" 8.426 | \n",
" 337.356 | \n",
" NaN | \n",
" NaN | \n",
" 10.779 | \n",
" 337.318 | \n",
" 337.317 | \n",
"
\n",
" \n",
" 2014-12-29 | \n",
" NaN | \n",
" NaN | \n",
" 360.447 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 339.975 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 0.910 | \n",
" 386.295 | \n",
" 8.421 | \n",
" 337.351 | \n",
" NaN | \n",
" NaN | \n",
" 10.788 | \n",
" 337.313 | \n",
" 337.312 | \n",
"
\n",
" \n",
" 2014-12-30 | \n",
" NaN | \n",
" NaN | \n",
" 360.445 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 339.968 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 0.907 | \n",
" 386.297 | \n",
" 8.418 | \n",
" 337.346 | \n",
" NaN | \n",
" NaN | \n",
" 10.796 | \n",
" 337.308 | \n",
" 337.307 | \n",
"
\n",
" \n",
" 2014-12-31 | \n",
" NaN | \n",
" NaN | \n",
" 360.444 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 339.962 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 0.904 | \n",
" 386.303 | \n",
" 8.418 | \n",
" 337.341 | \n",
" NaN | \n",
" NaN | \n",
" 10.795 | \n",
" 337.303 | \n",
" 337.301 | \n",
"
\n",
" \n",
"
\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",
" 05PA001 | \n",
" 05PA006 | \n",
" 05PA012 | \n",
" 05PB001 | \n",
" 05PB003 | \n",
" 05PB004 | \n",
" 05PB009 | \n",
" 05PB014 | \n",
" 05PB015 | \n",
" 05PB018 | \n",
" 05PB019 | \n",
" 05PB020 | \n",
" 05PB021 | \n",
" 05PB022 | \n",
" 05PC009 | \n",
" 05PC010 | \n",
" 05PC016 | \n",
" 05PC018 | \n",
" 05PC019 | \n",
" 05PC022 | \n",
"
\n",
" \n",
" \n",
" \n",
" 2014-12-27 | \n",
" NaN | \n",
" 36.7 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 36.9 | \n",
" 22.5 | \n",
" NaN | \n",
" 1.27 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 170.0 | \n",
" 0.027 | \n",
"
\n",
" \n",
" 2014-12-28 | \n",
" NaN | \n",
" 36.6 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 37.4 | \n",
" 22.4 | \n",
" NaN | \n",
" 1.26 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 170.0 | \n",
" 0.026 | \n",
"
\n",
" \n",
" 2014-12-29 | \n",
" NaN | \n",
" 36.5 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 37.8 | \n",
" 22.2 | \n",
" NaN | \n",
" 1.25 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 169.0 | \n",
" 0.026 | \n",
"
\n",
" \n",
" 2014-12-30 | \n",
" NaN | \n",
" 36.4 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 37.1 | \n",
" 22.0 | \n",
" NaN | \n",
" 1.24 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 169.0 | \n",
" 0.026 | \n",
"
\n",
" \n",
" 2014-12-31 | \n",
" NaN | \n",
" 36.3 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 38.0 | \n",
" 21.9 | \n",
" NaN | \n",
" 1.24 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 169.0 | \n",
" 0.026 | \n",
"
\n",
" \n",
"
\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
}