<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/beyond-basic-r-data-munging/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://owi.usgs.gov/blog/data-munging/"> The USGS OWI blog </a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<h2>What we couldn’t cover</h2>
<p>In the data cleaning portion of our <a rel="nofollow" href="https://owi.usgs.gov/R/training-curriculum/intro-curriculum/Introduction/" target="_blank">Intro to R class</a>, we cover a variety of common data manipulation tasks. Most of these were achieved using the package <code>dplyr</code>, including removing or retaining certain columns (<code>select</code>), filtering out rows by column condition (<code>filter</code>), creating new columns (<code>mutate</code>), renaming columns (<code>rename</code>), grouping data by categorical variables (<code>group_by</code>), and summarizing data (<code>summarize</code>).</p>
<p>But often, data users need to do more complex manipulation of their data, like changing the shape of the data or creating a new column conditional on values in another column. In this post, we will use USGS stream data to demonstrate data munging techniques that are beyond the basics taught in our Intro to R course. If you’re following along and see a new function and want to learn more, a reminder that you can see the help file for that function by executing a <code>?</code> followed by the function name in your console to see the help file and examples.</p>
<h2>What we want to achieve</h2>
<p>In this example, a data user is interested in how nutrient concentrations are related to discharge in a stream within a mixed use watershed of urban and agricultural land use. The site is located in Dane County near Madison, WI, and we’re focusing on daily values from a 20-year period from 1998 to 2017. We’ll use these data to demonstrate a variety of data munging techniques that are beyond the basics.</p>
<h2>How to get there</h2>
<h3>Get the data</h3>
<p>The first step falls within what we’ve already learned in the Intro to R class: pull data from NWIS using the USGS package <code>dataRetrieval</code>, select the columns we want to keep, and rename columns.</p>
<pre><code># load libraries
library(dplyr)
library(dataRetrieval)
library(lubridate)
library(tidyr)
library(ggplot2)
library(viridis)

# Get data for the Yahara River at Windsor in Dane County, Wisconsin
yahara_no &lt;- '05427718'

# define parameters of interest, and get those parameter names
params &lt;- c('00060', '00671', '80154', '00665')

# get daily values from NWIS
yahara_dat &lt;- readNWISdv(siteNumbers = yahara_no, parameterCd = params, 
                         startDate = '1997-10-01', endDate = '2017-09-30')

# rename columns using renameNWISColumns from package dataRetrieval
yahara_dat &lt;- renameNWISColumns(yahara_dat, 
                                p00665 = &quot;TP_mgL&quot;,
                                p00671 = &quot;Orthophosphate_mgL&quot;,
                                p80154 = &quot;SS_mgL&quot;)
</code></pre>
<p>You may be seeing the pipe (<code>%>%</code>) for the first time in the above code snippet. The pipe comes from the package <code>magrittr</code> and is used in <code>dplyr</code> to connect commands. <code>dplyr</code> functions generally take arguments in the same order: the dataframe you want to manipulate, and what you want to do it. A pipe upstream of a command “pipes in” the results of the upstream function; that is, you no longer have to specify the dataframe you want to manipulate. It’s an efficient way to code if you don’t care to create intermediate data objects. You can read more about the history and functionality of the pipe in R from <a rel="nofollow" href="https://www.datacamp.com/community/tutorials/pipe-r-tutorial?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">DataCamp’s tutorial on pipes</a>.</p>
<h3>Match strings</h3>
<p>Notice that in our new dataset, we have retained code columns, or those that end in “_cd”. How can we quickly identify which columns contain that string? Here, we’ll introduce you to pattern matching functions (including <code>grep</code> and <code>gsub</code>) which have wide application to data munging. First, we’ll use grep to return the index of the string or the string itself that contains “_cd”.</p>
<pre><code># use function `grep` to identify which columns are code columns
yahara_names &lt;- names(yahara_dat)
grep('_cd', yahara_names) # returns the index of the match
</code></pre>
<pre><code>## [1]  1  5  7  9 11
</code></pre>
<pre><code>grep('_cd', yahara_names, value = TRUE) # returns the matched elements themselves
</code></pre>
<pre><code>## [1] &quot;agency_cd&quot;             &quot;Flow_cd&quot;               &quot;TP_mgL_cd&quot;            
## [4] &quot;Orthophosphate_mgL_cd&quot; &quot;SS_mgL_cd&quot;
</code></pre>
<pre><code># change the code column names to be more explicit about what they contain
# using function gsub
gsub('_cd', '_code', yahara_names)
</code></pre>
<pre><code>##  [1] &quot;agency_code&quot;             &quot;site_no&quot;                
##  [3] &quot;Date&quot;                    &quot;Flow&quot;                   
##  [5] &quot;Flow_code&quot;               &quot;TP_mgL&quot;                 
##  [7] &quot;TP_mgL_code&quot;             &quot;Orthophosphate_mgL&quot;     
##  [9] &quot;Orthophosphate_mgL_code&quot; &quot;SS_mgL&quot;                 
## [11] &quot;SS_mgL_code&quot;
</code></pre>
<p>The package <code>dplyr</code> also has some handy pattern matching functions built in. For example, we can use the function <code>contains</code> in <code>select</code> to exclude those columns with the “_cd” string.</p>
<pre><code>yahara_dat &lt;- select(yahara_dat, -contains('_cd'))
head(yahara_dat)
</code></pre>
<pre><code>##    site_no       Date Flow TP_mgL Orthophosphate_mgL SS_mgL
## 1 05427718 1997-10-01   11   0.05                 NA   18.8
## 2 05427718 1997-10-02   11   0.05                 NA   18.6
## 3 05427718 1997-10-03   12   0.05                 NA   18.5
## 4 05427718 1997-10-04   11   0.04                 NA   18.5
## 5 05427718 1997-10-05   11   0.04                 NA   18.4
## 6 05427718 1997-10-06   11   0.04                 NA   18.4
</code></pre>
<p>These functions use regular expression to evaluate matches, and can be used to find much more complex patterns. We’ve only scratched the surface here on what can be done with pattern matching. Further reading on the topic of regular expression in R that we recommend include RStudio’s <a rel="nofollow" href="https://www.rstudio.com/wp-content/uploads/2016/09/RegExCheatsheet.pdf" target="_blank">regular expression cheat sheet</a> and the <a rel="nofollow" href="http://r4ds.had.co.nz/strings.html" target="_blank">“strings” section from the R for Data Science book</a>.</p>
<h3>Create new variables to summarize by water year</h3>
<p>First, let’s compare discharge across water years. The current data frame only has dates, so we’ll need to create a water year and a day of water year variable to use in our comparisons. We can use the handy <code>addWaterYear</code> function from <code>dataRetrieval</code> to do so. Next, we’ll calculate cumulative discharge by day for each year using <code>group_by</code> and <code>mutate</code>.</p>
<pre><code># add water year variable &quot;waterYear&quot; to our dataframe
yahara_dat &lt;- addWaterYear(yahara_dat)

# calculate cumulative discharge for each year by first grouping by water year,
# and then using the &quot;cumsum&quot; function. Add day of water year for plotting purposes.
# These steps will build a new dataframe, with the existing information in yahara_dat
# but with two additional columns.
cumulative_dat &lt;- group_by(yahara_dat, waterYear) %>%
  mutate(cumulative_dis = cumsum(Flow), 
         wy_doy = seq(1:n()))
  
# visually compare cumulative discharge across years
ggplot(cumulative_dat, aes(x = wy_doy, y = cumulative_dis, group = waterYear)) +
  geom_line(aes(color = waterYear)) +
  scale_color_viridis_c() +
  scale_x_continuous(breaks = c(1, 93, 184, 275), labels = c(&quot;Oct 1&quot;, &quot;Jan 1&quot;, &quot;Apr 1&quot;, &quot;July 1&quot;)) +
  theme_bw() +
  labs(color = &quot;Water Year&quot;, x = &quot;&quot;, y = &quot;Cumulative Discharge&quot;)
</code></pre>
<p><img src="https://i2.wp.com/owi.usgs.gov/blog/static/beyond-basic-data-munging/cumulativedischarge-1.png?w=456&ssl=1" alt="Cumulative discharge (by water year) in the Yahara River." title="Cumulative discharge (by water year) in the Yahara River." data-recalc-dims="1"></p>
<h3>Create categorical variables from continuous data</h3>
<p>There is variability year-to-year in cumulative discharge and the timing of large discharge events. As we look at the nutrient ~ discharge relationships, we may want to keep track of which years were wet versus dry. That is, we want to categorize years based on the total annual discharge. Below, we show several ways to create a categorical variable from continuous data.</p>
<pre><code># sum discharge by water year, also check to be sure each year has 
# data for every day by counting observations in a year
year_sums &lt;- yahara_dat %>%
  filter(!is.na(Flow)) %>%
  group_by(waterYear) %>%
  summarize(yearly_discharge = sum(Flow, na.rm = TRUE),
            ndays = n()) 
</code></pre>
<p>First, we’ll use an <code>ifelse</code> statement to create a categorical variable based on TRUE or FALSE conditions.</p>
<pre><code># use ifelse to create a categorical variable that has two conditions
median_yearly_discharge &lt;- median(year_sums$yearly_discharge)
year_sums &lt;- mutate(year_sums, discharge_high_low = ifelse(yearly_discharge &lt;= median_yearly_discharge, 'low', 'high'))

# we should get equal group sizes of &quot;low&quot; and &quot;high&quot;
summary(as.factor(year_sums$discharge_high_low))
</code></pre>
<pre><code>## high  low 
##   10   10
</code></pre>
<p>Now suppose you want to create a variable with three categories: dry, normal, and wet. We’ll use the <code>cut</code> function to do this so we can give multiple numeric break points to create the categories. We’ll arbitrarily use the 25th and 75th percentiles to define our cutoffs values.</p>
<pre><code>cut_vals &lt;- quantile(year_sums$yearly_discharge, probs = c(0.25, 0.75), na.rm = TRUE)
year_sums &lt;- mutate(year_sums, 
                    discharge_dry_normal_wet = cut(yearly_discharge, 
                                                 breaks = c(-Inf, cut_vals, Inf), 
                                                 labels = c(&quot;dry&quot;, &quot;normal&quot;, &quot;wet&quot;)))

# we expect roughly twice as many &quot;normal&quot; years as &quot;wet&quot; or &quot;dry&quot; years
summary(as.factor(year_sums$discharge_dry_normal_wet))
</code></pre>
<pre><code>##    dry normal    wet 
##      5     10      5
</code></pre>
<p>In some instances, you may want to create categorical variables based on a more complex set of rules. For example, if there was an important management action in this watershed that occurred in the year 2000, you may want your low/high discharge categories to be further classified as before/after. We’ll use the function <code>case_when</code> to use multiple rules to define our categories.</p>
<pre><code>year_sums &lt;- year_sums %>%
  mutate(discharge_before_after = case_when(
    yearly_discharge > median_yearly_discharge &amp; waterYear > 2000 ~ &quot;High Discharge, After&quot;,
    yearly_discharge > median_yearly_discharge &amp; waterYear &lt;= 2000 ~ &quot;High Discharge, Before&quot;,
    yearly_discharge &lt;= median_yearly_discharge &amp; waterYear &lt;= 2000 ~ &quot;Low Discharge, Before&quot;,
    yearly_discharge &lt;= median_yearly_discharge &amp; waterYear > 2000 ~ &quot;Low Discharge, After&quot;
  ))

# check how many years of data fall into each category
summary(as.factor(year_sums$discharge_before_after))
</code></pre>
<pre><code>## High Discharge, After  Low Discharge, After Low Discharge, Before 
##                    10                     7                     3
</code></pre>
<h3>Gather or stack variables in long data frame using <code>gather</code></h3>
<p>We can plot the variables through time to see major discharge and concentration events. With our current data structure, we could easily create individual plots of all constituents and discharge through time – e.g., this plot of TP through time:</p>
<pre><code> ggplot(cumulative_dat, aes(x = Date, y = TP_mgL)) +
  geom_point() +
  labs(x = &quot;Date&quot;, y = 'TP (mg/L)') +
  theme_bw()
</code></pre>
<p><img src="https://i1.wp.com/owi.usgs.gov/blog/static/beyond-basic-data-munging/tp_time-1.png?w=456&ssl=1" alt="Total phosphorus concentration through time in the Yahara River." title="Total phosphorus concentration through time in the Yahara River." data-recalc-dims="1"></p>
<p>But what if we wanted to stack this figure with a discharge through time plot, where the panels are aligned by date and share x-axis labels? We can do this by faceting in <code>ggplot</code>, but our current data structure is wide (each variable is in its own column), and we need a single “variable” column with an associated “value” that is presented in long format. To get our data in long format, we will <code>gather</code> (from the package <code>tidyr</code>) the nutrient and discharge columns.</p>
<p>For the <code>gather</code> function, we need to provide a key name which will become the column where the measured parameter names are stored. We also provide a value name, which will become the column where the measured values are stored. Finally, we tell the gather function which columns we do (by listing) or do not (by list with a negative sign) want to gather by adding a negative sign to those column names.</p>
<pre><code>yahara_long &lt;- cumulative_dat %>%
  gather(key = measured_parameter, value = value, Flow, TP_mgL, Orthophosphate_mgL, SS_mgL)

head(yahara_long)
</code></pre>
<pre><code>## # A tibble: 6 x 7
## # Groups:   waterYear [1]
##   site_no  Date       waterYear cumulative_dis wy_doy measured_parameter
##   </code></pre>