In bcgov/tidyhydat: Extract and Tidy Canadian 'Hydrometric' Data

"Tidy datasets are all alike but every messy dataset is messy in its own way - " @wickham2014tidy

knitr::opts_chunk$set(echo = TRUE, warning = FALSE, messages = FALSE, fig.width = 8, fig.height = 12)

library(tidyhydat)
library(dplyr)
library(dbplyr)

Introduction

Environment and Climate Change Canada (ECCC) through the Water Survey of Canada (WSC) maintains several national hydrometric data sources. These data are partially funded by provincial partners and constitute the main data products of a national integrated hydrometric network. Historical data are stored in the HYDAT database. HYDAT is the Canadian national Water Data Archive, published quarterly by the Government of Canada's Department of Environment and Climate Change. It is relational database that contains daily, monthly and annual data on water flow, water levels and sediment.

Real-time data are provided by ECCC over the web. Files are updated to a datamart on an hourly basis though the lag between actual hydrometric measurement and the availability of hydrometric data is approximately 2.5 hours. The objective of this document is the outline the usage of tidyhydat [@alberstidyhydat], an R package that accesses these hydrometric data sources and tidies them. tidyhydat is part of the rOpenSci suite of packages and resides at https://github.com/ropensci/tidyhydat. The objective of tidyhydat is to provide a standard method of accessing ECCC data sources using a consistent and easy to use interface that employs tidy data principles developed by @wickham2014tidy within the R project [@RCore].

Why use R in hydrology?

There are many statistical computing projects that offer great functionality for users. For tidyhydat I have chosen to use R. R is a mature open-source project that provides significant potential for advanced modelling, visualization and data manipulation. For hydrologists considering data analysis tools there are several commonly cited reasons to use R:

R is and always will be free to use and modify.
R is easily extensible and comprehensive. It is complimented by a rich suite of packages that implement a vast array of classical and modern statistical methods, exceptionally high-quality graphing capabilities and powerful data manipulation tools to handle a wide variety of data formats.
R facilitates the scientific method by allowing for a fully reproducible data workflow that can be repeated by others when code is shared.
R has a friendly community which is an important infrastructure element of any open source project.

There have been recent calls to use R more broadly in the field of hydrology [@moore2017watershed]. The tidyhydat package is an effort to push this call forward by being a standard package by which hydrologists and other users interact with WSC data in R. Conducting hydrological analysis in a programming environment like R allows hydrologists the ability to create fully reproducible workflows, automate repetitive tasks and provide the same rigour to the data analysis process that hydrologists apply to field equipment and experimental design [@wilson2014best].

Why use tidy data?

Embedded within tidyhydat is the principle of tidy data. @wickham2014tidy defines tidy data by three principles:

Each variable forms a column
Each observation forms a row
Each type of observational unit forms a table

It is illustrative here to provide an example of the types of data tidying processes that tidyhydat does for you automatically. The raw DLY_FLOWS table in the HYDAT database returns data that looks like this:

hydat_con <- DBI::dbConnect(RSQLite::SQLite(), system.file("test_db/tinyhydat.sqlite3", package = "tidyhydat"))
tbl(hydat_con, "DLY_FLOWS")

This data structure clearly violates the principles of tidy data - this is messy data. For example, column headers (e.g. FLOW1) contain the day number - a value. HYDAT is structured like this for very reasonable historical reasons. It does, however, significantly limit a hydrologists ability to efficiently use hydrometric data.

tidyhydat aims to make interacting with WSC data sources simpler. I have applied tidy data principles so that users can avoid thinking about the basic data process of importing and tidying and focus on the iterative process of visualizing and modelling their data [@wickham2016r]. After loading tidyhydat itself, we simply need to supply a station_number argument to the hy_daily_flows() function:

library(tidyhydat)
hy_daily_flows(station_number = "08MF005")

As you can see, this is much tidier data and is much easier to work with. In addition to these tidy principles, specific to tidyhydat, we can also define that for a common data source, variables should be referred to by a common name. For example, hydrometric stations are given a unique 7 digit identifier that contains important watershed information. This identifier is variously referred to as STATION_NUMBER or ID depending on the exact ECCC data source. To tidy this hydrometric data, we have renamed, where necessary, each instance of the unique identifier as STATION_NUMBER. This consistency to data formats, and in particular tidy data, situates tidyhydat well to interact seamlessly with the powerful tools being developed in the tidyverse [@wickham2017tidyverse] and provides a path in R to realize some of the goals outlined by @moore2017watershed.