Wrangling the Current Reservoir Levels data set

First look at the data

We’ll use the head() function to take a look.

My first observations are that these column names are completely unintelligible and that everything appears to be numbers, which should simplify getting the correct types. Let’s look at how read_csv() interpreted the column types.

I’ll use skimr::skim() to return the types because it returns the column types without fuss and, unlike glimpse(), it works when piped into kable() and therefore is prettier to display. I’m starting to learn more about the complexity of package interaction: there are a lot of packages and use cases which do not play well together.

There’s a lot of information here, but we’re interested in the first two columns. Every number was correctly interpreted, but Point_time was interpreted as a character when it is actually a date. So we have two fairly simple corrections to make: rename the columns, and change the date column to a date data type.

Adding human-friendly column headers

After taking a look at the data dictionary from the NYC Open Data page to figure out how to interpret these columns, I see that they provide labels that are much more human-friendly. We’ll use those instead and rename using dplyr::rename():

The names are good, but notice how the dates are arranged now. I added arrange(Date) to sort the rows by date because they were out of order in the raw data set. Since they are a character class, arrange() interpreted them alphanumberically, in other words 01/01/2019 comes before 01/01/2020 rather than coming before 01/02/2019.

To correct this, we’ll convert the first column to dates using lubridate::mdy(), confirm with class(), and look again.

data$Date <- data$Date %>%
  mdy()
class(data$Date)

## [1] "Date"

Great. The date is now arranged in ascending date order and displayed in R’s standard YYYY-MM-DD format, which is fine for our purposes here. Now we’re ready to talk about the tidiness of the data.

Semantics of the data

Tidy data is not a new concept, but its formal study and the popularity of the term have recently grown in stature with the importance of machine learning in modern business and the need for consistent approaches to manipulating data.

Hadley Wickham’s 2013 paper on the topic and continued work on the tidyverse have also facilitated the spread of the concept, and there are plenty of good introductions and breakdowns of the topic. I like the R tidy data vignette.

Tidy data by definition has three elements:

1. Each column represents a variable
2. Each row represents an observation
3. Each cell contains one value

Some treatments also add “each table represents one level of observation.” So, is the Current Reservoir Levels data set tidy?

To answer that, we need to explore the semantics a bit. The larger context of the data set is the water supply of New York City, which is a fascinating world of engineering in itself.

In our data set, we have the six upstate reservoirs represented which supply about 90% of the city’s water. They are from the Catskills (Ashokan and Schoharie) and Delaware (Rondout, Neversink, Pepacton, Cannonsville) watersheds. For each reservoir, there are three types of measurements, and some basins have multiple sites for each type:

1. Storage in billion gallons (BG)
2. Elevation in feet (FT)
3. Release in millions of gallons per day (MGD)

Storage represents how much water is in the reservoir, elevation represents the height of the surface of the water, and release represents how much water leaves the reservoir. Now we can consider what each column name means in terms of tidiness.

When our second column says “Ashokan East Storage,” that means it’s a measurement from the Ashokan reservoir, measuring storage, in the East Basin. When another column says “Schoharie Storage,” that means it’s a measurement from the Schoharie reservoir measuring storage. This means some columns have three variables per column, and all columns have at least two variables.

Since tidy data requires that each variable is its own column, our data is not tidy. To correct this, we will pivot our table using pivot_longer() and regular expressions (regex).

Pivoting the data

It’s simple to say what we want to do to make the data tidy: move the reservoir name to its own column, move the measurement type to its own column, and move the measurement site (where applicable) to its own column. Finally, we want to move the values to their own column.

This is where the actual difficulty and frustration in tech work happnes. How do I do this task given the tools I know? Are there other tools? There are many ways to do any given part, but how do I chain everything together in a way that works? Am I asking the wrong question? Am I approaching the solution from the wrong angle?

I suspect there’s no way to get over this initial hump other than having people to ask for help, expending elbow grease, taking deep dives into the documentation, gaining experience, and perfecting your Google Fu.

Once we break down the complex operation we want to perform into this simpler series of instructions, and then identify a set of tools that act accordingly (and in the way we expect them to), it’s not too difficult to transform the data. In this case, we are pivoting variables embedded in the column headers to transform them into new rows.

To tell R how we specifically want to break up the column headers, we will use regex. To avoid an overly complex single operation that is unnecessary and possibly impossible, we will break this pivot down into multiple steps. First, we’ll separate the reservoir name and measurement type.

The reservoir name is the first word in the columns, and is then separated with a space from the rest of the title (the measurement site and type). We want a regex that says: one word, followed by exactly one space (which we’ll ignore), and then everything else. Those expressions are \w*, \s{1}, and .* respectively.

There are tons of search results and doc pages, but I am starting to find a pattern in that R’s (and particularly the tidyverse’s) vignettes are very helpful when approaching a new topic. I like R’s vignettes, which are long-form narratives on a topic that include narrative cases with code. If docs are a cookbook, vignettes are a supermarket.

In this case, the vignette("pivot") page’s “Many variables in column names” section gave me exactly the use case I needed to put my code together. I thought about trying to achieve all three transformations in one step, but I already had smoke coming out of my ears and didn’t want to overdo it.

This is not my first experience with pivoting, but it has taken a lot of mental effort to get used to the logic behind the operation and the specific way that pivot_longer() achieves it. Once I was able to mentally visualize the spatial transformation the function was meant to achieve, I felt comfortable that I wasn’t just typing randomly.

Here’s the solution I came up with:

data <- data %>%
  pivot_longer(
    2:25,
    names_to = c("Reservoir", "Measurement Type"),
    names_pattern = "(\\w*)\\s{1}(.*)",
    values_to = "Value"
  )
glimpse(data)

## Rows: 31,416
## Columns: 4
## $ Date               <date> 2017-11-01, 2017-11-01, 2017-11-01, 2017-11-01, 20…
## $ Reservoir          <chr> "Ashokan", "Ashokan", "Ashokan", "Ashokan", "Ashoka…
## $ `Measurement Type` <chr> "East Storage", "East Elevation", "West Elevation",…
## $ Value              <dbl> 65.36, 577.86, 36.34, 577.89, 18.00, 12.92, 1109.86…

I used glimpse() here instead of head() because I wanted to illustrate a point: untidy data is much more compact. The original data had 1,309 rows, and pivoted it has 31,416 rows. The original dataset, with 25 columns, had an area of 32,725 cells, and the transformed data, with 4 columns, has 125,664 cells.

Splitting columns with extract()

To split the Site variable off of the Measurement Type column, I’ll call extract(). I’m sure it’s possible to write a regex to do this in one step, but since functions like extract() and pivot_longer() are calling underlying functions that have their own rules and quirks, I’m not going to fight the gods and will instead just run it through extract() twice.

I’ll also rename the current Measurement Type column because we’ll be replacing it with a newly extracted column.

data <- data %>%
  rename('temp'='Measurement Type') %>%
  extract(
    "temp",
    "Site",
    "(North|South|Conservation Flow|West|East)",
    remove=FALSE
    ) %>%
  extract(
    "temp",
    "Measurement Type",
    "(Storage|Elevation|Release)"
    )
data %>%
  head(100) %>%
  kbl() %>%
  kable_styling(font_size=12) %>%
  scroll_box(height="15em")

Are these particularly graceful regexes? Absolutely not. A computer science student would get points deducted for submitting something like this on a homework assignment. Yet they work with this data set in this context with these function calls, and that’s good enough for now. Not every meal can be a feast of legend.

The other issue I see after converting these columns is that their type is character. Since Reservoir, Site, and Measurement Type are categories, they should be factor data types. We’ll convert them using as_factor().

data$Reservoir <- data$Reservoir %>%
  as_factor()
data$`Measurement Type` <- data$`Measurement Type` %>%
  as_factor()
data$Site <- data$Site %>%
  as_factor()
summary(data)

##       Date                   Reservoir     Measurement Type
##  Min.   :2017-11-01   Ashokan     :6545   Storage  : 9163  
##  1st Qu.:2018-09-24   Schoharie   :3927   Elevation: 9163  
##  Median :2019-08-17   Rondout     :3927   Release  :13090  
##  Mean   :2019-08-17   Neversink   :6545                    
##  3rd Qu.:2020-07-09   Pepacton    :6545                    
##  Max.   :2021-05-31   Cannonsville:3927                    
##                 Site           Value        
##  East             : 2618   Min.   :   0.00  
##  West             : 2618   1st Qu.:  18.42  
##  North            : 2618   Median :  74.83  
##  South            : 2618   Mean   : 349.25  
##  Conservation Flow: 2618   3rd Qu.: 586.46  
##  NA's             :18326   Max.   :1572.72

Finally, we’ll save the data locally so that we don’t need to rewrite functions for all the transformations we’ve done the next time we’re working the data.

write.csv(data, file="/path/to/file.csv")

Conclusion

After retrieving the data, and before doing higher level work that explores the meaning of the data and looks for value, we have to make sure that the data is in a form we can use and ensure that it is not garbage. Changing the form is data wrangling (or munging), which we’ve done here.

I am now more comfortable with the form of this data. I don’t really like Values having multiple scales represented in one column (feet, billions of gallons, millions of gallons per day) because it makes the statistics calculated for the Values column meaningless. As I explore the data more, it may make sense to break this column apart.

Since the data is tidy, we are ready to look at the values present and make sure they are accurate, sane, and ready for higher level exploratory data analysis (EDA) techniques. Wrangling the data is not a one-time process, and as we explore and visualize the data, the shape of the table can change to meet the needs of the analysis.