# 9Cleaning and Wrangling Data

## Learning Objectives

• Introduce `dplyr` and `tidyr` functions to clean and wrangle data for analysis
• Learn about the Split-Apply-Combine strategy and how it applies to data wrangling
• Describe the difference between wide vs. long table formats and how to convert between them

## 9.1 Introduction

The data we get to work with are rarely, if ever, in the format we need to do our analyses. It’s often the case that one package requires data in one format, while another package requires the data to be in another format. To be efficient analysts, we should have good tools for reformatting data for our needs so we can do further work like making plots and fitting models. The `dplyr` and `tidyr` R packages provide a fairly complete and extremely powerful set of functions for us to do this reformatting quickly. Learning these tools well will greatly increase your efficiency as an analyst.

Let’s look at two motivating examples.

Example 1

Suppose you have the following `data.frame` called `length_data` with data about salmon length and want to calculate the average length per year.

year length_cm
1990 5.673318
1991 3.081224
1991 4.592696
1992 4.381523
1992 5.597777
1992 4.900052

The `dplyr` R library provides a fast and powerful way to do this calculation in a few lines of code:

``````length_data %>%
group_by(year) %>%
summarize(mean_length_cm = mean(length_cm))``````
Example 2

Another process we often need to do is to “reshape” our data. Consider the following table that is in what we call “wide” format:

site 1990 1991 1993
gold 100 118 112
lake 100 118 112
dredge 100 118 112

You are probably familiar with data in the above format, where values of the variable being observed are spread out across columns. In this example we have a different column per year. This wide format works well for data entry and sometimes works well for analysis but we quickly outgrow it when using R (and know it is not tidy data!). For example, how would you fit a model with year as a predictor variable? In an ideal world, we’d be able to just run `lm(length ~ year)`. But this won’t work on our wide data because `lm()` needs `length` and `year` to be columns in our table.

The `tidyr` package allows us to quickly switch between wide format and long format using the `pivot_longer()` function:

``````site_data %>%
pivot_longer(-site, names_to = "year", values_to = "length")``````
site year length
gold 1990 101
lake 1990 104
dredge 1990 144
dredge 1993 145

This lesson will cover examples to learn about the functions you’ll most commonly use from the `dplyr` and `tidyr` packages:

Common `dplyr` functions
Function name Description
`mutate()` Creates modify and deletes columns
`group_by()` Groups data by one or more variables
`summarise()` Summaries each group down to one row
`select()` Keep or drop columns using their names
`filter()` Keeps rows that matches conditions
`arrange()` order rows using columns variable
`rename()` Rename a column
Common `tidyr` functions
Function name Description
`pivot_longer()` transforms data from a wide to a long format
`pivot_wider()` transforms data from a long to a wide format
`unite()` Unite multiple columns into one by pasting strings together
`separate()` Separate a character column into multiple columns with a regular expression or numeric locations

## 9.2 Data cleaning basics

To demonstrate, we’ll be working with a tidied up version of a data set from Alaska Department of Fish & Game containing commercial catch data from 1878-1997. The data set and reference to the original source can be found at its public archive.

Setup

First, open a new Quarto document. Delete everything below the setup chunk, and add a library chunk that calls `dplyr`, `tidyr`, and `readr`

``````library(dplyr)
library(tidyr)

You may have noticed the following messages pop up when you ran your library chunk.

``````Attaching package: ‘dplyr’

The following objects are masked from ‘package:stats’:

filter, lag

The following objects are masked from ‘package:base’:

intersect, setdiff, setequal, union``````

These are important messages. They are letting you know that certain functions from the `stats` and `base` packages (which are loaded by default when you start R) are masked by different functions with the same name in the `dplyr` package. It turns out, the order that you load the packages in matters. Since we loaded `dplyr` after `stats`, R will assume that if you call `filter()`, you mean the `dplyr` version unless you specify otherwise.

Being specific about which version of `filter()`, for example, you call is easy. To explicitly call a function by its unambiguous name, we use the syntax `package_name::function_name(...)`. So, if we wanted to call the `stats` version of `filter()` in this Rmarkdown document, I would use the syntax `stats::filter(...)`.

Remove messages and warnings

Messages and warnings are important, but we might not want them in our final document. After you have read the packages in, adjust the chunk settings in your library chunk to suppress warnings and messages by adding `#| message: false` or `#| warning: false`. Both of these chunk options, when set to false, prevents messages or warnings from appearing in the rendered file.

Now that we have introduced some data wrangling libraries, let’s get the data that we are going to use for this lesson.

Setup
1. Find the data file `byerlySalmonByRegion.csv`. Right click the “Download” button and select “Copy Link Address”

2. Paste the copied URL into the `read_csv()` function

The code chunk you use to read in the data should look something like this:

``catch_original <- read_csv("https://knb.ecoinformatics.org/knb/d1/mn/v2/object/df35b.302.1")``

Note for Windows users: Keep in mind, if you want to replicate this workflow in your local computer you also need to use the `url()` function here with the argument `method = "libcurl"`.

It would look like this:

``catch_original <- read.csv(url("https://knb.ecoinformatics.org/knb/d1/mn/v2/object/df35b.302.1", method = "libcurl"))``

This data set is relatively clean and easy to interpret as-is. While it may be clean, it’s in a shape that makes it hard to use for some types of analyses so we’ll want to fix that first.

Exercise

Before we get too much further, spend a minute or two outlining your Quarto document so that it includes the following sections and steps:

• Data Sources
• Explore data
• Clean and Reshape data
• Using `select()` function
• Check column types
• Replace values in a column
• Reshape data
• Rename columns `rename()`
• Summary stats using `group_by()` and `summarize()`
• Filtering rows using `filter()`
• Sort data using `arrange()`
• Split and combine values in columns

## 9.3 Data exploration

Similar to what we did in our Intro to Literate Analysis lesson, it is good practice to skim through the data you just read in.

Doing so is important to make sure the data is read as you were expecting and to familiarize yourself with the data.

Some of the basic ways to explore your data are:

``````## Prints the column names of my data frame
colnames(catch_original)

## First 6 lines of the data frame

## Summary of each column of data
summary(catch_original)

## Prints unique values in a column (in this case, the region)
unique(catch_original\$Region)

## Opens data frame in its own tab to see each row and column of the data (do in console)
View(catch_original)``````

## 9.4 About the pipe (`%>%`) operator

Before we jump into learning `tidyr` and `dplyr`, we first need to explain the pipeline operator `%>%`.

Both the `tidyr` and the `dplyr` packages use the pipe operator (`%>%`), which may look unfamiliar. The pipe is a powerful way to efficiently chain together operations. The pipe will take the output of a previous statement, and use it as the input to the next statement.

Say you want to both `filter()` out rows of a data set, and `select()` certain columns.

``````df_filtered <- filter(df, ...)
df_selected <- select(df_filtered, ...)``````

You can write:

``````df_cleaned <- df %>%
filter(...) %>%
select(...)``````

If you think of the assignment operator (`<-`) as reading like “gets”, then the pipe operator would read like “then”.

So you might think of the above chunk being translated as:

The cleaned data frame gets the original data, and then a filter (of the original data), and then a select (of the filtered data).

The benefits to using pipes are that you don’t have to keep track of (or overwrite) intermediate data frames. The drawbacks are that it can be more difficult to explain the reasoning behind each step, especially when many operations are chained together. It is good to strike a balance between writing efficient code (chaining operations), while ensuring that you are still clearly explaining, both to your future self and others, what you are doing and why you are doing it.

Quick Tip

RStudio has a keyboard shortcut for `%>%`

• Windows: `Ctrl` + `Shift` + `M`
• Mac: `cmd` + `shift` + `M`

## 9.5 Selecting or removing columns using `select()`

We’re ready to go back to our salmon dataset. The first issue is the extra columns `All` and `notesRegCode`. Let’s select only the columns we want, and assign this to a variable called `catch_data`.

``````catch_data <- catch_original %>%
select(Region, Year, Chinook, Sockeye, Coho, Pink, Chum)

Much better!

The `select()` function also allows you to say which columns you don’t want, by passing unquoted column names preceded by minus (`-`) signs:

``````catch_data <- catch_original %>%
select(-All,-notesRegCode)``````

## 9.6 Quality check

Now that we have the data we are interested in using, we should do a little quality check to see that everything seems as expected. One nice way of doing this is the `glimpse()` function.

``dplyr::glimpse(catch_data)``
Exercise

Examine the output of the `glimpse()` function call. Does anything seem amiss with this data set that might warrant fixing?

Answer: The Chinook catch data are `character` class. Let’s fix it using the function `mutate()` before moving on.

## 9.7 Changing column content using `mutate()`

We can use the `mutate()` function to change a column, or to create a new column. First, let’s try to convert the Chinook catch values to `numeric` type using the `as.numeric()` function, and overwrite the old Chinook column.

``````catch_clean <- catch_data %>%
mutate(Chinook = as.numeric(Chinook))

We get a warning `"NAs introduced by coercion"` which is R telling us that it couldn’t convert every value to an integer and, for those values it couldn’t convert, it put an `NA` in its place. This is behavior we commonly experience when cleaning data sets and it’s important to have the skills to deal with it when it comes up.

To investigate, let’s isolate the issue. We can find out which values are `NA`s with a combination of `is.na()` and `which()`, and save that to a variable called `i`.

``````i <- which(is.na(catch_clean\$Chinook))
i``````

It looks like there is only one problem row, lets have a look at it in the original data.

``catch_data[i,]``

Well that’s odd: The value in `catch_thousands` is the letter `I`. It turns out that this data set is from a PDF which was automatically converted into a `csv` and this value of `I` is actually a 1.

Let’s fix it by incorporating the `if_else()` function to our `mutate()` call, which will change the value of the `Chinook` column to 1 if the value is equal to `I`, then will use `as.numeric()` to turn the character representations of numbers into numeric typed values.

``````catch_clean <- catch_data %>%
mutate(Chinook = if_else(condition = Chinook == "I",
true = "1",
false = Chinook),
Chinook = as.numeric(Chinook))

##check
catch_clean[i, ]``````

## 9.8 Changing shape using `pivot_longer()` and `pivot_wider()`

The next issue is that the data are in a wide format and we want the data in a long format instead. The function `pivot_longer()` from the `tidyr` package helps us do this conversion. If you do not remember all the arguments that go into `pivot_longer()` you can always call the `help` page by typing `?pivot_longer` in the console.

``````catch_long <- catch_clean %>%
#pivot longer all columns except Region and Year
pivot_longer(
cols = -c(Region, Year),
names_to = "species",
values_to = "catch"
)

The syntax we used above for `pivot_longer()` might be a bit confusing so let’s walk though it.

• The first argument to `pivot_longer` is the columns over which we are pivoting. You can select these by listing either the names of the columns you do want to pivot, or in this case, the names of the columns you are not pivoting over.

• The `names_to` argument: this is the name of the column that you are creating from the column names of the columns you are pivoting over.

• The `values_to` argument: the name of the column that you are creating from the values in the columns you are pivoting over.

The opposite of `pivot_longer()` is the `pivot_wider()` function. It works in a similar declarative fashion:

``````catch_wide <- catch_long %>%
pivot_wider(names_from = species,
values_from = catch)

Same than we did above we can pull up the documentation of the function to remind ourselves what goes in which argument. Type `?pivot_wider` in the console.

## 9.9 Renaming columns with `rename()`

If you scan through the data, you may notice the values in the `catch` column are very small (these are supposed to be annual catches). If we look at the metadata we can see that the `catch` column is in thousands of fish, so let’s convert it before moving on.

Let’s first rename the `catch` column to be called `catch_thousands`:

``````catch_long <- catch_long %>%
rename(catch_thousands = catch)

`names()` versus `rename()`

Many people use the base R function `names()` to rename columns, often in combination with column indexing that relies on columns being in a particular order. Column indexing is often also used to select columns instead of the `select()` function from `dplyr`. Although these methods work just fine, they do have one major drawback: in most implementations they rely on you knowing exactly the column order your data is in.

To illustrate why your knowledge of column order isn’t reliable enough for these operations, considering the following scenario:

Your colleague emails you letting you know that she has an updated version of the conductivity-temperature-depth data from this year’s research cruise, and sends it along. Excited, you re-run your scripts that use this data for your phytoplankton research. You run the script and suddenly all of your numbers seem off. You spend hours trying to figure out what is going on.

Unbeknownst to you, your colleagues bought a new sensor this year that measures dissolved oxygen. Because of the new variables in the data set, the column order is different. Your script which previously renamed the fourth column, `SAL_PSU` to `salinity` now renames the fourth column, `O2_MGpL` to `salinity`. No wonder your results looked so weird, good thing you caught it!

If you had written your code so that it doesn’t rely on column order, but instead renames columns using the `rename()` function, the code would have run just fine (assuming the name of the original salinity column didn’t change, in which case the code would have thrown an error in an obvious way). This is an example of a defensive coding strategy, where you try to anticipate issues before they arise, and write your code in such a way as to keep the issues from happening.

## 9.10 Adding columns using `mutate()`

Now let’s use `mutate()` again to create a new column called `catch` with units of fish (instead of thousands of fish).

``````catch_long <- catch_long %>%
mutate(catch = catch_thousands * 1000)

Let’s remove the `catch_thousands` column for now since we don’t need it. Note that here we have added to the expression we wrote above by adding another function call (mutate) to our expression. This takes advantage of the pipe operator by grouping together a similar set of statements, which all aim to clean up the `catch_clean` data frame.

``````catch_long <- catch_long %>%
mutate(catch = catch_thousands * 1000) %>%
select(-catch_thousands)

We’re now ready to start analyzing the data.

## 9.11 Summary statistics using `group_by()` and `summarize()`

Suppose we are now interested in getting the average catch per region. In our initial data exploration we saw there are 18 regions, we can easily see their names again:

``unique(catch_original\$Region)``

Think about how we would calculate the average catch per region “by hand”. It would be something like this:

1. We start with our table and notice there are multiple regions in the “Regions” column.

2. We split our original table to group all observations from the same region together.

3. We calculate the average catch for each of the groups we form.

4. Then we combine the values for average catch per region into a single table.

Analyses like this conform to what is known as the Split-Apply-Combine strategy. This strategy follows the three steps we explained above:

1. Split: Split the data into logical groups (e.g., region, species, etc.)
2. Apply: Calculate some summary statistic on each group (e.g. mean catch by year, number of individuals per species)
3. Combine: Combine the statistic calculated on each group back together into a single table

The `dplyr` library lets us easily employ the Split-Apply-Combine strategy by using the `group_by()` and `summarize()` functions:

``````mean_region <- catch_long %>%
group_by(Region) %>%
summarize(mean_catch = mean(catch))

Let’s see how the previous code implements the Split-Apply-Combine strategy:

1. `group_by(Region)`: this is telling R to split the dataframe and create a group for each different value in the column `Region`. R just keeps track of the groups, it doesn’t return separate dataframes per region.

2. `mean(catch)`: here `mean` is the function we want to apply to the column `catch` in each group.

3. `summarize(catch = mean(catch))` the function `summarize()` is used to combine the results of `mean(catch)` in each group into a single table. The argument `mean_catch = mean(catch)` indicates that the column having the results of `mean(catch)` will be named `mean_catch`.

Another common use of `group_by()` followed by `summarize()` is to count the number of rows in each group. We have to use a special function from `dplyr`, `n()`.

``````n_region <- catch_long %>%
group_by(Region) %>%
summarize(n = n())

Try using `count()`

If you are finding that you are reaching for this combination of `group_by()`, `summarize()` and `n()` a lot, there is a helpful `dplyr` function `count()` that accomplishes this in one function!

Exercise
• Find another grouping and statistic to calculate for each group.
• Find out if you can group by multiple variables.
``````## for example:
catch_year_sp <- catch_long %>%
group_by(Year, species) %>%
summarize(total_year = sum(catch, na.rm = T))``````

## 9.12 Filtering rows using `filter()`

We use the `filter()` function to filter our `data.frame` to rows matching some condition. It’s similar to `subset()` from base R.

Let’s go back to our original `data.frame` and do some `filter()`ing:

``````sse_catch <- catch_long %>%
filter(Region == "SSE")

Exercise
• Filter to just catches of over one million fish
• Filter to just Chinook from the SSE region
``````## Catches over a million fish
catch_million <- catch_long %>%
filter(catch > 1000000)

## Chinook from SSE data
chinook_see <- catch_long %>%
filter(Region == "SSE",
species == "Chinook")

## OR
chinook_see <- catch_long %>%
filter(Region == "SSE" & species == "Chinook")``````

## 9.13 Sorting your data using `arrange()`

The `arrange()` function is used to sort the rows of a `data.frame`. Two common cases to use `arrange()` are:

• To calculate a cumulative sum (with `cumsum()`) so row order matters
• To display a table (like in an `.qmd` document) in sorted order

Let’s re-calculate mean catch by region, and then `arrange()` the output by mean catch:

``````mean_region <- catch_long %>%
group_by(Region) %>%
summarize(mean_catch = mean(catch)) %>%
arrange(mean_catch)

The default sorting order of `arrange()` is to sort in ascending order. To reverse the sort order, wrap the column name inside the `desc()` function:

``````mean_region <- catch_long %>%
group_by(Region) %>%
summarize(mean_catch = mean(catch)) %>%
arrange(desc(mean_catch))

## 9.14 Splitting a column using `separate()` and `unite()`

The `separate()` function allow us to easily split a single column into numerous. Its complement, the `unite()` function, allows us to combine multiple columns into a single one.

This can come in really handy when we need to split a column into two pieces by a consistent separator (like a dash).

Let’s make a new `data.frame` with fake data to illustrate this. Here we have a set of site identification codes with information about the island where the site is (the first 3 letters) and a site number (the 3 numbers). If we want to group and summarize by island, we need a column with just the island information.

``````sites_df <- data.frame(site = c("HAW-101",
"HAW-103",
"OAH-320",
"OAH-219",
"MAU-039"))

sites_df %>%
separate(site, c("island", "site_number"), "-")``````
Exercise

Split the `city` column in the data frame `cities_df` into `city` and `state_code` columns

``````## create `cities_df`
cities_df <- data.frame(city = c("Juneau AK",
"Sitka AK",
"Anchorage AK"))``````
``````colnames(cities_df)

cities_clean <- cities_df %>%
separate(city, c("city", "state_code"), " ")``````

The `unite()` function does just the reverse of `separate()`. If we have a `data.frame` that contains columns for year, month, and day, we might want to unite these into a single date column.

``````dates_df <- data.frame(
year = c("1930",
"1930",
"1930"),
month = c("12",
"12",
"12"),
day = c("14",
"15",
"16")
)

dates_df %>%
unite(date, year, month, day, sep = "-")``````

## 9.15 Now, all together!

We just ran through the various things we can do with `dplyr` and `tidyr` but if you’re wondering how this might look in a real analysis. Let’s look at that now:

``````catch_original <- read_csv(url("https://knb.ecoinformatics.org/knb/d1/mn/v2/object/df35b.302.1",
method = "libcurl"))

mean_region <- catch_original %>%
select(-All, -notesRegCode) %>%
mutate(Chinook = ifelse(Chinook == "I", 1, Chinook)) %>%
mutate(Chinook = as.numeric(Chinook)) %>%
pivot_longer(-c(Region, Year),
names_to = "species",
values_to = "catch") %>%
mutate(catch = catch*1000) %>%
group_by(Region) %>%
summarize(mean_catch = mean(catch)) %>%
arrange(desc(mean_catch))

1. Save the `.qmd` you have been working on for this lesson.
3. `Stage (Add) > Commit > Pull > Push`