3  Data wrangling part 2

Get the lesson R script: data_wrangling_2.R

Get the lesson data: download zip

3.1 Lesson Outline

3.2 Lesson Exercises

3.3 Goals

In this lesson we’ll continue our discussion of data wrangling with the tidyverse. Data wrangling is the manipulation or combination of datasets for the purpose of understanding. It fits within the broader scheme of data exploration, described as the art of looking at your data, rapidly generating hypotheses, quickly testing them, then repeating again and again and again (from R for Data Science, 2nd Ed., as is most of today’s content).

Always remember that wrangling is based on a purpose. The process always begins by answering the following two questions:

  • What do my input data look like?
  • What should my input data look like given what I want to do?

You define what steps to take to get your data from input to where you want to go.

Last lesson we learned the following functions from the dplyr package (cheatsheet here):

  • Selecting variables with select
  • Filtering observations by some criteria with filter
  • Adding or modifying existing variables with mutate
  • Working with the lubridate package

As before, we have a limited amount of time to cover the basics of data wrangling. It’s an unrealistic expectation that you will be a ninja wrangler after this training. As such, the goals are to expose you to fundamentals and to develop an appreciation of what’s possible. We also provide resources that you can use for follow-up learning on your own.

After this lesson you should be able to answer (or be able to find answers to) the following:

  • How are data joined?
  • What is tidy data?
  • How do I summarize a dataset?

You should already have the tidyverse package installed, but let’s give it a go if you haven’t done this part yet:

# install
install.packages('tidyverse')

After installation, we can load the package:

library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

3.4 Combining data

Combining data is a common task of data wrangling. Perhaps we want to combine information between two datasets that share a common identifier. As a real world example, our training data contain measurements from various stations, but we also want to include spatial information about those stations (i.e., latitude, longitude). We would need to combine data if this information is in two different places. Combining data with dplyr is called joining.

All joins require that each of the tables can be linked by shared identifiers. These are called ‘keys’ and are usually represented as a separate column that acts as a unique variable for the observations. The station_name is our common key, but remember that a key might need to be unique for each row. It doesn’t make sense to join two tables by station if multiple site visits were made. In that case, your keys should include some information about the site visit and station. In other words, you would need to join with two variables.

3.4.1 Types of joins

The challenge with joins is that the two datasets may not represent the same observations for a given key. For example, you might have one table with all observations for every key, another with only some observations, or two tables with only a few shared keys. What you get back from a join will depend on what’s shared between tables, in addition to the type of join you use.

We can demonstrate types of joins with simple graphics. The first is an inner-join.

The second is an outer-join, and comes in three flavors: left, right, and full.

If all keys are shared between two data objects, then inner, left, right, and full joins will give you the same result.

The data we downloaded for this training included station metadata and water quality measurements at the stations. If we want to plot any of the data by location, we need to join the two datasets.

library(here)

# load the metadata
metadat <- read_csv(here("data", "metadat.csv"))

# load the water quality data
wqdat <- read_csv(here("data", "wqdat.csv"))

Let’s do some quick wrangling to get the data ready for joining. We’ll remove the unnecessary columns from metadat and make sure that timestamp in wqdat is in the correct format.

# select station name, lat, lon
metadat <- metadat |> 
  select(station_name, station_latitude, station_longitude)

# format time zone
wqdat <- wqdat |> 
  mutate(timestamp = force_tz(timestamp, tzone = "Etc/GMT+5"))

Now we can join the two datasets. We’ll use left_join() to combine the two datasets by the station_name key. We’re using a left_join because we know that there are fewer unique stations in the water quality data than the metadata. An inner_join would have also produced the same result. The result will be a new dataset that contains all of the water quality data, plus the station latitude and longitude.

# join the two 
joindat <- left_join(wqdat, metadat, by = 'station_name')
dim(joindat)
[1] 51931     9
head(joindat)
# A tibble: 6 × 9
  station_no station_name       parametertype_shortname parametertype_name
       <dbl> <chr>              <chr>                   <chr>             
1      23113 Lake Panasoffkee 7 WQ_79                   Alkalinity (CaCO3)
2      23113 Lake Panasoffkee 7 WQ_79                   Alkalinity (CaCO3)
3      23113 Lake Panasoffkee 7 WQ_79                   Alkalinity (CaCO3)
4      23113 Lake Panasoffkee 7 WQ_79                   Alkalinity (CaCO3)
5      23113 Lake Panasoffkee 7 WQ_79                   Alkalinity (CaCO3)
6      23113 Lake Panasoffkee 7 WQ_79                   Alkalinity (CaCO3)
# ℹ 5 more variables: timestamp <dttm>, value <dbl>, unit_symbol <chr>,
#   station_latitude <dbl>, station_longitude <dbl>

A final note about joins is that you will have relationships between two tables defined as one-to-one, one-to-many, many-to-one, or many-to-many. Often times you may expect a one-to-one join (i.e., one row in table x will always correspond to one row in table y), but another relationship is observed (e.g., one row in table x corresponds to more than one row in table y, a one-to-many join). The join functions in dplyr will notify you if this occurs depending on the type of join you use. This is good because it tells you how to interpret the results of the join and whether or not you may have unexpected results. In general, you should have a prior expectation of the relationship between two tables. You can be explicit with the type of relationship you expect by including the arguments relationship = 'one-to-one' (or other types) in your join function.

3.5 Exercise 6

For this exercise we’ll repeat the join we just did, but on a subset of the data. We’re going to select some columns of interest from our training dataset, filter by station, then use full_join() with the station location data. Try to use pipes if you can.

  1. Select the station_name, timestamp, parametertype_name, and value columns from the water quality dataset.

  2. Filter by station_name to get only station "Lake Panasoffkee 8" and parametertype_name to get only "Temperature, Water"

  3. Use a full_join to join the dataset with the metadata. What is the key value for joining?

  4. Check the dimensions of the new table. What happened?

Click to show/hide solution 
# wrangle before join
joindat <- wqdat |> 
  select(station_name, timestamp, parametertype_name, value) |> 
  filter(station_name == "Lake Panasoffkee 8" & parametertype_name == "Temperature, Water")

dim(joindat)

# full join
joindat <- joindat |> 
  full_join(metadat, by = 'station_name')

dim(joindat)

3.6 Tidy data

The opposite of a tidy dataset is a messy dataset. You should always work towards a tidy data set as an outcome of the wrangling process. Tidy data are easy to work with and will make downstream analysis much simpler. This will become apparent when we start summarizing and plotting our data.

To help understand tidy data, it’s useful to look at alternative ways of representing data. The example below shows the same data organised in four different ways. Each dataset shows the same values of four variables country, year, population, and cases, but each dataset organises the values differently. Only one of these examples is tidy.

table1
# A tibble: 6 × 4
  country      year  cases population
  <chr>       <dbl>  <dbl>      <dbl>
1 Afghanistan  1999    745   19987071
2 Afghanistan  2000   2666   20595360
3 Brazil       1999  37737  172006362
4 Brazil       2000  80488  174504898
5 China        1999 212258 1272915272
6 China        2000 213766 1280428583
table2
# A tibble: 12 × 4
   country      year type            count
   <chr>       <dbl> <chr>           <dbl>
 1 Afghanistan  1999 cases             745
 2 Afghanistan  1999 population   19987071
 3 Afghanistan  2000 cases            2666
 4 Afghanistan  2000 population   20595360
 5 Brazil       1999 cases           37737
 6 Brazil       1999 population  172006362
 7 Brazil       2000 cases           80488
 8 Brazil       2000 population  174504898
 9 China        1999 cases          212258
10 China        1999 population 1272915272
11 China        2000 cases          213766
12 China        2000 population 1280428583
table3
# A tibble: 6 × 3
  country      year rate             
  <chr>       <dbl> <chr>            
1 Afghanistan  1999 745/19987071     
2 Afghanistan  2000 2666/20595360    
3 Brazil       1999 37737/172006362  
4 Brazil       2000 80488/174504898  
5 China        1999 212258/1272915272
6 China        2000 213766/1280428583
# Spread across two tibbles
table4a  # cases
# A tibble: 3 × 3
  country     `1999` `2000`
  <chr>        <dbl>  <dbl>
1 Afghanistan    745   2666
2 Brazil       37737  80488
3 China       212258 213766
table4b  # population
# A tibble: 3 × 3
  country         `1999`     `2000`
  <chr>            <dbl>      <dbl>
1 Afghanistan   19987071   20595360
2 Brazil       172006362  174504898
3 China       1272915272 1280428583

These are all representations of the same underlying data but they are not equally easy to work with. The tidy dataset is much easier to work with inside the tidyverse.

There are three inter-correlated rules which make a dataset tidy:

  1. Each variable must have its own column.
  2. Each observation must have its own row.
  3. Each value must have its own cell.

There are some very real reasons why you would encounter untidy data:

  1. Most people aren’t familiar with the principles of tidy data, and it’s hard to derive them yourself unless you spend a lot of time working with data.

  2. Data is often organised to facilitate some use other than analysis. For example, data is often organised to make entry as easy as possible.

The tidyr package can help you create tidy datasets. A full review of the functions in tidyr is beyond the scope of this training. Have a look at the cheatsheet to get started. Just know that the package is available to help create tidy data.

For the example tables above, only the first table is tidy. The second table, although not technically tidy (variables spread across rows), is still a useful format that we’ll work with later. We’ll talk about how to manipulate a dataset to work with both formats using two functions from tidyr: pivot_longer() and pivot_wider().

3.6.1 Values as column names

A common problem is a dataset where some of the column names are not names of variables, but values of a variable. Take table4a: the column names 1999 and 2000 represent values of the year variable, and each row represents two observations, not one.

table4a
# A tibble: 3 × 3
  country     `1999` `2000`
  <chr>        <dbl>  <dbl>
1 Afghanistan    745   2666
2 Brazil       37737  80488
3 China       212258 213766

To tidy a dataset like this, we need to combine those columns into a new pair of variables. To describe that operation we need three parameters:

  • The set of columns that represent values, not variables. In this example, those are the columns 1999 and 2000.

  • The name of the variable whose values form the column name. Here it is year.

  • The name of the variable whose values are spread over the cells. Here it’s the number of cases.

Together those parameters generate the call to pivot_longer():

table4a |>
  pivot_longer(c('1999', '2000'), names_to = "year", values_to = "cases")
# A tibble: 6 × 3
  country     year   cases
  <chr>       <chr>  <dbl>
1 Afghanistan 1999     745
2 Afghanistan 2000    2666
3 Brazil      1999   37737
4 Brazil      2000   80488
5 China       1999  212258
6 China       2000  213766

This operation can be graphically demonstrated:

3.6.2 Observations across rows

Another problem is when you have a single observation scattered across rows. For example, table2 shows that the number of cases and population is spread across rows where the observation is country and year.

table2
# A tibble: 12 × 4
   country      year type            count
   <chr>       <dbl> <chr>           <dbl>
 1 Afghanistan  1999 cases             745
 2 Afghanistan  1999 population   19987071
 3 Afghanistan  2000 cases            2666
 4 Afghanistan  2000 population   20595360
 5 Brazil       1999 cases           37737
 6 Brazil       1999 population  172006362
 7 Brazil       2000 cases           80488
 8 Brazil       2000 population  174504898
 9 China        1999 cases          212258
10 China        1999 population 1272915272
11 China        2000 cases          213766
12 China        2000 population 1280428583

To tidy this up, we first analyse the representation in a similar way as before. This time, however, we only need to identify two things:

  • The column that contains variable names. Here it’s type.

  • The column that contains values from multiple variables. Here it’s count.

Once we’ve figured that out, we can use pivot_wider().

pivot_wider(table2, names_from = 'type', values_from = 'count')
# A tibble: 6 × 4
  country      year  cases population
  <chr>       <dbl>  <dbl>      <dbl>
1 Afghanistan  1999    745   19987071
2 Afghanistan  2000   2666   20595360
3 Brazil       1999  37737  172006362
4 Brazil       2000  80488  174504898
5 China        1999 212258 1272915272
6 China        2000 213766 1280428583

This operation can be graphically demonstrated:

3.7 Exercise 7

Let’s take a look at the water quality data. Are these data “tidy”? To demonstrate a different format, we’ll use pivot_wider() to manipulate the data.

  1. Inspect the water quality dataset. What are the dimensions (hint: dim())? What are the names and column types (hint: str())?

  2. Remove the unit_symbol and parametertype_shortname columns and convert timestamp to date using as.Date(). Assign the new dataset to a variable in your environment. Why do we need to do this?

  3. The data are in long format with parameters spread across rows. We can convert it to wide format with one parameter per column using the pivot_wider() function. What value will you use for the names_from argument? What value will you use for the values_from argument? Also add the argument values_fn = mean. We’ll explain what this does in a minute. Assign the new dataset to a variable in your environment.

  4. Check the dimensions and structure of your new dataset. What’s different?

Click to show/hide solution 
# check dimensions, structure
dim(wqdat)
str(wqdat)

# convert dat to wide format
widedat <- wqdat |>
  select(-unit_symbol, -parametertype_shortname) |>
  mutate(timestamp = as.Date(timestamp)) |> 
  pivot_wider(names_from = parametertype_name, values_from = value, values_fn = mean)

# check dimensions, structure
dim(widedat)
str(widedat)

3.8 Summarize

The last tool we’re going to learn about in dplyr is the summarize function. As the name implies, this function lets you summarize columns in a dataset. Think of it as a way to condense rows using a summary method of your choice, e.g., what’s the average of the values in a column based on groups in another column?

We can use the summarize() function to get the total or average value by group. Note the use of the .by argument as a critical piece that defines the groups for summarizing. The mean function also uses the optional argument na.rm = T to remove missing values from the calculation, otherwise, the result will be NA.

by_sta <- summarize(wqdat, mean_val = mean(value, na.rm = T), .by = c(station_name, parametertype_name))
by_sta
# A tibble: 241 × 3
   station_name       parametertype_name         mean_val
   <chr>              <chr>                         <dbl>
 1 Lake Panasoffkee 7 Alkalinity (CaCO3)         133.    
 2 Lake Panasoffkee 7 Ammonia (N)                  0.0443
 3 Lake Panasoffkee 7 Arsenic                      6.22  
 4 Lake Panasoffkee 7 Bicarbonate (CaCO3)        132.    
 5 Lake Panasoffkee 7 Cadmium                      0.3   
 6 Lake Panasoffkee 7 Calcium                     58.9   
 7 Lake Panasoffkee 7 Carbon- Total Organic       12.5   
 8 Lake Panasoffkee 7 Chloride                    11.2   
 9 Lake Panasoffkee 7 Chlorophyll a- corrected    19.6   
10 Lake Panasoffkee 7 Chlorophyll a- uncorrected  20.6   
# ℹ 231 more rows

The below example is the same as the previous, except we’re also grouping by year, first by creating the Year variable using mutate() and the lubridate::year() function (note: :: lets you use a function without loading the entire package). The result is a summary of the average value by station, parameter, and year.

by_sta_yr <- wqdat |> 
  mutate(Year = lubridate::year(timestamp)) |> 
  summarize(mean_val = mean(value, na.rm = TRUE), .by = c(station_name, parametertype_name, Year))
by_sta_yr
# A tibble: 4,339 × 4
   station_name       parametertype_name  Year mean_val
   <chr>              <chr>              <dbl>    <dbl>
 1 Lake Panasoffkee 7 Alkalinity (CaCO3)  2000     80.8
 2 Lake Panasoffkee 7 Alkalinity (CaCO3)  2001    130. 
 3 Lake Panasoffkee 7 Alkalinity (CaCO3)  2002    132. 
 4 Lake Panasoffkee 7 Alkalinity (CaCO3)  2003    148. 
 5 Lake Panasoffkee 7 Alkalinity (CaCO3)  2004    141. 
 6 Lake Panasoffkee 7 Alkalinity (CaCO3)  2005    155. 
 7 Lake Panasoffkee 7 Alkalinity (CaCO3)  2006    109. 
 8 Lake Panasoffkee 7 Alkalinity (CaCO3)  2007    107. 
 9 Lake Panasoffkee 7 Alkalinity (CaCO3)  2008    146. 
10 Lake Panasoffkee 7 Alkalinity (CaCO3)  2009    128. 
# ℹ 4,329 more rows

We can also get more than one summary at a time. The summary function can use any function that operates on a vector. Some common examples include min(), max(), sd(), var(), median(), mean(), and n(). It’s usually good practice to include a summary of how many observations were in each group using n().

more_sums <- summarize(wqdat, 
    n = n(),
    min_val = min(value),
    max_val = max(value),
    mean_val = mean(value), 
    .by = c(station_name, parametertype_name)
  )
more_sums
# A tibble: 241 × 6
   station_name       parametertype_name             n min_val max_val mean_val
   <chr>              <chr>                      <int>   <dbl>   <dbl>    <dbl>
 1 Lake Panasoffkee 7 Alkalinity (CaCO3)           205   1     197     133.    
 2 Lake Panasoffkee 7 Ammonia (N)                  212   0.005   0.631   0.0443
 3 Lake Panasoffkee 7 Arsenic                      149   0.6    11.2     6.22  
 4 Lake Panasoffkee 7 Bicarbonate (CaCO3)            7  73.4   162     132.    
 5 Lake Panasoffkee 7 Cadmium                        4   0.3     0.3     0.3   
 6 Lake Panasoffkee 7 Calcium                      158  18.6   128      58.9   
 7 Lake Panasoffkee 7 Carbon- Total Organic        221   3.24   33.3    12.5   
 8 Lake Panasoffkee 7 Chloride                     215   6.74   16.5    11.2   
 9 Lake Panasoffkee 7 Chlorophyll a- corrected     174   1     118.     19.6   
10 Lake Panasoffkee 7 Chlorophyll a- uncorrected   146   1.41  116      20.6   
# ℹ 231 more rows

3.9 Exercise 8

Now we have access to a several tools in the tidyverse to help us wrangle more effectively. For the final exercise, we’re going to subset our training data and get a summary. Specifically, we’ll filter our data by a specific station and parameter, then summarize the average value by year.

  1. Using wqdat, filter the data by a specific station and parameter.

  2. Summarize the value column by taking the average grouped by year.

  3. Which year has the highest average value?

Click to show/hide solution 
sumdat <- wqdat |>
  filter(station_name == 'Lake Panasoffkee 4' & parametertype_name == 'Temperature, Water') |> 
  mutate(Year = lubridate::year(timestamp)) |> 
  summarize(
    ave = mean(value, na.rm = TRUE), 
    .by = Year
  ) |> 
  arrange(-ave)
sumdat

3.10 Next time

Now you should be able to answer (or be able to find answers to) the following:

  • How are data joined?
  • What is tidy data?
  • How do I summarize a dataset?

In the next lesson we’ll learn about data visualization and graphics.

3.11 Attribution

Content in this lesson was pillaged extensively from the USGS-R training curriculum here and R for Data Science.