Introductory Data Wrangling
Matthew E. Aiello-Lammens
Note - much of the material in this session is based on or remixed from materials in the Data Carpentry - Data Analysis and Visualization in R for Ecologists lessons, maintained by François Michonneau & Auriel Fournier. Copyright (c) Data Carpentry.
Section Goals
- Be able to load a data set into the R environment from a .csv file
- Practice with basic data summary and
data.frameindexing functions - Practice working with factors and dates
- Become familiar with the
tidyverse“curated” list of packages - Practice sub-setting a data set using functions
selectandfilter - Practice calculating summaries of a data set using functions
group_byandsummarize
Working with data files
Portal Project data set
We will be working with a data set from the Portal Project, a long-term ecological research project near Portal, Arizona (southeast AZ) that was started in 1977. Data collected as part of this project includes observations of plants, rodents, and ants in a set of experimentally manipulated study plots.
Downloading the data
We are going to use the R function download.file() to
download the CSV file that contains the survey data from figshare, a website for storing and
archiving research products. Here’s a direct link to the figshare page
for these data - https://figshare.com/articles/dataset/Portal_Project_Teaching_Database/1314459/10
- but we won’t be downloading these data manually.
Inside the download.file command, the first entry is a
character string with the source URL (“https://ndownloader.figshare.com/files/2292169”). This
source URL downloads a CSV file from figshare. The text after the comma
(“data/portal_data_joined.csv”) is the destination of the file on your
local machine. You’ll need to have a folder on your machine called
“data” where you’ll download the file to. You should have created this
folder in the first week of class. So this command downloads a file from
figshare, names it “portal_data_joined.csv” and adds it to a preexisting
folder named “data”.
download.file(url = "https://ndownloader.figshare.com/files/2292169",
destfile = "data/portal_data_joined.csv")Reading the data into R
The file has now been downloaded to the destination you specified,
but R has not yet loaded the data from the file into memory. To do this,
we are going to use the read_csv() function from the
tidyverse package. Note that there other
ways to read data in, such as the base R read.csv(), but
we’ll use the upgraded read_csv() function here.
First, we need to install the tidyverse
package. Type install.packages("tidyverse") straight into
the console. It’s better to write this in the console than in our script
for any package, as there’s no need to re-install packages every time we
run the script. Then, to load the package type:
## load the tidyverse packages, incl. dplyr
library(tidyverse)Now we can use the functions from the
tidyverse package. Let’s use
read_csv() to read the data into a data frame:
surveys <- read_csv("data/portal_data_joined.csv")## Rows: 34786 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.You will see the message
Parsed with column specification, followed by each column
name and its data type. When you execute read_csv on a data
file, it looks through the first 1000 rows of each column and guesses
its data type. For example, in this data set, read_csv()
reads weight as col_double (a numeric data
type), and species as col_character. You have
the option to specify the data type for a column manually by using the
col_types argument in read_csv.
Examining the surveys data frame
Let’s use some of the functions we’ve learned in the past, and a few
new ones, to look at the surveys data frame.
First, let’s open the data frame in a viewer.
View(surveys)Next, let’s use the summary function.
summary(surveys)## record_id month day year plot_id
## Min. : 1 Min. : 1.000 Min. : 1.0 Min. :1977 Min. : 1.00
## 1st Qu.: 8964 1st Qu.: 4.000 1st Qu.: 9.0 1st Qu.:1984 1st Qu.: 5.00
## Median :17762 Median : 6.000 Median :16.0 Median :1990 Median :11.00
## Mean :17804 Mean : 6.474 Mean :16.1 Mean :1990 Mean :11.34
## 3rd Qu.:26655 3rd Qu.:10.000 3rd Qu.:23.0 3rd Qu.:1997 3rd Qu.:17.00
## Max. :35548 Max. :12.000 Max. :31.0 Max. :2002 Max. :24.00
##
## species_id sex hindfoot_length weight
## Length:34786 Length:34786 Min. : 2.00 Min. : 4.00
## Class :character Class :character 1st Qu.:21.00 1st Qu.: 20.00
## Mode :character Mode :character Median :32.00 Median : 37.00
## Mean :29.29 Mean : 42.67
## 3rd Qu.:36.00 3rd Qu.: 48.00
## Max. :70.00 Max. :280.00
## NA's :3348 NA's :2503
## genus species taxa plot_type
## Length:34786 Length:34786 Length:34786 Length:34786
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
##
##
##
## How about a few other functions?
Challenge - looking at data frames
Try using each of the functions below and identifying what they do.
dim(surveys)
nrow(surveys)
ncol(surveys)
names(surveys)
rownames(surveys)
str(surveys)Metadata
Metadata is information about the data. Standards for metadata can be complicated, but at its most basic level, metadata is simply information about each column of a data set. Here’s an example of metadata for the Portal data set.
| Column | Description |
|---|---|
| record_id | Unique id for the observation |
| month | month of observation |
| day | day of observation |
| year | year of observation |
| plot_id | ID of a particular plot |
| species_id | 2-letter code |
| sex | sex of animal (“M”, “F”) |
| hindfoot_length | length of the hindfoot in mm |
| weight | weight of the animal in grams |
| genus | genus of animal |
| species | species of animal |
| taxon | e.g. Rodent, Reptile, Bird, Rabbit |
| plot_type | type of plot |
Factors
When we ran str(surveys) above we saw that several of
the columns consist of integers. The columns genus,
species, sex, plot_type, …
however, are of the class character. Arguably, these
columns contain categorical data, that is, they can
only take on a limited number of values. R has a special class for
working with categorical data, called factor.
Once created, factors can only contain a pre-defined set of values, known as levels. Factors are stored as integers associated with labels and they can be ordered or unordered. While factors look (and often behave) like character vectors, they are actually treated as integer vectors by R. So you need to be very careful when treating them as strings.
When importing a data frame with read_csv(), the columns
that contain text are not automatically coerced (=converted) into the
factor data type. Note - this is
different than the behavior of the base R read.csv()
function. Once we have loaded the data we can convert columns to factors
using the factor() function:
surveys$sex <- factor(surveys$sex)We can see that the conversion has worked by using the
summary() function again. This produces a table with the
counts for each factor level:
summary(surveys$sex)## F M NA's
## 15690 17348 1748IMPORTANT - By default, R always sorts levels in alphabetical order. For instance, if you have a factor with 2 levels:
sex <- factor(c("male", "female", "female", "male"))R will assign 1 to the level "female" and
2 to the level "male" (because f
comes before m, even though the first element in this
vector is "male"). You can see this by using the function
levels() and you can find the number of levels using
nlevels():
levels(sex)## [1] "female" "male"nlevels(sex)## [1] 2Sometimes, the order of the factors does not matter, other times you
might want to specify the order because it is meaningful (e.g., “low”,
“medium”, “high”), it improves your visualization, or it is required by
a particular type of analysis. Here, one way to reorder our levels in
the sex vector would be:
sex # current order## [1] male female female male
## Levels: female malesex <- factor(sex, levels = c("male", "female"))
sex # after re-ordering## [1] male female female male
## Levels: male femaleIn R’s memory, these factors are represented by integers (1, 2, 3),
but are more informative than integers because factors are self
describing: "female", "male" is more
descriptive than 1, 2. Which one is “male”?
You wouldn’t be able to tell just from the integer data. Factors, on the
other hand, have this information built in. You can actually see the
conversion (and see some of the ways you might run into trouble) by
coercing a factor into a number:
as.numeric(sex)## [1] 1 2 2 1Challenge - applying factors to the Portal data
Change the columns taxa and genus in the
surveys data frame into a factor. Once you have done this,
use the summary function to determine how many of each
taxa are in the data set.
Here’s a more detailed example of how converting factors can go off the rails.
year_fct <- factor(c(1990, 1983, 1977, 1998, 1990))
as.numeric(year_fct) # Wrong! And there is no warning...## [1] 3 2 1 4 3as.numeric(as.character(year_fct)) # Works...## [1] 1990 1983 1977 1998 1990as.numeric(levels(year_fct))[year_fct] # The recommended way.## [1] 1990 1983 1977 1998 1990Renaming factors
When your data is stored as a factor, you can use the
plot() function to get a quick glance at the number of
observations represented by each factor level. Let’s look at the number
of males and females captured over the course of the experiment:
## bar plot of the number of females and males captured during the experiment:
ggplot(data = surveys, aes(x = sex)) +
geom_bar()
However, as we saw when we used summary(surveys$sex),
there are about 1700 individuals for which the sex information hasn’t
been recorded. To show them in the plot, we can turn the missing values
into a factor level with the addNA() function. We will also
have to give the new factor level a label. We are going to work with a
copy of the sex column, so we’re not modifying the working
copy of the data frame:
sex <- surveys$sex
levels(sex)## [1] "F" "M"sex <- addNA(sex)
levels(sex)## [1] "F" "M" NAhead(sex)## [1] M M <NA> <NA> <NA> <NA>
## Levels: F M <NA>levels(sex)[3] <- "undetermined"
levels(sex)## [1] "F" "M" "undetermined"head(sex)## [1] M M undetermined undetermined undetermined
## [6] undetermined
## Levels: F M undeterminedNow we can plot the data again, using plot(sex).
ggplot(data = NULL, aes(x = sex)) +
geom_bar()
Working with dates
One of the most common issues that new (and experienced!) R users
have is converting date and time information into a variable that is
appropriate and usable during analyses. As a reminder from earlier in
this lesson, the best practice for dealing with date data is to ensure
that each component of your date is stored as a separate variable. Using
str(), We can confirm that our data frame has a separate
column for day, month, and year, and that each contains integer
values.
str(surveys)## spc_tbl_ [34,786 × 13] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
## $ record_id : num [1:34786] 1 72 224 266 349 363 435 506 588 661 ...
## $ month : num [1:34786] 7 8 9 10 11 11 12 1 2 3 ...
## $ day : num [1:34786] 16 19 13 16 12 12 10 8 18 11 ...
## $ year : num [1:34786] 1977 1977 1977 1977 1977 ...
## $ plot_id : num [1:34786] 2 2 2 2 2 2 2 2 2 2 ...
## $ species_id : chr [1:34786] "NL" "NL" "NL" "NL" ...
## $ sex : Factor w/ 2 levels "F","M": 2 2 NA NA NA NA NA NA 2 NA ...
## $ hindfoot_length: num [1:34786] 32 31 NA NA NA NA NA NA NA NA ...
## $ weight : num [1:34786] NA NA NA NA NA NA NA NA 218 NA ...
## $ genus : chr [1:34786] "Neotoma" "Neotoma" "Neotoma" "Neotoma" ...
## $ species : chr [1:34786] "albigula" "albigula" "albigula" "albigula" ...
## $ taxa : chr [1:34786] "Rodent" "Rodent" "Rodent" "Rodent" ...
## $ plot_type : chr [1:34786] "Control" "Control" "Control" "Control" ...
## - attr(*, "spec")=
## .. cols(
## .. record_id = col_double(),
## .. month = col_double(),
## .. day = col_double(),
## .. year = col_double(),
## .. plot_id = col_double(),
## .. species_id = col_character(),
## .. sex = col_character(),
## .. hindfoot_length = col_double(),
## .. weight = col_double(),
## .. genus = col_character(),
## .. species = col_character(),
## .. taxa = col_character(),
## .. plot_type = col_character()
## .. )
## - attr(*, "problems")=<externalptr>We are going to use the ymd() function from the package
lubridate (which belongs to the
tidyverse; learn more here).
lubridate gets installed as part as the
tidyverse installation. When you load the
tidyverse
(library(tidyverse)), the core packages (the packages used
in most data analyses) get loaded.
lubridate however does not belong to the
core tidyverse, so you have to load it explicitly with
library(lubridate)
Start by loading the required package:
library(lubridate)ymd() takes a vector representing year, month, and day,
and converts it to a Date vector. Date is a
class of data recognized by R as being a date and can be manipulated as
such. The argument that the function requires is flexible, but, as a
best practice, is a character vector formatted as “YYYY-MM-DD”.
Let’s create a date object and inspect the structure:
my_date <- ymd("2015-01-01")
str(my_date)## Date[1:1], format: "2015-01-01"Now let’s paste the year, month, and day separately - we get the same result:
# sep indicates the character to use to separate each component
my_date <- ymd(paste("2015", "1", "1", sep = "-"))
str(my_date)## Date[1:1], format: "2015-01-01"Now we apply this function to the surveys dataset. Create a character
vector from the year, month, and
day columns of surveys using
paste():
paste(surveys$year, surveys$month, surveys$day, sep = "-")This character vector can be used as the argument for
ymd():
ymd(paste(surveys$year, surveys$month, surveys$day, sep = "-"))There is a warning telling us that some dates could not be parsed
(understood) by the ymd() function. For these dates, the
function has returned NA, which means they are treated as
missing values. We will deal with this problem later, but first we add
the resulting Date vector to the surveys data
frame as a new column called date:
surveys$date <- ymd(paste(surveys$year, surveys$month, surveys$day, sep = "-"))## Warning: 129 failed to parse.str(surveys) # notice the new column, with 'date' as the class## spc_tbl_ [34,786 × 14] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
## $ record_id : num [1:34786] 1 72 224 266 349 363 435 506 588 661 ...
## $ month : num [1:34786] 7 8 9 10 11 11 12 1 2 3 ...
## $ day : num [1:34786] 16 19 13 16 12 12 10 8 18 11 ...
## $ year : num [1:34786] 1977 1977 1977 1977 1977 ...
## $ plot_id : num [1:34786] 2 2 2 2 2 2 2 2 2 2 ...
## $ species_id : chr [1:34786] "NL" "NL" "NL" "NL" ...
## $ sex : Factor w/ 2 levels "F","M": 2 2 NA NA NA NA NA NA 2 NA ...
## $ hindfoot_length: num [1:34786] 32 31 NA NA NA NA NA NA NA NA ...
## $ weight : num [1:34786] NA NA NA NA NA NA NA NA 218 NA ...
## $ genus : chr [1:34786] "Neotoma" "Neotoma" "Neotoma" "Neotoma" ...
## $ species : chr [1:34786] "albigula" "albigula" "albigula" "albigula" ...
## $ taxa : chr [1:34786] "Rodent" "Rodent" "Rodent" "Rodent" ...
## $ plot_type : chr [1:34786] "Control" "Control" "Control" "Control" ...
## $ date : Date[1:34786], format: "1977-07-16" "1977-08-19" ...
## - attr(*, "spec")=
## .. cols(
## .. record_id = col_double(),
## .. month = col_double(),
## .. day = col_double(),
## .. year = col_double(),
## .. plot_id = col_double(),
## .. species_id = col_character(),
## .. sex = col_character(),
## .. hindfoot_length = col_double(),
## .. weight = col_double(),
## .. genus = col_character(),
## .. species = col_character(),
## .. taxa = col_character(),
## .. plot_type = col_character()
## .. )
## - attr(*, "problems")=<externalptr>Let’s make sure everything worked correctly. Again, one way to
inspect the new column is to use summary():
summary(surveys$date)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## "1977-07-16" "1984-03-12" "1990-07-22" "1990-12-15" "1997-07-29" "2002-12-31"
## NA's
## "129"Let’s investigate why some dates could not be parsed.
We can use the functions we saw previously to deal with missing data
to identify the rows in our data frame that are failing. If we combine
them with what we learned about subsetting data frames earlier, we can
extract the columns “year,”month”, “day” from the records that have
NA in our new column date. We will also use
head() so we don’t clutter the output:
missing_dates <- surveys[is.na(surveys$date), c("year", "month", "day")]
head(missing_dates)## # A tibble: 6 × 3
## year month day
## <dbl> <dbl> <dbl>
## 1 2000 9 31
## 2 2000 4 31
## 3 2000 4 31
## 4 2000 4 31
## 5 2000 4 31
## 6 2000 9 31Challenge - problems with dates
Why did these dates fail to parse? If you had to use these data for your analyses, how would you deal with this situation?
Data wrangling using dplyr and
tidyr
Bracket subsetting is handy, but it can be cumbersome and difficult
to read, especially for complicated operations. Enter
dplyr. dplyr
is a package for making tabular data wrangling easier. It pairs nicely
with tidyr which enables you to swiftly
convert between different data formats for plotting and analysis.
The tidyverse package is an
“umbrella-package” that installs tidyr,
dplyr, and several other packages useful
for data analysis, such as ggplot2,
tibble, etc.
The tidyverse package tries to address
3 common issues that arise when doing data analysis with some of the
functions that come with R:
- The results from a base R function sometimes depend on the type of data.
- Using R expressions in a non standard way, which can be confusing for new learners.
- Hidden arguments, having default operations that new learners are not aware of.
What are dplyr and
tidyr?
The package dplyr provides easy tools
for the most common data manipulation tasks. It is built to work
directly with data frames, with many common tasks optimized by being
written in a compiled language (C++). An additional feature is the
ability to work directly with data stored in an external database. The
benefits of doing this are that the data can be managed natively in a
relational database, queries can be conducted on that database, and only
the results of the query are returned.
Advanced data science concept - The above addresses a common problem with R in that all operations are conducted in-memory and thus the amount of data you can work with is limited by available memory. The database connections essentially remove that limitation in that you can connect to a database of many hundreds of GB, conduct queries on it directly, and pull back into R only what you need for analysis.
The package tidyr addresses the common
problem of wanting to reshape your data for plotting and use by
different R functions. Sometimes we want data sets where we have one row
per measurement. Sometimes we want a data frame where each measurement
type has its own column, and rows are instead more aggregated groups
(e.g., a time period, an experimental unit like a plot or a batch
number). Moving back and forth between these formats is non-trivial, and
tidyr gives you tools for this and more
sophisticated data manipulation.
To learn more about dplyr and
tidyr after the workshop, you may want to
check out this handy
data transformation with dplyr
cheatsheet and this one
about tidyr.
I also heavily rely on this data
wrangling cheatsheet which combines main functions of both
dplyr and tidyr.
Next, we’re going to learn some of the most common
dplyr functions:
select(): subset columnsfilter(): subset rows on conditionsmutate(): create new columns by using information from other columnsgroup_by()andsummarize(): create summary statistics on grouped dataarrange(): sort resultscount(): count discrete values
Selecting columns and filtering rows
To select columns of a data frame, use select(). The
first argument to this function is the data frame
(surveys), and the subsequent arguments are the columns to
keep.
select(surveys, plot_id, species_id, weight)## # A tibble: 34,786 × 3
## plot_id species_id weight
## <dbl> <chr> <dbl>
## 1 2 NL NA
## 2 2 NL NA
## 3 2 NL NA
## 4 2 NL NA
## 5 2 NL NA
## 6 2 NL NA
## 7 2 NL NA
## 8 2 NL NA
## 9 2 NL 218
## 10 2 NL NA
## # ℹ 34,776 more rowsTo select all columns except certain ones, put a “-” in front of the variable to exclude it.
select(surveys, -record_id, -species_id)## # A tibble: 34,786 × 12
## month day year plot_id sex hindfoot_length weight genus species taxa
## <dbl> <dbl> <dbl> <dbl> <fct> <dbl> <dbl> <chr> <chr> <chr>
## 1 7 16 1977 2 M 32 NA Neotoma albigula Rode…
## 2 8 19 1977 2 M 31 NA Neotoma albigula Rode…
## 3 9 13 1977 2 <NA> NA NA Neotoma albigula Rode…
## 4 10 16 1977 2 <NA> NA NA Neotoma albigula Rode…
## 5 11 12 1977 2 <NA> NA NA Neotoma albigula Rode…
## 6 11 12 1977 2 <NA> NA NA Neotoma albigula Rode…
## 7 12 10 1977 2 <NA> NA NA Neotoma albigula Rode…
## 8 1 8 1978 2 <NA> NA NA Neotoma albigula Rode…
## 9 2 18 1978 2 M NA 218 Neotoma albigula Rode…
## 10 3 11 1978 2 <NA> NA NA Neotoma albigula Rode…
## # ℹ 34,776 more rows
## # ℹ 2 more variables: plot_type <chr>, date <date>This will select all the variables in surveys except
record_id and species_id.
To choose rows based on a specific criterion, use
filter():
filter(surveys, year == 1995)## # A tibble: 1,180 × 14
## record_id month day year plot_id species_id sex hindfoot_length weight
## <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <fct> <dbl> <dbl>
## 1 22314 6 7 1995 2 NL M 34 NA
## 2 22728 9 23 1995 2 NL F 32 165
## 3 22899 10 28 1995 2 NL F 32 171
## 4 23032 12 2 1995 2 NL F 33 NA
## 5 22003 1 11 1995 2 DM M 37 41
## 6 22042 2 4 1995 2 DM F 36 45
## 7 22044 2 4 1995 2 DM M 37 46
## 8 22105 3 4 1995 2 DM F 37 49
## 9 22109 3 4 1995 2 DM M 37 46
## 10 22168 4 1 1995 2 DM M 36 48
## # ℹ 1,170 more rows
## # ℹ 5 more variables: genus <chr>, species <chr>, taxa <chr>, plot_type <chr>,
## # date <date>Pipes
What if you want to select and filter at the same time? There are three ways to do this: use intermediate steps, nested functions, or pipes.
With intermediate steps, you create a temporary data frame and use that as input to the next function, like this:
surveys2 <- filter(surveys, weight < 5)
surveys_sml <- select(surveys2, species_id, sex, weight)This is readable, but can clutter up your workspace with lots of objects that you have to name individually. With multiple steps, that can be hard to keep track of.
You can also nest functions (i.e. one function inside of another), like this:
surveys_sml <- select(filter(surveys, weight < 5), species_id, sex, weight)This is handy, but can be difficult to read if too many functions are nested, as R evaluates the expression from the inside out (in this case, filtering, then selecting).
The last option, pipes, are a recent addition to R. Pipes
let you take the output of one function and send it directly to the
next, which is useful when you need to do many things to the same
dataset. Pipes in R look like %>% and are made available
via the magrittr package, installed
automatically with dplyr.
surveys %>%
filter(weight < 5) %>%
select(species_id, sex, weight)## # A tibble: 17 × 3
## species_id sex weight
## <chr> <fct> <dbl>
## 1 PF F 4
## 2 PF F 4
## 3 PF M 4
## 4 RM F 4
## 5 RM M 4
## 6 PF <NA> 4
## 7 PP M 4
## 8 RM M 4
## 9 RM M 4
## 10 RM M 4
## 11 PF M 4
## 12 PF F 4
## 13 RM M 4
## 14 RM M 4
## 15 RM F 4
## 16 RM M 4
## 17 RM M 4In the above code, we use the pipe to send the surveys
dataset first through filter() to keep rows where
weight is less than 5, then through select()
to keep only the species_id, sex, and
weight columns. Since %>% takes the object
on its left and passes it as the first argument to the function on its
right, we don’t need to explicitly include the data frame as an argument
to the filter() and select() functions any
more.
Some may find it helpful to read the pipe like the word “then”. For
instance, in the above example, we took the data frame
surveys, then we filtered for rows
with weight < 5, then we selected
columns species_id, sex, and
weight. The dplyr functions
by themselves are somewhat simple, but by combining them into linear
workflows with the pipe, we can accomplish more complex manipulations of
data frames.
If we want to create a new object with this smaller version of the data, we can assign it a new name:
surveys_sml <- surveys %>%
filter(weight < 5) %>%
select(species_id, sex, weight)
surveys_sml## # A tibble: 17 × 3
## species_id sex weight
## <chr> <fct> <dbl>
## 1 PF F 4
## 2 PF F 4
## 3 PF M 4
## 4 RM F 4
## 5 RM M 4
## 6 PF <NA> 4
## 7 PP M 4
## 8 RM M 4
## 9 RM M 4
## 10 RM M 4
## 11 PF M 4
## 12 PF F 4
## 13 RM M 4
## 14 RM M 4
## 15 RM F 4
## 16 RM M 4
## 17 RM M 4Note that the final data frame is the leftmost part of this expression.
Challenge - practicing with pipes
Using pipes, subset the surveys data to include animals
collected before 1995 and retain only the columns year,
sex, and weight.
Mutate
Frequently you’ll want to create new columns based on the values in
existing columns, for example to do unit conversions, or to find the
ratio of values in two columns. For this we’ll use
mutate().
To create a new column of weight in kg:
surveys %>%
mutate(weight_kg = weight / 1000)## # A tibble: 34,786 × 15
## record_id month day year plot_id species_id sex hindfoot_length weight
## <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <fct> <dbl> <dbl>
## 1 1 7 16 1977 2 NL M 32 NA
## 2 72 8 19 1977 2 NL M 31 NA
## 3 224 9 13 1977 2 NL <NA> NA NA
## 4 266 10 16 1977 2 NL <NA> NA NA
## 5 349 11 12 1977 2 NL <NA> NA NA
## 6 363 11 12 1977 2 NL <NA> NA NA
## 7 435 12 10 1977 2 NL <NA> NA NA
## 8 506 1 8 1978 2 NL <NA> NA NA
## 9 588 2 18 1978 2 NL M NA 218
## 10 661 3 11 1978 2 NL <NA> NA NA
## # ℹ 34,776 more rows
## # ℹ 6 more variables: genus <chr>, species <chr>, taxa <chr>, plot_type <chr>,
## # date <date>, weight_kg <dbl>You can also create a second new column based on the first new column
within the same call of mutate():
surveys %>%
mutate(weight_kg = weight / 1000,
weight_lb = weight_kg * 2.2)## # A tibble: 34,786 × 16
## record_id month day year plot_id species_id sex hindfoot_length weight
## <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <fct> <dbl> <dbl>
## 1 1 7 16 1977 2 NL M 32 NA
## 2 72 8 19 1977 2 NL M 31 NA
## 3 224 9 13 1977 2 NL <NA> NA NA
## 4 266 10 16 1977 2 NL <NA> NA NA
## 5 349 11 12 1977 2 NL <NA> NA NA
## 6 363 11 12 1977 2 NL <NA> NA NA
## 7 435 12 10 1977 2 NL <NA> NA NA
## 8 506 1 8 1978 2 NL <NA> NA NA
## 9 588 2 18 1978 2 NL M NA 218
## 10 661 3 11 1978 2 NL <NA> NA NA
## # ℹ 34,776 more rows
## # ℹ 7 more variables: genus <chr>, species <chr>, taxa <chr>, plot_type <chr>,
## # date <date>, weight_kg <dbl>, weight_lb <dbl>If this runs off your screen and you just want to see the first few
rows, you can use a pipe to view the head() of the data.
(Pipes work with non-dplyr functions, too,
as long as the dplyr or
magrittr package is loaded).
surveys %>%
mutate(weight_kg = weight / 1000) %>%
head()## # A tibble: 6 × 15
## record_id month day year plot_id species_id sex hindfoot_length weight
## <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <fct> <dbl> <dbl>
## 1 1 7 16 1977 2 NL M 32 NA
## 2 72 8 19 1977 2 NL M 31 NA
## 3 224 9 13 1977 2 NL <NA> NA NA
## 4 266 10 16 1977 2 NL <NA> NA NA
## 5 349 11 12 1977 2 NL <NA> NA NA
## 6 363 11 12 1977 2 NL <NA> NA NA
## # ℹ 6 more variables: genus <chr>, species <chr>, taxa <chr>, plot_type <chr>,
## # date <date>, weight_kg <dbl>The first few rows of the output are full of NAs, so if
we wanted to remove those we could insert a filter() in the
chain:
surveys %>%
filter(!is.na(weight)) %>%
mutate(weight_kg = weight / 1000) %>%
head()## # A tibble: 6 × 15
## record_id month day year plot_id species_id sex hindfoot_length weight
## <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <fct> <dbl> <dbl>
## 1 588 2 18 1978 2 NL M NA 218
## 2 845 5 6 1978 2 NL M 32 204
## 3 990 6 9 1978 2 NL M NA 200
## 4 1164 8 5 1978 2 NL M 34 199
## 5 1261 9 4 1978 2 NL M 32 197
## 6 1453 11 5 1978 2 NL M NA 218
## # ℹ 6 more variables: genus <chr>, species <chr>, taxa <chr>, plot_type <chr>,
## # date <date>, weight_kg <dbl>is.na() is a function that determines whether something
is an NA. The ! symbol negates the result, so
we’re asking for every row where weight is not an
NA.
Split-apply-combine data analysis and the summarize()
function
Many data analysis tasks can be approached using the
split-apply-combine paradigm: split the data into groups, apply
some analysis to each group, and then combine the results.
dplyr makes this very easy through the use
of the group_by() function.
group_by() is often used together with
summarize(), which collapses each group into a single-row
summary of that group. Important note - the functions
summarize() and summarise() are equivelant.
Just two different spellings for the same thing ¯\_(ツ)_/¯.
group_by() takes as arguments the column names that
contain the categorical variables for which you want to
calculate the summary statistics. So to compute the mean
weight by sex:
surveys %>%
group_by(sex) %>%
summarize(mean_weight = mean(weight, na.rm = TRUE))## # A tibble: 3 × 2
## sex mean_weight
## <fct> <dbl>
## 1 F 42.2
## 2 M 43.0
## 3 <NA> 64.7You can also group by multiple columns:
surveys %>%
group_by(sex, species_id) %>%
summarize(mean_weight = mean(weight, na.rm = TRUE))## `summarise()` has grouped output by 'sex'. You can override using the `.groups`
## argument.## # A tibble: 92 × 3
## # Groups: sex [3]
## sex species_id mean_weight
## <fct> <chr> <dbl>
## 1 F BA 9.16
## 2 F DM 41.6
## 3 F DO 48.5
## 4 F DS 118.
## 5 F NL 154.
## 6 F OL 31.1
## 7 F OT 24.8
## 8 F OX 21
## 9 F PB 30.2
## 10 F PE 22.8
## # ℹ 82 more rowsWe can see that the sex column contains NA
values because some animals had escaped before their sex and body
weights could be determined. The resulting mean_weight
column does not contain NA but NaN (which
refers to “Not a Number”) because mean() was called on a
vector of NA values while at the same time setting
na.rm = TRUE. To avoid this, we can remove the missing
values for weight before we attempt to calculate the summary statistics
on weight. Because the missing values are removed first, we can omit
na.rm = TRUE when computing the mean:
surveys %>%
filter(!is.na(weight)) %>%
group_by(sex, species_id) %>%
summarize(mean_weight = mean(weight))## `summarise()` has grouped output by 'sex'. You can override using the `.groups`
## argument.## # A tibble: 64 × 3
## # Groups: sex [3]
## sex species_id mean_weight
## <fct> <chr> <dbl>
## 1 F BA 9.16
## 2 F DM 41.6
## 3 F DO 48.5
## 4 F DS 118.
## 5 F NL 154.
## 6 F OL 31.1
## 7 F OT 24.8
## 8 F OX 21
## 9 F PB 30.2
## 10 F PE 22.8
## # ℹ 54 more rowsHere are a few more examples of using group_by and
summarise
It is sometimes useful to rearrange the result of a query to inspect
the values. For instance, we can sort on min_weight to put
the lighter species first:
surveys %>%
filter(!is.na(weight)) %>%
group_by(sex, species_id) %>%
summarize(mean_weight = mean(weight),
min_weight = min(weight)) %>%
arrange(min_weight)## `summarise()` has grouped output by 'sex'. You can override using the `.groups`
## argument.## # A tibble: 64 × 4
## # Groups: sex [3]
## sex species_id mean_weight min_weight
## <fct> <chr> <dbl> <dbl>
## 1 F PF 7.97 4
## 2 F RM 11.1 4
## 3 M PF 7.89 4
## 4 M PP 17.2 4
## 5 M RM 10.1 4
## 6 <NA> PF 6 4
## 7 F OT 24.8 5
## 8 F PP 17.2 5
## 9 F BA 9.16 6
## 10 M BA 7.36 6
## # ℹ 54 more rowsTo sort in descending order, we need to add the desc()
function. If we want to sort the results by decreasing order of mean
weight:
surveys %>%
filter(!is.na(weight)) %>%
group_by(sex, species_id) %>%
summarize(mean_weight = mean(weight),
min_weight = min(weight)) %>%
arrange(desc(mean_weight))## `summarise()` has grouped output by 'sex'. You can override using the `.groups`
## argument.## # A tibble: 64 × 4
## # Groups: sex [3]
## sex species_id mean_weight min_weight
## <fct> <chr> <dbl> <dbl>
## 1 <NA> NL 168. 83
## 2 M NL 166. 30
## 3 F NL 154. 32
## 4 M SS 130 130
## 5 <NA> SH 130 130
## 6 M DS 122. 12
## 7 <NA> DS 120 78
## 8 F DS 118. 45
## 9 F SH 78.8 30
## 10 F SF 69 46
## # ℹ 54 more rowsCounting
When working with data, we often want to know the number of
observations found for each factor or combination of factors. For this
task, dplyr provides count().
For example, if we wanted to count the number of rows of data for each
sex, we would do:
surveys %>%
count(sex) ## # A tibble: 3 × 2
## sex n
## <fct> <int>
## 1 F 15690
## 2 M 17348
## 3 <NA> 1748The count() function is shorthand for something we’ve
already seen: grouping by a variable, and summarizing it by counting the
number of observations in that group. In other words,
surveys %>% count() is equivalent to:
surveys %>%
group_by(sex) %>%
summarise(count = n())## # A tibble: 3 × 2
## sex count
## <fct> <int>
## 1 F 15690
## 2 M 17348
## 3 <NA> 1748For convenience, count() provides the sort
argument:
surveys %>%
count(sex, sort = TRUE) ## # A tibble: 3 × 2
## sex n
## <fct> <int>
## 1 M 17348
## 2 F 15690
## 3 <NA> 1748Previous example shows the use of count() to count the
number of rows/observations for one factor (i.e.,
sex). If we wanted to count combination of
factors, such as sex and species, we
would specify the first and the second factor as the arguments of
count():
surveys %>%
count(sex, species) ## # A tibble: 81 × 3
## sex species n
## <fct> <chr> <int>
## 1 F albigula 675
## 2 F baileyi 1646
## 3 F eremicus 579
## 4 F flavus 757
## 5 F fulvescens 57
## 6 F fulviventer 17
## 7 F hispidus 99
## 8 F leucogaster 475
## 9 F leucopus 16
## 10 F maniculatus 382
## # ℹ 71 more rowsWith the above code, we can proceed with arrange() to
sort the table according to a number of criteria so that we have a
better comparison. For instance, we might want to arrange the table
above in (i) an alphabetical order of the levels of the species and (ii)
in descending order of the count:
surveys %>%
count(sex, species) %>%
arrange(species, desc(n))## # A tibble: 81 × 3
## sex species n
## <fct> <chr> <int>
## 1 F albigula 675
## 2 M albigula 502
## 3 <NA> albigula 75
## 4 <NA> audubonii 75
## 5 F baileyi 1646
## 6 M baileyi 1216
## 7 <NA> baileyi 29
## 8 <NA> bilineata 303
## 9 <NA> brunneicapillus 50
## 10 <NA> chlorurus 39
## # ℹ 71 more rowsFrom the table above, we may learn that, for instance, there are 75
observations of the albigula species that are not specified for
its sex (i.e. NA).
Challenge - counting captures per year
Use group_by and count as we worked with
above to determine the number of captures per year.
Calculate the mean number of captures per year.
Reshaping with gather and spread
In surveys, the rows of surveys contain the
values of variables associated with each record (the unit), values such
as the weight or sex of each animal associated with each record. What if
instead of comparing records, we wanted to compare the different mean
weight of each genus between plots? (Ignoring plot_type for
simplicity).
We’d need to create a new table where each row (the unit) is
comprised of values of variables associated with each plot. In practical
terms this means the values in genus would become the names
of column variables and the cells would contain the values of the mean
weight observed on each plot.
Having created a new table, it is therefore straightforward to explore the relationship between the weight of different genera within, and between, the plots. The key point here is that we are still following a tidy data structure, but we have reshaped the data according to the observations of interest: average genus weight per plot instead of recordings per date.
The opposite transformation would be to transform column names into values of a variable.
We can do both these of transformations with two tidyr
functions, spread() and gather().
Spreading
spread() takes three principal arguments:
- the data
- the key column variable whose values will become new column
names.
- the value column variable whose values will fill the new column variables.
Further arguments include fill which, if set, fills in
missing values with the value provided.
Let’s use spread() to transform surveys to find the mean
weight of each genus in each plot over the entire survey period. We use
filter(), group_by() and
summarise() to filter our observations and variables of
interest, and create a new variable for the
mean_weight.
surveys_gw <- surveys %>%
filter(!is.na(weight)) %>%
group_by(plot_id, genus) %>%
summarize(mean_weight = mean(weight))## `summarise()` has grouped output by 'plot_id'. You can override using the
## `.groups` argument.str(surveys_gw)## gropd_df [196 × 3] (S3: grouped_df/tbl_df/tbl/data.frame)
## $ plot_id : num [1:196] 1 1 1 1 1 1 1 1 2 2 ...
## $ genus : chr [1:196] "Baiomys" "Chaetodipus" "Dipodomys" "Neotoma" ...
## $ mean_weight: num [1:196] 7 22.2 60.2 156.2 27.7 ...
## - attr(*, "groups")= tibble [24 × 2] (S3: tbl_df/tbl/data.frame)
## ..$ plot_id: num [1:24] 1 2 3 4 5 6 7 8 9 10 ...
## ..$ .rows : list<int> [1:24]
## .. ..$ : int [1:8] 1 2 3 4 5 6 7 8
## .. ..$ : int [1:9] 9 10 11 12 13 14 15 16 17
## .. ..$ : int [1:9] 18 19 20 21 22 23 24 25 26
## .. ..$ : int [1:8] 27 28 29 30 31 32 33 34
## .. ..$ : int [1:9] 35 36 37 38 39 40 41 42 43
## .. ..$ : int [1:8] 44 45 46 47 48 49 50 51
## .. ..$ : int [1:7] 52 53 54 55 56 57 58
## .. ..$ : int [1:7] 59 60 61 62 63 64 65
## .. ..$ : int [1:8] 66 67 68 69 70 71 72 73
## .. ..$ : int [1:7] 74 75 76 77 78 79 80
## .. ..$ : int [1:8] 81 82 83 84 85 86 87 88
## .. ..$ : int [1:8] 89 90 91 92 93 94 95 96
## .. ..$ : int [1:8] 97 98 99 100 101 102 103 104
## .. ..$ : int [1:8] 105 106 107 108 109 110 111 112
## .. ..$ : int [1:8] 113 114 115 116 117 118 119 120
## .. ..$ : int [1:7] 121 122 123 124 125 126 127
## .. ..$ : int [1:8] 128 129 130 131 132 133 134 135
## .. ..$ : int [1:9] 136 137 138 139 140 141 142 143 144
## .. ..$ : int [1:9] 145 146 147 148 149 150 151 152 153
## .. ..$ : int [1:10] 154 155 156 157 158 159 160 161 162 163
## .. ..$ : int [1:9] 164 165 166 167 168 169 170 171 172
## .. ..$ : int [1:8] 173 174 175 176 177 178 179 180
## .. ..$ : int [1:8] 181 182 183 184 185 186 187 188
## .. ..$ : int [1:8] 189 190 191 192 193 194 195 196
## .. ..@ ptype: int(0)
## ..- attr(*, ".drop")= logi TRUEThis yields surveys_gw where the observations for each
plot are spread across multiple rows, 196 observations of 3 variables.
Using spread() to key on genus with values
from mean_weight this becomes 24 observations of 11
variables, one row for each plot.
surveys_spread <- surveys_gw %>%
spread(key = genus, value = mean_weight)
str(surveys_spread)## gropd_df [24 × 11] (S3: grouped_df/tbl_df/tbl/data.frame)
## $ plot_id : num [1:24] 1 2 3 4 5 6 7 8 9 10 ...
## $ Baiomys : num [1:24] 7 6 8.61 NA 7.75 ...
## $ Chaetodipus : num [1:24] 22.2 25.1 24.6 23 18 ...
## $ Dipodomys : num [1:24] 60.2 55.7 52 57.5 51.1 ...
## $ Neotoma : num [1:24] 156 169 158 164 190 ...
## $ Onychomys : num [1:24] 27.7 26.9 26 28.1 27 ...
## $ Perognathus : num [1:24] 9.62 6.95 7.51 7.82 8.66 ...
## $ Peromyscus : num [1:24] 22.2 22.3 21.4 22.6 21.2 ...
## $ Reithrodontomys: num [1:24] 11.4 10.7 10.5 10.3 11.2 ...
## $ Sigmodon : num [1:24] NA 70.9 65.6 82 82.7 ...
## $ Spermophilus : num [1:24] NA NA NA NA NA NA NA NA NA NA ...
## - attr(*, "groups")= tibble [24 × 2] (S3: tbl_df/tbl/data.frame)
## ..$ plot_id: num [1:24] 1 2 3 4 5 6 7 8 9 10 ...
## ..$ .rows : list<int> [1:24]
## .. ..$ : int 1
## .. ..$ : int 2
## .. ..$ : int 3
## .. ..$ : int 4
## .. ..$ : int 5
## .. ..$ : int 6
## .. ..$ : int 7
## .. ..$ : int 8
## .. ..$ : int 9
## .. ..$ : int 10
## .. ..$ : int 11
## .. ..$ : int 12
## .. ..$ : int 13
## .. ..$ : int 14
## .. ..$ : int 15
## .. ..$ : int 16
## .. ..$ : int 17
## .. ..$ : int 18
## .. ..$ : int 19
## .. ..$ : int 20
## .. ..$ : int 21
## .. ..$ : int 22
## .. ..$ : int 23
## .. ..$ : int 24
## .. ..@ ptype: int(0)
## ..- attr(*, ".drop")= logi TRUEWe could now plot comparisons between the weight of genera (one is called a genus, multiple are called genera) in different plots, although we may wish to fill in the missing values first.
surveys_gw %>%
spread(genus, mean_weight, fill = 0) %>%
head()## # A tibble: 6 × 11
## # Groups: plot_id [6]
## plot_id Baiomys Chaetodipus Dipodomys Neotoma Onychomys Perognathus Peromyscus
## <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 1 7 22.2 60.2 156. 27.7 9.62 22.2
## 2 2 6 25.1 55.7 169. 26.9 6.95 22.3
## 3 3 8.61 24.6 52.0 158. 26.0 7.51 21.4
## 4 4 0 23.0 57.5 164. 28.1 7.82 22.6
## 5 5 7.75 18.0 51.1 190. 27.0 8.66 21.2
## 6 6 0 24.9 58.6 180. 25.9 7.81 21.8
## # ℹ 3 more variables: Reithrodontomys <dbl>, Sigmodon <dbl>, Spermophilus <dbl>Gathering
The opposing situation could occur if we had been provided with data
in the form of surveys_spread, where the genus names are
column names, but we wish to treat them as values of a genus variable
instead.
In this situation we are gathering the column names and turning them into a pair of new variables. One variable represents the column names as values, and the other variable contains the values previously associated with the column names.
gather() takes four principal arguments:
- the data
- the key column variable we wish to create from column names.
- the values column variable we wish to create and fill with values associated with the key.
- the names of the columns we use to fill the key variable (or to drop).
To recreate surveys_gw from surveys_spread
we would create a key called genus and value called
mean_weight and use all columns except plot_id
for the key variable. Here we exclude plot_id from being
gather()ed.
surveys_gather <- surveys_spread %>%
gather(key = "genus", value = "mean_weight", -plot_id)
str(surveys_gather)## gropd_df [240 × 3] (S3: grouped_df/tbl_df/tbl/data.frame)
## $ plot_id : num [1:240] 1 2 3 4 5 6 7 8 9 10 ...
## $ genus : chr [1:240] "Baiomys" "Baiomys" "Baiomys" "Baiomys" ...
## $ mean_weight: num [1:240] 7 6 8.61 NA 7.75 ...
## - attr(*, "groups")= tibble [24 × 2] (S3: tbl_df/tbl/data.frame)
## ..$ plot_id: num [1:24] 1 2 3 4 5 6 7 8 9 10 ...
## ..$ .rows : list<int> [1:24]
## .. ..$ : int [1:10] 1 25 49 73 97 121 145 169 193 217
## .. ..$ : int [1:10] 2 26 50 74 98 122 146 170 194 218
## .. ..$ : int [1:10] 3 27 51 75 99 123 147 171 195 219
## .. ..$ : int [1:10] 4 28 52 76 100 124 148 172 196 220
## .. ..$ : int [1:10] 5 29 53 77 101 125 149 173 197 221
## .. ..$ : int [1:10] 6 30 54 78 102 126 150 174 198 222
## .. ..$ : int [1:10] 7 31 55 79 103 127 151 175 199 223
## .. ..$ : int [1:10] 8 32 56 80 104 128 152 176 200 224
## .. ..$ : int [1:10] 9 33 57 81 105 129 153 177 201 225
## .. ..$ : int [1:10] 10 34 58 82 106 130 154 178 202 226
## .. ..$ : int [1:10] 11 35 59 83 107 131 155 179 203 227
## .. ..$ : int [1:10] 12 36 60 84 108 132 156 180 204 228
## .. ..$ : int [1:10] 13 37 61 85 109 133 157 181 205 229
## .. ..$ : int [1:10] 14 38 62 86 110 134 158 182 206 230
## .. ..$ : int [1:10] 15 39 63 87 111 135 159 183 207 231
## .. ..$ : int [1:10] 16 40 64 88 112 136 160 184 208 232
## .. ..$ : int [1:10] 17 41 65 89 113 137 161 185 209 233
## .. ..$ : int [1:10] 18 42 66 90 114 138 162 186 210 234
## .. ..$ : int [1:10] 19 43 67 91 115 139 163 187 211 235
## .. ..$ : int [1:10] 20 44 68 92 116 140 164 188 212 236
## .. ..$ : int [1:10] 21 45 69 93 117 141 165 189 213 237
## .. ..$ : int [1:10] 22 46 70 94 118 142 166 190 214 238
## .. ..$ : int [1:10] 23 47 71 95 119 143 167 191 215 239
## .. ..$ : int [1:10] 24 48 72 96 120 144 168 192 216 240
## .. ..@ ptype: int(0)
## ..- attr(*, ".drop")= logi TRUENote that now the NA genera are included in the
re-gathered format. Spreading and then gathering can be a useful way to
balance out a dataset so every replicate has the same composition.
We could also have used a specification for what columns to include.
This can be useful if you have a large number of identifying columns,
and it’s easier to specify what to gather than what to leave alone. And
if the columns are directly adjacent, we don’t even need to list them
all out - just use the : operator!
surveys_spread %>%
gather(key = "genus", value = "mean_weight", Baiomys:Spermophilus) %>%
head()## # A tibble: 6 × 3
## # Groups: plot_id [6]
## plot_id genus mean_weight
## <dbl> <chr> <dbl>
## 1 1 Baiomys 7
## 2 2 Baiomys 6
## 3 3 Baiomys 8.61
## 4 4 Baiomys NA
## 5 5 Baiomys 7.75
## 6 6 Baiomys NAExporting data
Now that you have learned how to use
dplyr to extract information from or
summarize your raw data, you may want to export these new data sets to
share them with your collaborators or for archival.
Similar to the read_csv() function used for reading CSV
files into R, there is a write_csv() function that
generates CSV files from data frames.
Before using write_csv(), we are going to create a new
folder, data, in our working directory that will store this
generated dataset. We don’t want to write generated datasets in the same
directory as our raw data. It’s good practice to keep them separate. The
data_raw folder should only contain the raw, unaltered
data, and should be left alone to make sure we don’t delete or modify
it. In contrast, our script will generate the contents of the
data directory, so even if the files it contains are
deleted, we can always re-generate them.
In preparation for our next lesson on plotting, we are going to prepare a cleaned up version of the data set that doesn’t include any missing data.
Let’s start by removing observations of animals for which
weight and hindfoot_length are missing, or the
sex has not been determined:
surveys_complete <- surveys %>%
filter(!is.na(weight), # remove missing weight
!is.na(hindfoot_length), # remove missing hindfoot_length
!is.na(sex)) # remove missing sexNow that our data set is ready, we can save it as a CSV file in our
data folder.
write_csv(surveys_complete, file = "data/surveys_complete.csv")