Working with data
Last updated on 2023-05-02 | Edit this page
Estimated time 124 minutes
Overview
Questions
- How do you manipulate tabular data in R?
Objectives
- Import CSV data into R.
- Understand the difference between base R and
tidyverseapproaches. - Subset rows and columns of data.frames.
- Use pipes to link steps together into pipelines.
- Create new data.frame columns using existing columns.
- Utilize the concept of split-apply-combine data analysis.
- Reshape data between wide and long formats.
- Export data to a CSV file.
R
library(tidyverse)
Importing data
Up until this point, we have been working with the
complete_old dataframe contained in the ratdat
package. However, you typically won’t access data from an R package; it
is much more common to access data files stored somewhere on your
computer. We are going to download a CSV file containing the surveys
data to our computer, which we will then read into R.
Click this link to download the file: https://www.michaelc-m.com/Rewrite-R-ecology-lesson/data/cleaned/surveys_complete_77_89.csv.
You will be prompted to save the file on your computer somewhere.
Save it inside the cleaned data folder, which is in the
data folder in your R-Ecology-Workshop folder.
Once it’s inside our project, we will be able to point R towards it.
File paths
When we reference other files from an R script, we need to give R
precise instructions on where those files are. We do that using
something called a file path. It looks something like
this: "Documents/Manuscripts/Chapter_2.txt". This path
would tell your computer how to get from whatever folder contains the
Documents folder all the way to the .txt
file.
There are two kinds of paths: absolute and
relative. Absolute paths are specific to a particular
computer, whereas relative paths are relative to a certain folder.
Because we are keeping all of our work in the
R-Ecology-Workshop folder, all of our paths can be relative
to this folder.
Now, let’s read our CSV file into R and store it in an object named
surveys. We will use the read_csv function
from the tidyverse’s readr package, and the
argument we give will be the relative path to the CSV
file.
R
surveys <- read_csv("data/cleaned/surveys_complete_77_89.csv")
OUTPUT
Rows: 16878 Columns: 13
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (6): species_id, sex, genus, species, taxa, plot_type
dbl (7): record_id, month, day, year, plot_id, hindfoot_length, weight
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.Callout
Typing out paths can be error prone, so we can utilize a keyboard
shortcut. Inside the parentheses of read_csv(), type out a
pair of quotes and put your cursor between them. Then hit
Tab. A small menu showing your folders and files should show
up. You can use the ↑ and ↓ keys to move through
the options, or start typing to narrow them down. You can hit
Enter to select a file or folder, and hit Tab
again to continue building the file path. This might take a bit of
getting used to, but once you get the hang of it, it will speed up
writing file paths and reduce the number of mistakes you make.
You may have noticed a bit of feedback from R when you ran the last line of code. We got some useful information about the CSV file we read in. We can see:
- the number of rows and columns
- the delimiter of the file, which is how values are
separated, a comma
"," - a set of columns that were parsed as various vector
types
- the file has 6 character columns and 7 numeric columns
- we can see the names of the columns for each type
When working with the output of a new function, it’s often a good
idea to check the class():
R
class(surveys)
OUTPUT
[1] "spec_tbl_df" "tbl_df" "tbl" "data.frame" Whoa! What is this thing? It has multiple classes? Well, it’s called
a tibble, and it is the tidyverse version of a
data.frame. It is a data.frame, but with some added perks. It
prints out a little more nicely, it highlights NA values
and negative values in red, and it will generally communicate with you
more (in terms of warnings and errors, which is a good thing).
Callout
tidyverse vs. base R
As we begin to delve more deeply into the tidyverse, we
should briefly pause to mention some of the reasons for focusing on the
tidyverse set of tools. In R, there are often many ways to
get a job done, and there are other approaches that can accomplish tasks
similar to the tidyverse.
The phrase base R is used to refer to approaches
that utilize functions contained in R’s default packages. We have
already used some base R functions, such as str(),
head(), and mean(), and we will be using more
scattered throughout this lesson. However, there are some key base R
approaches we will not be teaching. These include square bracket
subsetting and base plotting. You may come across code written by other
people that looks like surveys[1:10, 2] or
plot(surveys$weight, surveys$hindfoot_length), which are
base R commands. If you’re interested in learning more about these
approaches, you can check out other Carpentries lessons like the Software
Carpentry Programming with R lesson.
We choose to teach the tidyverse set of packages because
they share a similar syntax and philosophy, making them consistent and
producing highly readable code. They are also very flexible and
powerful, with a growing number of packages designed according to
similar principles and to work well with the rest of the packages. The
tidyverse packages tend to have very clear documentation
and wide array of learning materials that tend to be written with novice
users in mind. Finally, the tidyverse has only continued to
grow, and has strong support from RStudio, which implies that these
approaches will be relevant into the future.
Manipulating data
One of the most important skills for working with data in R is the
ability to manipulate, modify, and reshape data. The dplyr
and tidyr packages in the tidyverse provide a
series of powerful functions for many common data manipulation
tasks.
We’ll start off with two of the most commonly used dplyr
functions: select(), which selects certain columns of a
data.frame, and filter(), which filters out rows according
to certain criteria.
select()
To use the select() function, the first argument is the
name of the data.frame, and the rest of the arguments are
unquoted names of the columns you want:
R
select(surveys, plot_id, species_id, hindfoot_length)
OUTPUT
# A tibble: 16,878 × 3
plot_id species_id hindfoot_length
<dbl> <chr> <dbl>
1 2 NL 32
2 3 NL 33
3 2 DM 37
4 7 DM 36
5 3 DM 35
6 1 PF 14
7 2 PE NA
8 1 DM 37
9 1 DM 34
10 6 PF 20
# … with 16,868 more rowsThe columns are arranged in the order we specified inside
select().
To select all columns except specific columns, put a -
in front of the column you want to exclude:
R
select(surveys, -record_id, -year)
OUTPUT
# A tibble: 16,878 × 11
month day plot_id specie…¹ sex hindf…² weight genus species taxa plot_…³
<dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <chr> <chr>
1 7 16 2 NL M 32 NA Neot… albigu… Rode… Control
2 7 16 3 NL M 33 NA Neot… albigu… Rode… Long-t…
3 7 16 2 DM F 37 NA Dipo… merria… Rode… Control
4 7 16 7 DM M 36 NA Dipo… merria… Rode… Rodent…
5 7 16 3 DM M 35 NA Dipo… merria… Rode… Long-t…
6 7 16 1 PF M 14 NA Pero… flavus Rode… Specta…
7 7 16 2 PE F NA NA Pero… eremic… Rode… Control
8 7 16 1 DM M 37 NA Dipo… merria… Rode… Specta…
9 7 16 1 DM F 34 NA Dipo… merria… Rode… Specta…
10 7 16 6 PF F 20 NA Pero… flavus Rode… Short-…
# … with 16,868 more rows, and abbreviated variable names ¹species_id,
# ²hindfoot_length, ³plot_typeselect() also works with numeric vectors for the order
of the columns. To select the 3rd, 4th, 5th, and 10th columns, we could
run the following code:
R
select(surveys, c(3:5, 10))
OUTPUT
# A tibble: 16,878 × 4
day year plot_id genus
<dbl> <dbl> <dbl> <chr>
1 16 1977 2 Neotoma
2 16 1977 3 Neotoma
3 16 1977 2 Dipodomys
4 16 1977 7 Dipodomys
5 16 1977 3 Dipodomys
6 16 1977 1 Perognathus
7 16 1977 2 Peromyscus
8 16 1977 1 Dipodomys
9 16 1977 1 Dipodomys
10 16 1977 6 Perognathus
# … with 16,868 more rowsYou should be careful when using this method, since you are being less explicit about which columns you want. However, it can be useful if you have a data.frame with many columns and you don’t want to type out too many names.
Finally, you can select columns based on whether they match a certain
criteria by using the where() function. If we want all
numeric columns, we can ask to select all the columns
where the class is numeric:
R
select(surveys, where(is.numeric))
OUTPUT
# A tibble: 16,878 × 7
record_id month day year plot_id hindfoot_length weight
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1 7 16 1977 2 32 NA
2 2 7 16 1977 3 33 NA
3 3 7 16 1977 2 37 NA
4 4 7 16 1977 7 36 NA
5 5 7 16 1977 3 35 NA
6 6 7 16 1977 1 14 NA
7 7 7 16 1977 2 NA NA
8 8 7 16 1977 1 37 NA
9 9 7 16 1977 1 34 NA
10 10 7 16 1977 6 20 NA
# … with 16,868 more rowsInstead of giving names or positions of columns, we instead pass the
where() function with the name of another function inside
it, in this case is.numeric(), and we get all the columns
for which that function returns TRUE.
We can use this to select any columns that have any NA
values in them:
R
select(surveys, where(anyNA))
OUTPUT
# A tibble: 16,878 × 7
species_id sex hindfoot_length weight genus species taxa
<chr> <chr> <dbl> <dbl> <chr> <chr> <chr>
1 NL M 32 NA Neotoma albigula Rodent
2 NL M 33 NA Neotoma albigula Rodent
3 DM F 37 NA Dipodomys merriami Rodent
4 DM M 36 NA Dipodomys merriami Rodent
5 DM M 35 NA Dipodomys merriami Rodent
6 PF M 14 NA Perognathus flavus Rodent
7 PE F NA NA Peromyscus eremicus Rodent
8 DM M 37 NA Dipodomys merriami Rodent
9 DM F 34 NA Dipodomys merriami Rodent
10 PF F 20 NA Perognathus flavus Rodent
# … with 16,868 more rowsfilter()
The filter() function is used to select rows that meet
certain criteria. To get all the rows where the value of
year is equal to 1985, we would run the following:
R
filter(surveys, year == 1985)
OUTPUT
# A tibble: 1,438 × 13
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 9790 1 19 1985 16 RM F 16 4 Reit… megalo…
2 9791 1 19 1985 17 OT F 20 16 Onyc… torrid…
3 9792 1 19 1985 6 DO M 35 48 Dipo… ordii
4 9793 1 19 1985 12 DO F 35 40 Dipo… ordii
5 9794 1 19 1985 24 RM M 16 4 Reit… megalo…
6 9795 1 19 1985 12 DO M 34 48 Dipo… ordii
7 9796 1 19 1985 6 DM F 37 35 Dipo… merria…
8 9797 1 19 1985 14 DM M 36 45 Dipo… merria…
9 9798 1 19 1985 6 DM F 36 38 Dipo… merria…
10 9799 1 19 1985 19 RM M 16 4 Reit… megalo…
# … with 1,428 more rows, 2 more variables: taxa <chr>, plot_type <chr>, and
# abbreviated variable names ¹record_id, ²species_id, ³hindfoot_lengthThe == sign means “is equal to”. There are several other
operators we can use: >, >=, <, <=, and != (not equal to).
Another useful operator is %in%, which asks if the value on
the lefthand side is found anywhere in the vector on the righthand side.
For example, to get rows with specific species_id values,
we could run:
R
filter(surveys, species_id %in% c("RM", "DO"))
OUTPUT
# A tibble: 2,835 × 13
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 68 8 19 1977 8 DO F 32 52 Dipo… ordii
2 292 10 17 1977 3 DO F 36 33 Dipo… ordii
3 294 10 17 1977 3 DO F 37 50 Dipo… ordii
4 311 10 17 1977 19 RM M 18 13 Reit… megalo…
5 317 10 17 1977 17 DO F 32 48 Dipo… ordii
6 323 10 17 1977 17 DO F 33 31 Dipo… ordii
7 337 10 18 1977 8 DO F 35 41 Dipo… ordii
8 356 11 12 1977 1 DO F 32 44 Dipo… ordii
9 378 11 12 1977 1 DO M 33 48 Dipo… ordii
10 397 11 13 1977 17 RM F 16 7 Reit… megalo…
# … with 2,825 more rows, 2 more variables: taxa <chr>, plot_type <chr>, and
# abbreviated variable names ¹record_id, ²species_id, ³hindfoot_lengthWe can also use multiple conditions in one filter()
statement. Here we will get rows with a year less than or equal to 1988
and whose hindfoot length values are not NA. The
! before the is.na() function means “not”.
R
filter(surveys, year <= 1988 & !is.na(hindfoot_length))
OUTPUT
# A tibble: 12,779 × 13
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 1 7 16 1977 2 NL M 32 NA Neot… albigu…
2 2 7 16 1977 3 NL M 33 NA Neot… albigu…
3 3 7 16 1977 2 DM F 37 NA Dipo… merria…
4 4 7 16 1977 7 DM M 36 NA Dipo… merria…
5 5 7 16 1977 3 DM M 35 NA Dipo… merria…
6 6 7 16 1977 1 PF M 14 NA Pero… flavus
7 8 7 16 1977 1 DM M 37 NA Dipo… merria…
8 9 7 16 1977 1 DM F 34 NA Dipo… merria…
9 10 7 16 1977 6 PF F 20 NA Pero… flavus
10 11 7 16 1977 5 DS F 53 NA Dipo… specta…
# … with 12,769 more rows, 2 more variables: taxa <chr>, plot_type <chr>, and
# abbreviated variable names ¹record_id, ²species_id, ³hindfoot_lengthR
surveys_filtered <- filter(surveys, year >= 1980 & year <= 1985)
R
surveys_selected <- select(surveys, year, month, species_id, plot_id)
The pipe: %>%
What happens if we want to both select() and
filter() our data? We have a couple options. First, we
could use nested functions:
R
filter(select(surveys, -day), month >= 7)
OUTPUT
# A tibble: 8,244 × 12
record…¹ month year plot_id speci…² sex hindf…³ weight genus species taxa
<dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <chr>
1 1 7 1977 2 NL M 32 NA Neot… albigu… Rode…
2 2 7 1977 3 NL M 33 NA Neot… albigu… Rode…
3 3 7 1977 2 DM F 37 NA Dipo… merria… Rode…
4 4 7 1977 7 DM M 36 NA Dipo… merria… Rode…
5 5 7 1977 3 DM M 35 NA Dipo… merria… Rode…
6 6 7 1977 1 PF M 14 NA Pero… flavus Rode…
7 7 7 1977 2 PE F NA NA Pero… eremic… Rode…
8 8 7 1977 1 DM M 37 NA Dipo… merria… Rode…
9 9 7 1977 1 DM F 34 NA Dipo… merria… Rode…
10 10 7 1977 6 PF F 20 NA Pero… flavus Rode…
# … with 8,234 more rows, 1 more variable: plot_type <chr>, and abbreviated
# variable names ¹record_id, ²species_id, ³hindfoot_lengthR will evaluate statements from the inside out. First,
select() will operate on the surveys
data.frame, removing the column day. The resulting
data.frame is then used as the first argument for filter(),
which selects rows with a month greater than or equal to 7.
Nested functions can be very difficult to read with only a few functions, and nearly impossible when many functions are done at once. An alternative approach is to create intermediate objects:
R
surveys_noday <- select(surveys, -day)
filter(surveys_noday, month >= 7)
OUTPUT
# A tibble: 8,244 × 12
record…¹ month year plot_id speci…² sex hindf…³ weight genus species taxa
<dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <chr>
1 1 7 1977 2 NL M 32 NA Neot… albigu… Rode…
2 2 7 1977 3 NL M 33 NA Neot… albigu… Rode…
3 3 7 1977 2 DM F 37 NA Dipo… merria… Rode…
4 4 7 1977 7 DM M 36 NA Dipo… merria… Rode…
5 5 7 1977 3 DM M 35 NA Dipo… merria… Rode…
6 6 7 1977 1 PF M 14 NA Pero… flavus Rode…
7 7 7 1977 2 PE F NA NA Pero… eremic… Rode…
8 8 7 1977 1 DM M 37 NA Dipo… merria… Rode…
9 9 7 1977 1 DM F 34 NA Dipo… merria… Rode…
10 10 7 1977 6 PF F 20 NA Pero… flavus Rode…
# … with 8,234 more rows, 1 more variable: plot_type <chr>, and abbreviated
# variable names ¹record_id, ²species_id, ³hindfoot_lengthThis approach is easier to read, since we can see the steps in order, but after enough steps, we are left with a cluttered mess of intermediate objects, often with confusing names.
An elegant solution to this problem is an operator called the
pipe, which looks like %>%. You can
insert it by using the keyboard shortcut Shift+Cmd+M (Mac) or
Shift+Ctrl+M (Windows). Here’s how you could use a pipe to
select and filter in one step:
R
surveys %>%
select(-day) %>%
filter(month >= 7)
OUTPUT
# A tibble: 8,244 × 12
record…¹ month year plot_id speci…² sex hindf…³ weight genus species taxa
<dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr> <chr>
1 1 7 1977 2 NL M 32 NA Neot… albigu… Rode…
2 2 7 1977 3 NL M 33 NA Neot… albigu… Rode…
3 3 7 1977 2 DM F 37 NA Dipo… merria… Rode…
4 4 7 1977 7 DM M 36 NA Dipo… merria… Rode…
5 5 7 1977 3 DM M 35 NA Dipo… merria… Rode…
6 6 7 1977 1 PF M 14 NA Pero… flavus Rode…
7 7 7 1977 2 PE F NA NA Pero… eremic… Rode…
8 8 7 1977 1 DM M 37 NA Dipo… merria… Rode…
9 9 7 1977 1 DM F 34 NA Dipo… merria… Rode…
10 10 7 1977 6 PF F 20 NA Pero… flavus Rode…
# … with 8,234 more rows, 1 more variable: plot_type <chr>, and abbreviated
# variable names ¹record_id, ²species_id, ³hindfoot_lengthWhat it does is take the thing on the lefthand side and insert it as
the first argument of the function on the righthand side. By putting
each of our functions onto a new line, we can build a nice, readable
pipeline. It can be useful to think of this as a little
assembly line for our data. It starts at the top and gets piped into a
select() function, and it comes out modified somewhat. It
then gets sent into the filter() function, where it is
further modified, and then the final product gets printed out to our
console. It can also be helpful to think of %>% as
meaning “and then”. Since many tidyverse functions have
verbs for names, a pipeline can be read like a sentence.
It’s worth showing the learners that you can run a pipeline without highlighting the whole thing. If your cursor is on any line of a pipeline, running that line will run the whole thing.
You can also show that by highlighting a section of a pipeline, you can run only the first X steps of it.
If we want to store this final product as an object, we use an assignment arrow at the start:
R
surveys_sub <- surveys %>%
select(-day) %>%
filter(month >= 7)
A good approach is to build a pipeline step by step prior to assignment. You add functions to the pipeline as you go, with the results printing in the console for you to view. Once you’re satisfied with your final result, go back and add the assignment arrow statement at the start. This approach is very interactive, allowing you to see the results of each step as you build the pipeline, and produces nicely readable code.
R
surveys_1988 <- surveys %>%
filter(year == 1988) %>%
select(record_id, month, species_id)
Make sure to filter() before you select().
You need to use the year column for filtering rows, but it
is discarded in the select() step. You also need to make
sure to use == instead of = when you are
filtering rows where year is equal to 1988.
Making new columns with mutate()
Another common task is creating a new column based on values in existing columns. For example, we could add a new column that has the weight in kilograms instead of grams:
R
surveys %>%
mutate(weight_kg = weight / 1000)
OUTPUT
# A tibble: 16,878 × 14
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 1 7 16 1977 2 NL M 32 NA Neot… albigu…
2 2 7 16 1977 3 NL M 33 NA Neot… albigu…
3 3 7 16 1977 2 DM F 37 NA Dipo… merria…
4 4 7 16 1977 7 DM M 36 NA Dipo… merria…
5 5 7 16 1977 3 DM M 35 NA Dipo… merria…
6 6 7 16 1977 1 PF M 14 NA Pero… flavus
7 7 7 16 1977 2 PE F NA NA Pero… eremic…
8 8 7 16 1977 1 DM M 37 NA Dipo… merria…
9 9 7 16 1977 1 DM F 34 NA Dipo… merria…
10 10 7 16 1977 6 PF F 20 NA Pero… flavus
# … with 16,868 more rows, 3 more variables: taxa <chr>, plot_type <chr>,
# weight_kg <dbl>, and abbreviated variable names ¹record_id, ²species_id,
# ³hindfoot_lengthYou can create multiple columns in one mutate() call,
and they will get created in the order you write them. This means you
can even reference the first new column in the second new column:
R
surveys %>%
mutate(weight_kg = weight / 1000,
weight_lbs = weight_kg * 2.2)
OUTPUT
# A tibble: 16,878 × 15
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 1 7 16 1977 2 NL M 32 NA Neot… albigu…
2 2 7 16 1977 3 NL M 33 NA Neot… albigu…
3 3 7 16 1977 2 DM F 37 NA Dipo… merria…
4 4 7 16 1977 7 DM M 36 NA Dipo… merria…
5 5 7 16 1977 3 DM M 35 NA Dipo… merria…
6 6 7 16 1977 1 PF M 14 NA Pero… flavus
7 7 7 16 1977 2 PE F NA NA Pero… eremic…
8 8 7 16 1977 1 DM M 37 NA Dipo… merria…
9 9 7 16 1977 1 DM F 34 NA Dipo… merria…
10 10 7 16 1977 6 PF F 20 NA Pero… flavus
# … with 16,868 more rows, 4 more variables: taxa <chr>, plot_type <chr>,
# weight_kg <dbl>, weight_lbs <dbl>, and abbreviated variable names
# ¹record_id, ²species_id, ³hindfoot_lengthWe can also use multiple columns to create a single column. For example, it’s often good practice to keep the components of a date in separate columns until necessary, as we’ve done here. This is because programs like Excel can do automatic things with dates in a way that is not reproducible and sometimes hard to notice. However, now that we are working in R, we can safely put together a date column.
To put together the columns into something that looks like a date, we
can use the paste() function, which takes arguments of the
items to paste together, as well as the argument sep, which
is the character used to separate the items.
R
surveys %>%
mutate(date = paste(year, month, day, sep = "-"))
OUTPUT
# A tibble: 16,878 × 14
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 1 7 16 1977 2 NL M 32 NA Neot… albigu…
2 2 7 16 1977 3 NL M 33 NA Neot… albigu…
3 3 7 16 1977 2 DM F 37 NA Dipo… merria…
4 4 7 16 1977 7 DM M 36 NA Dipo… merria…
5 5 7 16 1977 3 DM M 35 NA Dipo… merria…
6 6 7 16 1977 1 PF M 14 NA Pero… flavus
7 7 7 16 1977 2 PE F NA NA Pero… eremic…
8 8 7 16 1977 1 DM M 37 NA Dipo… merria…
9 9 7 16 1977 1 DM F 34 NA Dipo… merria…
10 10 7 16 1977 6 PF F 20 NA Pero… flavus
# … with 16,868 more rows, 3 more variables: taxa <chr>, plot_type <chr>,
# date <chr>, and abbreviated variable names ¹record_id, ²species_id,
# ³hindfoot_lengthSince our new column gets moved all the way to the end, it doesn’t
end up printing out. We can use the relocate() function to
put it after our year column:
R
surveys %>%
mutate(date = paste(year, month, day, sep = "-")) %>%
relocate(date, .after = year)
OUTPUT
# A tibble: 16,878 × 14
record_id month day year date plot_id speci…¹ sex hindf…² weight genus
<dbl> <dbl> <dbl> <dbl> <chr> <dbl> <chr> <chr> <dbl> <dbl> <chr>
1 1 7 16 1977 1977-… 2 NL M 32 NA Neot…
2 2 7 16 1977 1977-… 3 NL M 33 NA Neot…
3 3 7 16 1977 1977-… 2 DM F 37 NA Dipo…
4 4 7 16 1977 1977-… 7 DM M 36 NA Dipo…
5 5 7 16 1977 1977-… 3 DM M 35 NA Dipo…
6 6 7 16 1977 1977-… 1 PF M 14 NA Pero…
7 7 7 16 1977 1977-… 2 PE F NA NA Pero…
8 8 7 16 1977 1977-… 1 DM M 37 NA Dipo…
9 9 7 16 1977 1977-… 1 DM F 34 NA Dipo…
10 10 7 16 1977 1977-… 6 PF F 20 NA Pero…
# … with 16,868 more rows, 3 more variables: species <chr>, taxa <chr>,
# plot_type <chr>, and abbreviated variable names ¹species_id,
# ²hindfoot_lengthNow we can see that we have a character column that contains our date
string. However, it’s not truly a date column. Dates are a type of
numeric variable with a defined, ordered scale. To turn this column into
a proper date, we will use a function from the tidyverse’s
lubridate package, which has lots of useful functions for
working with dates. The function ymd() will parse a date
string that has the order year-month-day. Let’s load the package and use
ymd().
R
library(lubridate)
OUTPUT
Attaching package: 'lubridate'OUTPUT
The following objects are masked from 'package:base':
date, intersect, setdiff, unionR
surveys %>%
mutate(date = paste(year, month, day, sep = "-"),
date = ymd(date)) %>%
relocate(date, .after = year)
OUTPUT
# A tibble: 16,878 × 14
record_id month day year date plot_id species…¹ sex hindf…² weight
<dbl> <dbl> <dbl> <dbl> <date> <dbl> <chr> <chr> <dbl> <dbl>
1 1 7 16 1977 1977-07-16 2 NL M 32 NA
2 2 7 16 1977 1977-07-16 3 NL M 33 NA
3 3 7 16 1977 1977-07-16 2 DM F 37 NA
4 4 7 16 1977 1977-07-16 7 DM M 36 NA
5 5 7 16 1977 1977-07-16 3 DM M 35 NA
6 6 7 16 1977 1977-07-16 1 PF M 14 NA
7 7 7 16 1977 1977-07-16 2 PE F NA NA
8 8 7 16 1977 1977-07-16 1 DM M 37 NA
9 9 7 16 1977 1977-07-16 1 DM F 34 NA
10 10 7 16 1977 1977-07-16 6 PF F 20 NA
# … with 16,868 more rows, 4 more variables: genus <chr>, species <chr>,
# taxa <chr>, plot_type <chr>, and abbreviated variable names ¹species_id,
# ²hindfoot_lengthR
surveys %>%
mutate(date = paste(year, month, day, sep = "-"),
date = as.Date(date)) %>%
relocate(date, .after = year)
OUTPUT
# A tibble: 16,878 × 14
record_id month day year date plot_id species…¹ sex hindf…² weight
<dbl> <dbl> <dbl> <dbl> <date> <dbl> <chr> <chr> <dbl> <dbl>
1 1 7 16 1977 1977-07-16 2 NL M 32 NA
2 2 7 16 1977 1977-07-16 3 NL M 33 NA
3 3 7 16 1977 1977-07-16 2 DM F 37 NA
4 4 7 16 1977 1977-07-16 7 DM M 36 NA
5 5 7 16 1977 1977-07-16 3 DM M 35 NA
6 6 7 16 1977 1977-07-16 1 PF M 14 NA
7 7 7 16 1977 1977-07-16 2 PE F NA NA
8 8 7 16 1977 1977-07-16 1 DM M 37 NA
9 9 7 16 1977 1977-07-16 1 DM F 34 NA
10 10 7 16 1977 1977-07-16 6 PF F 20 NA
# … with 16,868 more rows, 4 more variables: genus <chr>, species <chr>,
# taxa <chr>, plot_type <chr>, and abbreviated variable names ¹species_id,
# ²hindfoot_lengthNow we can see that our date column has the type
date as well. In this example, we created our column with
two separate lines in mutate(), but we can combine them
into one:
R
# using nested functions
surveys %>%
mutate(date = ymd(paste(year, month, day, sep = "-"))) %>%
relocate(date, .after = year)
OUTPUT
# A tibble: 16,878 × 14
record_id month day year date plot_id species…¹ sex hindf…² weight
<dbl> <dbl> <dbl> <dbl> <date> <dbl> <chr> <chr> <dbl> <dbl>
1 1 7 16 1977 1977-07-16 2 NL M 32 NA
2 2 7 16 1977 1977-07-16 3 NL M 33 NA
3 3 7 16 1977 1977-07-16 2 DM F 37 NA
4 4 7 16 1977 1977-07-16 7 DM M 36 NA
5 5 7 16 1977 1977-07-16 3 DM M 35 NA
6 6 7 16 1977 1977-07-16 1 PF M 14 NA
7 7 7 16 1977 1977-07-16 2 PE F NA NA
8 8 7 16 1977 1977-07-16 1 DM M 37 NA
9 9 7 16 1977 1977-07-16 1 DM F 34 NA
10 10 7 16 1977 1977-07-16 6 PF F 20 NA
# … with 16,868 more rows, 4 more variables: genus <chr>, species <chr>,
# taxa <chr>, plot_type <chr>, and abbreviated variable names ¹species_id,
# ²hindfoot_lengthR
# using a pipe *inside* mutate()
surveys %>%
mutate(date = paste(year, month, day,
sep = "-") %>% ymd()) %>%
relocate(date, .after = year)
OUTPUT
# A tibble: 16,878 × 14
record_id month day year date plot_id species…¹ sex hindf…² weight
<dbl> <dbl> <dbl> <dbl> <date> <dbl> <chr> <chr> <dbl> <dbl>
1 1 7 16 1977 1977-07-16 2 NL M 32 NA
2 2 7 16 1977 1977-07-16 3 NL M 33 NA
3 3 7 16 1977 1977-07-16 2 DM F 37 NA
4 4 7 16 1977 1977-07-16 7 DM M 36 NA
5 5 7 16 1977 1977-07-16 3 DM M 35 NA
6 6 7 16 1977 1977-07-16 1 PF M 14 NA
7 7 7 16 1977 1977-07-16 2 PE F NA NA
8 8 7 16 1977 1977-07-16 1 DM M 37 NA
9 9 7 16 1977 1977-07-16 1 DM F 34 NA
10 10 7 16 1977 1977-07-16 6 PF F 20 NA
# … with 16,868 more rows, 4 more variables: genus <chr>, species <chr>,
# taxa <chr>, plot_type <chr>, and abbreviated variable names ¹species_id,
# ²hindfoot_lengthR
surveys %>%
mutate(date = ymd(paste(year, month, day, sep = "-"))) %>%
ggplot(aes(x = date, y = weight)) +
geom_jitter(alpha = 0.1)
WARNING
Warning: Removed 1692 rows containing missing values (geom_point).
This isn’t necessarily the most useful plot, but we will learn some techniques that will help produce nice time series plots
The split-apply-combine approach
Many data analysis tasks can be achieved using the
split-apply-combine approach: you split the data into groups, apply some
analysis to each group, and combine the results in some way.
dplyr has a few convenient functions to enable this
approach, the main two being group_by() and
summarize().
group_by() takes a data.frame and the name of one or
more columns with categorical values that define the groups.
summarize() then collapses each group into a one-row
summary of the group, giving you back a data.frame with one row per
group. The syntax for summarize() is similar to
mutate(), where you define new columns based on values of
other columns. Let’s try calculating the mean weight of all our animals
by sex.
R
surveys %>%
group_by(sex) %>%
summarize(mean_weight = mean(weight, na.rm = T))
OUTPUT
# A tibble: 3 × 2
sex mean_weight
<chr> <dbl>
1 F 53.1
2 M 53.2
3 <NA> 74.0You can see that the mean weight for males is slightly higher than
for females, but that animals whose sex is unknown have much higher
weights. This is probably due to small sample size, but we should check
to be sure. Like mutate(), we can define multiple columns
in one summarize() call. The function n() will
count the number of rows in each group.
R
surveys %>%
group_by(sex) %>%
summarize(mean_weight = mean(weight, na.rm = T),
n = n())
OUTPUT
# A tibble: 3 × 3
sex mean_weight n
<chr> <dbl> <int>
1 F 53.1 7318
2 M 53.2 8260
3 <NA> 74.0 1300You will often want to create groups based on multiple columns. For
example, we might be interested in the mean weight of every species +
sex combination. All we have to do is add another column to our
group_by() call.
R
surveys %>%
group_by(species_id, sex) %>%
summarize(mean_weight = mean(weight, na.rm = T),
n = n())
OUTPUT
`summarise()` has grouped output by 'species_id'. You can override using the
`.groups` argument.OUTPUT
# A tibble: 67 × 4
# Groups: species_id [36]
species_id sex mean_weight n
<chr> <chr> <dbl> <int>
1 AB <NA> NaN 223
2 AH <NA> NaN 136
3 BA M 7 3
4 CB <NA> NaN 23
5 CM <NA> NaN 13
6 CQ <NA> NaN 16
7 CS <NA> NaN 1
8 CV <NA> NaN 1
9 DM F 40.7 2522
10 DM M 44.0 3108
# … with 57 more rowsOur resulting data.frame is much larger, since we have a greater
number of groups. We also see a strange value showing up in our
mean_weight column: NaN. This stands for “Not
a Number”, and it often results from trying to do an operation a vector
with zero entries. How can a vector have zero entries? Well, if a
particular group (like the AB species ID + NA sex group)
has only NA values for weight, then the
na.rm = T argument in mean() will remove
all the values prior to calculating the mean. The
result will be a value of NaN. Since we are not
particularly interested in these values, let’s add a step to our
pipeline to remove rows where weight is NA
before doing any other steps. This means that any
groups with only NA values will disappear from our
data.frame before we formally create the groups with
group_by().
R
surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, sex) %>%
summarize(mean_weight = mean(weight),
n = n())
OUTPUT
`summarise()` has grouped output by 'species_id'. You can override using the
`.groups` argument.OUTPUT
# A tibble: 46 × 4
# Groups: species_id [18]
species_id sex mean_weight n
<chr> <chr> <dbl> <int>
1 BA M 7 3
2 DM F 40.7 2460
3 DM M 44.0 3013
4 DM <NA> 37 8
5 DO F 48.4 679
6 DO M 49.3 748
7 DO <NA> 44 1
8 DS F 118. 1055
9 DS M 123. 1184
10 DS <NA> 121. 16
# … with 36 more rowsThat looks better! It’s often useful to take a look at the results in
some order, like the lowest mean weight to highest. We can use the
arrange() function for that:
R
surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, sex) %>%
summarize(mean_weight = mean(weight),
n = n()) %>%
arrange(mean_weight)
OUTPUT
`summarise()` has grouped output by 'species_id'. You can override using the
`.groups` argument.OUTPUT
# A tibble: 46 × 4
# Groups: species_id [18]
species_id sex mean_weight n
<chr> <chr> <dbl> <int>
1 PF <NA> 6 2
2 BA M 7 3
3 PF F 7.09 215
4 PF M 7.10 296
5 RM M 9.92 678
6 RM <NA> 10.4 7
7 RM F 10.7 629
8 RF M 12.4 16
9 RF F 13.7 46
10 PP <NA> 15 2
# … with 36 more rowsIf we want to reverse the order, we can wrap the column name in
desc():
R
surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, sex) %>%
summarize(mean_weight = mean(weight),
n = n()) %>%
arrange(desc(mean_weight))
OUTPUT
`summarise()` has grouped output by 'species_id'. You can override using the
`.groups` argument.OUTPUT
# A tibble: 46 × 4
# Groups: species_id [18]
species_id sex mean_weight n
<chr> <chr> <dbl> <int>
1 NL M 168. 355
2 NL <NA> 164. 9
3 NL F 151. 460
4 SS M 130 1
5 DS M 123. 1184
6 DS <NA> 121. 16
7 DS F 118. 1055
8 SH F 79.2 61
9 SH M 67.6 34
10 SF F 58.3 3
# … with 36 more rowsYou may have seen several messages saying
summarise() has grouped output by 'species_id'. You can override using the .groups argument.
These are warning you that your resulting data.frame has retained some
group structure, which means any subsequent operations on that
data.frame will happen at the group level. If you look at the resulting
data.frame printed out in your console, you will see these lines:
# A tibble: 46 × 4
# Groups: species_id [18]They tell us we have a data.frame with 46 rows, 4 columns, and a
group variable species_id, for which there are 18 groups.
We will see something similar if we use group_by()
alone:
R
surveys %>%
group_by(species_id, sex)
OUTPUT
# A tibble: 16,878 × 13
# Groups: species_id, sex [67]
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 1 7 16 1977 2 NL M 32 NA Neot… albigu…
2 2 7 16 1977 3 NL M 33 NA Neot… albigu…
3 3 7 16 1977 2 DM F 37 NA Dipo… merria…
4 4 7 16 1977 7 DM M 36 NA Dipo… merria…
5 5 7 16 1977 3 DM M 35 NA Dipo… merria…
6 6 7 16 1977 1 PF M 14 NA Pero… flavus
7 7 7 16 1977 2 PE F NA NA Pero… eremic…
8 8 7 16 1977 1 DM M 37 NA Dipo… merria…
9 9 7 16 1977 1 DM F 34 NA Dipo… merria…
10 10 7 16 1977 6 PF F 20 NA Pero… flavus
# … with 16,868 more rows, 2 more variables: taxa <chr>, plot_type <chr>, and
# abbreviated variable names ¹record_id, ²species_id, ³hindfoot_lengthWhat we get back is the entire surveys data.frame, but
with the grouping variables added: 67 groups of species_id
+ sex combinations. Groups are often maintained throughout
a pipeline, and if you assign the resulting data.frame to a new object,
it will also have those groups. This can lead to confusing results if
you forget about the grouping and want to carry out operations on the
whole data.frame, not by group. Therefore, it is a good habit to remove
the groups at the end of a pipeline containing
group_by():
R
surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, sex) %>%
summarize(mean_weight = mean(weight),
n = n()) %>%
arrange(desc(mean_weight)) %>%
ungroup()
OUTPUT
`summarise()` has grouped output by 'species_id'. You can override using the
`.groups` argument.OUTPUT
# A tibble: 46 × 4
species_id sex mean_weight n
<chr> <chr> <dbl> <int>
1 NL M 168. 355
2 NL <NA> 164. 9
3 NL F 151. 460
4 SS M 130 1
5 DS M 123. 1184
6 DS <NA> 121. 16
7 DS F 118. 1055
8 SH F 79.2 61
9 SH M 67.6 34
10 SF F 58.3 3
# … with 36 more rowsNow our data.frame just says # A tibble: 46 × 4 at the
top, with no groups.
While it is common that you will want to get the one-row-per-group
summary that summarise() provides, there are times where
you want to calculate a per-group value but keep all the rows in your
data.frame. For example, we might want to know the mean weight for each
species ID + sex combination, and then we might want to know how far
from that mean value each observation in the group is. For this, we can
use group_by() and mutate() together:
R
surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, sex) %>%
mutate(mean_weight = mean(weight),
weight_diff = weight - mean_weight)
OUTPUT
# A tibble: 15,186 × 15
# Groups: species_id, sex [46]
record…¹ month day year plot_id speci…² sex hindf…³ weight genus species
<dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <dbl> <chr> <chr>
1 63 8 19 1977 3 DM M 35 40 Dipo… merria…
2 64 8 19 1977 7 DM M 37 48 Dipo… merria…
3 65 8 19 1977 4 DM F 34 29 Dipo… merria…
4 66 8 19 1977 4 DM F 35 46 Dipo… merria…
5 67 8 19 1977 7 DM M 35 36 Dipo… merria…
6 68 8 19 1977 8 DO F 32 52 Dipo… ordii
7 69 8 19 1977 2 PF M 15 8 Pero… flavus
8 70 8 19 1977 3 OX F 21 22 Onyc… sp.
9 71 8 19 1977 7 DM F 36 35 Dipo… merria…
10 74 8 19 1977 8 PF M 12 7 Pero… flavus
# … with 15,176 more rows, 4 more variables: taxa <chr>, plot_type <chr>,
# mean_weight <dbl>, weight_diff <dbl>, and abbreviated variable names
# ¹record_id, ²species_id, ³hindfoot_lengthSince we get all our columns back, the new columns are at the very
end and don’t print out in the console. Let’s use select()
to just look at the columns of interest. Inside select() we
can use the contains() function to get any column
containing the word “weight” in the name:
R
surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, sex) %>%
mutate(mean_weight = mean(weight),
weight_diff = weight - mean_weight) %>%
select(species_id, sex, contains("weight"))
OUTPUT
# A tibble: 15,186 × 5
# Groups: species_id, sex [46]
species_id sex weight mean_weight weight_diff
<chr> <chr> <dbl> <dbl> <dbl>
1 DM M 40 44.0 -4.00
2 DM M 48 44.0 4.00
3 DM F 29 40.7 -11.7
4 DM F 46 40.7 5.28
5 DM M 36 44.0 -8.00
6 DO F 52 48.4 3.63
7 PF M 8 7.10 0.902
8 OX F 22 21 1
9 DM F 35 40.7 -5.72
10 PF M 7 7.10 -0.0980
# … with 15,176 more rowsWhat happens with the group_by() + mutate()
combination is similar to using summarize(): for each
group, the mean weight is calculated. However, instead of reporting only
one row per group, the mean weight for each group is added to each row
in that group. For each row in a group (like DM species ID + M sex), you
will see the same value in mean_weight.
R
surveys_daily_counts <- surveys %>%
mutate(date = ymd(paste(year, month, day, sep = "-"))) %>%
group_by(date, sex) %>%
summarize(n = n())
OUTPUT
`summarise()` has grouped output by 'date'. You can override using the
`.groups` argument.R
# shorter approach using count()
surveys_daily_counts <- surveys %>%
mutate(date = ymd(paste(year, month, day, sep = "-"))) %>%
count(date, sex)
Challenge 4: Making a time series (continued)
- Now use the data.frame you just made to plot the daily number of
animals of each sex caught over time. It’s up to you what
geomto use, but alineplot might be a good choice. You should also think about how to differentiate which data corresponds to which sex.
R
surveys_daily_counts %>%
ggplot(aes(x = date, y = n, color = sex)) +
geom_line()
Reshaping data with tidyr
Let’s say we are interested in comparing the mean weights of each
species across our different plots. We can begin this process using the
group_by() + summarize() approach:
R
sp_by_plot <- surveys %>%
filter(!is.na(weight)) %>%
group_by(species_id, plot_id) %>%
summarise(mean_weight = mean(weight)) %>%
arrange(species_id, plot_id)
OUTPUT
`summarise()` has grouped output by 'species_id'. You can override using the
`.groups` argument.R
sp_by_plot
OUTPUT
# A tibble: 300 × 3
# Groups: species_id [18]
species_id plot_id mean_weight
<chr> <dbl> <dbl>
1 BA 3 8
2 BA 21 6.5
3 DM 1 42.7
4 DM 2 42.6
5 DM 3 41.2
6 DM 4 41.9
7 DM 5 42.6
8 DM 6 42.1
9 DM 7 43.2
10 DM 8 43.4
# … with 290 more rowsThat looks great, but it is a bit difficult to compare values across
plots. It would be nice if we could reshape this data.frame to make
those comparisons easier. Well, the tidyr package from the
tidyverse has a pair of functions that allow you to reshape
data by pivoting it: pivot_wider() and
pivot_longer(). pivot_wider() will make the
data wider, which means increasing the number of columns and reducing
the number of rows. pivot_longer() will do the opposite,
reducing the number of columns and increasing the number of rows.
In this case, it might be nice to create a data.frame where each
species has its own row, and each plot has its own column containing the
mean weight for a given species. We will use pivot_wider()
to reshape our data in this way. It takes 3 arguments:
- the name of the data.frame
-
names_from: which column should be used to generate the names of the new columns? -
values_from: which column should be used to fill in the values of the new columns?
Any columns not used for names_from or
values_from will not be pivoted.
In our case, we want the new columns to be named from our
plot_id column, with the values coming from the
mean_weight column. We can pipe our data.frame right into
pivot_wider() and add those two arguments:
R
sp_by_plot_wide <- sp_by_plot %>%
pivot_wider(names_from = plot_id,
values_from = mean_weight)
sp_by_plot_wide
OUTPUT
# A tibble: 18 × 25
# Groups: species_id [18]
species…¹ `3` `21` `1` `2` `4` `5` `6` `7` `8` `9`
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 BA 8 6.5 NA NA NA NA NA NA NA NA
2 DM 41.2 41.5 42.7 42.6 41.9 42.6 42.1 43.2 43.4 43.1
3 DO 42.7 NA 50.1 50.3 46.8 50.4 49.0 52 49.2 49.0
4 DS 128. NA 129. 125. 118. 111. 114. 126. 128. 115.
5 NL 171. 136. 154. 171. 164. 192. 176. 170. 134. 165
6 OL 32.1 28.6 35.5 34 33.0 32.6 31.8 NA 30.3 32.0
7 OT 24.1 24.1 23.7 24.9 26.5 23.6 23.5 22 24.1 23.1
8 OX 22 NA NA 22 NA 20 NA NA NA NA
9 PE 22.7 19.6 21.6 22.0 NA 21 21.6 22.8 19.4 22.1
10 PF 7.12 7.23 6.57 6.89 6.75 7.5 7.54 7 6.78 7.29
11 PH 28 31 NA NA NA 29 NA NA NA NA
12 PM 20.1 23.6 23.7 23.9 NA 23.7 22.3 23.4 23 21
13 PP 17.1 13.6 14.3 16.4 14.8 19.8 16.8 NA 13.9 14.8
14 RF 14.8 17 NA 16 NA 14 12.1 13 NA NA
15 RM 10.3 9.89 10.9 10.6 10.4 10.8 10.6 10.7 9 9.6
16 SF NA 49 NA NA NA NA NA NA NA NA
17 SH 76.0 79.9 NA 88 NA 82.7 NA NA NA NA
18 SS NA NA NA NA NA NA NA NA NA NA
# … with 14 more variables: `10` <dbl>, `11` <dbl>, `12` <dbl>, `13` <dbl>,
# `14` <dbl>, `15` <dbl>, `16` <dbl>, `17` <dbl>, `18` <dbl>, `19` <dbl>,
# `20` <dbl>, `22` <dbl>, `23` <dbl>, `24` <dbl>, and abbreviated variable
# name ¹species_idNow we’ve got our reshaped data.frame. There are a few things to
notice. First, we have a new column for each plot_id value.
There is one old column left in the data.frame: species_id.
It wasn’t used in pivot_wider(), so it stays, and now
contains a single entry for each unique species_id
value.
Finally, a lot of NAs have appeared. Some species aren’t
found in every plot, but because a data.frame has to have a value in
every row and every column, an NA is inserted. We can
double-check this to verify what is going on.
Looking in our new pivoted data.frame, we can see that there is an
NA value for the species BA in plot
1. Let’s take our sp_by_plot data.frame and
look for the mean_weight of that species + plot
combination.
R
sp_by_plot %>%
filter(species_id == "BA" & plot_id == 1)
OUTPUT
# A tibble: 0 × 3
# Groups: species_id [0]
# … with 3 variables: species_id <chr>, plot_id <dbl>, mean_weight <dbl>We get back 0 rows. There is no mean_weight for the
species BA in plot 1. This either happened
because no BA were ever caught in plot 1, or
because every BA caught in plot 1 had an
NA weight value and all the rows got removed when we used
filter(!is.na(weight)) in the process of making
sp_by_plot. Because there are no rows with that species +
plot combination, in our pivoted data.frame, the value gets filled with
NA.
There is another pivot_ function that does the opposite,
moving data from a wide to long format, called
pivot_longer(). It takes 3 arguments: cols for
the columns you want to pivot, names_to for the name of the
new column which will contain the old column names, and
values_to for the name of the new column which will contain
the old values.
We can pivot our new wide data.frame to a long format using
pivot_longer(). We want to pivot all the columns except
species_id, and we will use PLOT for the new
column of plot IDs, and MEAN_WT for the new column of mean
weight values.
R
sp_by_plot_wide %>%
pivot_longer(cols = -species_id, names_to = "PLOT", values_to = "MEAN_WT")
OUTPUT
# A tibble: 432 × 3
# Groups: species_id [18]
species_id PLOT MEAN_WT
<chr> <chr> <dbl>
1 BA 3 8
2 BA 21 6.5
3 BA 1 NA
4 BA 2 NA
5 BA 4 NA
6 BA 5 NA
7 BA 6 NA
8 BA 7 NA
9 BA 8 NA
10 BA 9 NA
# … with 422 more rowsOne thing you will notice is that all those NA values
that got generated when we pivoted wider. However, we can filter those
out, which gets us back to the same data as sp_by_plot,
before we pivoted it wider.
R
sp_by_plot_wide %>%
pivot_longer(cols = -species_id, names_to = "PLOT", values_to = "MEAN_WT") %>%
filter(!is.na(MEAN_WT))
OUTPUT
# A tibble: 300 × 3
# Groups: species_id [18]
species_id PLOT MEAN_WT
<chr> <chr> <dbl>
1 BA 3 8
2 BA 21 6.5
3 DM 3 41.2
4 DM 21 41.5
5 DM 1 42.7
6 DM 2 42.6
7 DM 4 41.9
8 DM 5 42.6
9 DM 6 42.1
10 DM 7 43.2
# … with 290 more rowsData are often recorded in spreadsheets in a wider format, but lots
of tidyverse tools, especially ggplot2, like
data in a longer format, so pivot_longer() is often very
useful.
Exporting data
Let’s say we want to send the wide version of our
sb_by_plot data.frame to a colleague who doesn’t use R. In
this case, we might want to save it as a CSV file.
First, we might want to modify the names of the columns, since right
now they are bare numbers, which aren’t very informative. Luckily,
pivot_wider() has an argument names_prefix
which will allow us to add “plot_” to the start of each column.
R
sp_by_plot %>%
pivot_wider(names_from = plot_id, values_from = mean_weight,
names_prefix = "plot_")
OUTPUT
# A tibble: 18 × 25
# Groups: species_id [18]
species_id plot_3 plot_21 plot_1 plot_2 plot_4 plot_5 plot_6 plot_7 plot_8
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 BA 8 6.5 NA NA NA NA NA NA NA
2 DM 41.2 41.5 42.7 42.6 41.9 42.6 42.1 43.2 43.4
3 DO 42.7 NA 50.1 50.3 46.8 50.4 49.0 52 49.2
4 DS 128. NA 129. 125. 118. 111. 114. 126. 128.
5 NL 171. 136. 154. 171. 164. 192. 176. 170. 134.
6 OL 32.1 28.6 35.5 34 33.0 32.6 31.8 NA 30.3
7 OT 24.1 24.1 23.7 24.9 26.5 23.6 23.5 22 24.1
8 OX 22 NA NA 22 NA 20 NA NA NA
9 PE 22.7 19.6 21.6 22.0 NA 21 21.6 22.8 19.4
10 PF 7.12 7.23 6.57 6.89 6.75 7.5 7.54 7 6.78
11 PH 28 31 NA NA NA 29 NA NA NA
12 PM 20.1 23.6 23.7 23.9 NA 23.7 22.3 23.4 23
13 PP 17.1 13.6 14.3 16.4 14.8 19.8 16.8 NA 13.9
14 RF 14.8 17 NA 16 NA 14 12.1 13 NA
15 RM 10.3 9.89 10.9 10.6 10.4 10.8 10.6 10.7 9
16 SF NA 49 NA NA NA NA NA NA NA
17 SH 76.0 79.9 NA 88 NA 82.7 NA NA NA
18 SS NA NA NA NA NA NA NA NA NA
# … with 15 more variables: plot_9 <dbl>, plot_10 <dbl>, plot_11 <dbl>,
# plot_12 <dbl>, plot_13 <dbl>, plot_14 <dbl>, plot_15 <dbl>, plot_16 <dbl>,
# plot_17 <dbl>, plot_18 <dbl>, plot_19 <dbl>, plot_20 <dbl>, plot_22 <dbl>,
# plot_23 <dbl>, plot_24 <dbl>That looks better! Let’s save this data.frame as a new object.
R
surveys_sp <- sp_by_plot %>%
pivot_wider(names_from = plot_id, values_from = mean_weight,
names_prefix = "plot_")
surveys_sp
OUTPUT
# A tibble: 18 × 25
# Groups: species_id [18]
species_id plot_3 plot_21 plot_1 plot_2 plot_4 plot_5 plot_6 plot_7 plot_8
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 BA 8 6.5 NA NA NA NA NA NA NA
2 DM 41.2 41.5 42.7 42.6 41.9 42.6 42.1 43.2 43.4
3 DO 42.7 NA 50.1 50.3 46.8 50.4 49.0 52 49.2
4 DS 128. NA 129. 125. 118. 111. 114. 126. 128.
5 NL 171. 136. 154. 171. 164. 192. 176. 170. 134.
6 OL 32.1 28.6 35.5 34 33.0 32.6 31.8 NA 30.3
7 OT 24.1 24.1 23.7 24.9 26.5 23.6 23.5 22 24.1
8 OX 22 NA NA 22 NA 20 NA NA NA
9 PE 22.7 19.6 21.6 22.0 NA 21 21.6 22.8 19.4
10 PF 7.12 7.23 6.57 6.89 6.75 7.5 7.54 7 6.78
11 PH 28 31 NA NA NA 29 NA NA NA
12 PM 20.1 23.6 23.7 23.9 NA 23.7 22.3 23.4 23
13 PP 17.1 13.6 14.3 16.4 14.8 19.8 16.8 NA 13.9
14 RF 14.8 17 NA 16 NA 14 12.1 13 NA
15 RM 10.3 9.89 10.9 10.6 10.4 10.8 10.6 10.7 9
16 SF NA 49 NA NA NA NA NA NA NA
17 SH 76.0 79.9 NA 88 NA 82.7 NA NA NA
18 SS NA NA NA NA NA NA NA NA NA
# … with 15 more variables: plot_9 <dbl>, plot_10 <dbl>, plot_11 <dbl>,
# plot_12 <dbl>, plot_13 <dbl>, plot_14 <dbl>, plot_15 <dbl>, plot_16 <dbl>,
# plot_17 <dbl>, plot_18 <dbl>, plot_19 <dbl>, plot_20 <dbl>, plot_22 <dbl>,
# plot_23 <dbl>, plot_24 <dbl>Now we can save this data.frame to a CSV using the
write_csv() function from the readr package.
The first argument is the name of the data.frame, and the second is the
path to the new file we want to create, including the file extension
.csv.
R
write_csv(surveys_sp, "data/cleaned/surveys_meanweight_species_plot.csv")
If we go look into our data/cleaned_data folder, we will
see this new CSV file.
Keypoints
- use
filter()to subset rows andselect()to subset columns - build up pipelines one step at a time before assigning the result
- it is often best to keep components of dates separate until needed,
then use
mutate()to make a date column -
group_by()can be used withsummarize()to collapse rows ormutate()to keep the same number of rows -
pivot_wider()andpivot_longer()are powerful for reshaping data, but you should plan out how to use them thoughtfully