Previous chapter
TidyReshape Data
Next chapter

Welcome

The tools that you learned in the previous Primers work best when your data is organized in a specific way. This format is known as tidy data and it appears throughout the tidyverse. You will spend a lot of time as a data scientist wrangling your data into a useable format, so it is important to learn how to do this fast.

This tutorial will teach you how to recognize tidy data, as well as how to reshape untidy data into a tidy format. In it, you will learn the core data wrangling functions for the tidyverse:

  • gather() - which reshapes wide data into long data, and
  • spread() - which reshapes long data into wide data

This tutorial uses the core tidyverse packages, including ggplot2, dplyr, and tidyr, as well as the babynames package. All of these packages have been pre-installed and pre-loaded for your convenience.

Click the Next Topic button to begin.

Tidy Data

Variables, values, and observations

In Exploratory Data Analysis, we proposed three definitions that are useful for data science:

  • A variable is a quantity, quality, or property that you can measure.

  • A value is the state of a variable when you measure it. The value of a variable may change from measurement to measurement.

  • An observation is a set of measurements that are made under similar conditions (you usually make all of the measurements in an observation at the same time and on the same object). An observation will contain several values, each associated with a different variable. I’ll sometimes refer to an observation as a case or data point.

These definitions are tied to the concept of tidy data. To see how, let’s apply the definitions to some real data.

Quiz 1 - What are the variables?

table1
## # A tibble: 6 x 4
##   country      year  cases population
##   <chr>       <int>  <int>      <int>
## 1 Afghanistan  1999    745   19987071
## 2 Afghanistan  2000   2666   20595360
## 3 Brazil       1999  37737  172006362
## 4 Brazil       2000  80488  174504898
## 5 China        1999 212258 1272915272
## 6 China        2000 213766 1280428583

Quiz 2 - What are the variables?

Now consider this data set. Does it contain the same variables?

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

The shapes of data

These data sets reveal something important: you can reorganize the same set of variables, values, and observations in many different ways.

It’s not hard to do. If you run the code chunks below, you can see the same data displayed in three more ways.

Tidy data

Data can come in a variety of formats, but one format is easier to use in R than the others. This format is known as tidy data. A data set is tidy if:

  1. Each variable is in its own column
  2. Each observation is in its own row
  3. Each value is in its own cell (this follows from #1 and #2)

Among our tables above, only table1 is tidy.

table1
## # A tibble: 6 x 4
##   country      year  cases population
##   <chr>       <int>  <int>      <int>
## 1 Afghanistan  1999    745   19987071
## 2 Afghanistan  2000   2666   20595360
## 3 Brazil       1999  37737  172006362
## 4 Brazil       2000  80488  174504898
## 5 China        1999 212258 1272915272
## 6 China        2000 213766 1280428583

Extracting variables

To see why tidy data is easier to use, consider a basic task. Each code chunk below extracts the values of the cases variable as a vector and computes the mean of the variable. One uses a tidy table, table1:

mean(table1$cases)
## [1] 91276.67

The other uses an untidy table, table2:

mean(table2$count[c(1,3,5,7,9,11)])
## [1] 91276.67

Which line of code is easier to write? Which line could you write if you’ve only looked at the first row of the data?

Reusing code

Not only is the code for table1 easier to write, it is easier to reuse. To see what I mean, modify the code chunks below to compute the mean of the population variable for each table.

First with table1:

Then with table2:

Again table1 is easier to work with; you only need to change the name of the variable that you wish to extract. Code like this is easier to generalize to new data sets (if they are tidy) and easier to automate with a function.

Let’s look at one more advantage.

Calculations

Suppose you would like to compute the ratios of cases to population for each country and each year. To do this, you need to ensure that the correct value of cases is paired with the correct value of population when you do the calculation.

Again, this is hard to do with untidy table2:

table2$count[c(1,3,5,7,9,11)] / table2$count[c(2,4,6,8,10,12)]
## [1] 0.0000372741 0.0001294466 0.0002193930 0.0004612363 0.0001667495
## [6] 0.0001669488

But it is easy to do with tidy table1. Give it a try below:

These small differences may seem petty, but they add up over the course of a data analysis, stealing time and inviting mistakes.

Tidy data and R

The tidy data format works so well for R because it aligns the structure of your data with the mechanics of R:

  • R stores each data frame as a list of column vectors, which makes it easy to extract a column from a data frame as a vector. Tidy data places each variable in its own column vector, which makes it easy to extract all of the values of a variable to compute a summary statistic, or to use the variable in a computation.

  • R computes many functions and operations in a vectorized fashion, matching the first values of each vector of input to compute the first result, matching the second values of each input to compute the second result, and so on. Tidy data ensures that R will always match values with other values from the same operation whenever vector inputs are drawn from the same table.

As a result, most functions in R—and every function in the tidyverse—will expect your data to be organized into a tidy format. (You may have noticed above that we could use dplyr functions to work on table1, but not on table2).

Recap

“Data comes in many formats, but R prefers just one: tidy data.” — Garrett Grolemund

A data set is tidy if:

  1. Each variable is in its own column
  2. Each observation is in its own row
  3. Each value is in its own cell (this follows from #1 and #2)

Now that you know what tidy data is, what can you do about untidy data?

Gathering columns

Untidy data

“Tidy data sets are all alike; but every messy data set is messy in its own way.” — Hadley Wickham

How you tidy an untidy data set will depend on the initial configuration of the data. For example, consider the cases data set below.

cases
## # A tibble: 3 x 4
##   Country `2011` `2012` `2013`
##   <chr>    <dbl>  <dbl>  <dbl>
## 1 FR        7000   6900   7000
## 2 DE        5800   6000   6200
## 3 US       15000  14000  13000

Quiz 3 - What are the variables?

gather()

You can use the gather() function in the tidyr package to convert wide data to long data. Notice that gather() returns a tidy copy of the dataset, but does not alter the original dataset. If you wish to use this copy later, you’ll need to save it somewhere.

cases %>% gather(key = "year", value = "n", 2, 3, 4)
## # A tibble: 9 x 3
##   Country year      n
##   <chr>   <chr> <dbl>
## 1 FR      2011   7000
## 2 DE      2011   5800
## 3 US      2011  15000
## 4 FR      2012   6900
## 5 DE      2012   6000
## 6 US      2012  14000
## 7 FR      2013   7000
## 8 DE      2013   6200
## 9 US      2013  13000

Let’s take a closer look at the gather() syntax.

gather() syntax

Here’s the same call written without the pipe operator, which makes the syntax easier to see.

gather(cases, key = "year", value = "n", 2, 3, 4)

To use gather(), pass it the name of a data set to reshape followed by two new column names to use. Each name should be a character string surrounded by quotes:

  • the key string will become the name of a new column that contains former column names.
  • the value string will become the name of a new column that contains former cell values.

Finally, use numbers to tell gather() which columns to use to build the new columns. Here gather will use the second, third, and fourth columns. gather() will remove these columns from the results, but their contents will appear in the new columns. Any unspecified columns will remain in the dataset, their contents repeated as often as necessary to duplicate each relationship in the original untidy data set.

Key and Value columns

gather() relies on the idea of key:value pairs. A key value pair is a pair that lists a value alongside the name of the variable that the value describes. If you wanted, you could list every value in a dataset as a key value pair, but this is not how R lists values.

In a tidy data set, you will find “keys”—that is variable names—in the column names of the data set. The values will appear in the cells of the columns. Here we know that the key for each value in the year column is year. This arrangement reduces duplication.

Sometimes you will also find key value pairs listed beside each other in two separate columns, as in table2. Here the type column lists the keys that are associated with the count column. This layout is sometimes called “narrow” data.

Tidyr functions rely on the key value vocabulary to describe what should go where. In gather() the key argument describes the new column that contains the values that previously appeared in the tidy key position, i.e. in the column names. The value argument describes the new column that contains the values that previously appeared in the value positions, e.g. in the column cells.

Exercise 1 - Tidy table4a

Now that you’ve seen gather() in action, try using it to tidy table4a:

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

The result should contain three columns: country, year, and cases. Begin by modifying our code below.

Specifying columns

So far we’ve used numbers to describe which columns to reshape with gather(), but this isn’t necessary. gather() also recognizes column names as well as all of the select() helpers that you learned about in Isolating Data with dplyr. So for example, these expressions would all do the same thing:

table4a %>% gather(key = "year", value = "cases", 2, 3)
table4a %>% gather(key = "year", value = "cases", `1999`, `2000`)
table4a %>% gather(key = "year", value = "cases", -country)
table4a %>% gather(key = "year", value = "cases", one_of(c("1999", "2000")))

Notice that 1999 and 2000 are numbers. When you directly call column names that are numbers, you need to surround the names with backticks (otherwise gather() would think you mean the 1999th and 2000th columns). Use ?select_helpers to open a help page that lists the select helpers.

Exercise 2 - Tidy table4b

Use gather() and the - helper to tidy table4b into a dataset with three columns: country, year, and population.

table4b
## # A tibble: 3 x 3
##   country         `1999`     `2000`
## * <chr>            <int>      <int>
## 1 Afghanistan   19987071   20595360
## 2 Brazil       172006362  174504898
## 3 China       1272915272 1280428583

Converting output

If you looked closely at your results in the previous exercises, you may have noticed something odd: the new year column contains character vectors. You can tell because R displays a <chr> beneath the column name.

You can ask R to convert each new column to an appropriate data type by adding convert = TRUE to the gather() call. R will inspect the contents of the columns to choose the most likely data type. Give it a try in the code above!

The flexibility of gather()

cases, table4a, and table4b are all two by two tables, a simple form of wide data. But you will also encounter more complicated examples of wide data. For example, it is common for researchers to place one subject per row. In this case, you might see several columns of identifying information followed by a set of columns that list repeated measurements of the same variable. cases2 emulates such a data set.

cases2
## # A tibble: 3 x 6
##   city    country continent     `2011` `2012` `2013`
##   <chr>   <chr>   <chr>          <dbl>  <dbl>  <dbl>
## 1 Paris   FR      Europe          7000   6900   7000
## 2 Berlin  DE      Europe          5800   6000   6200
## 3 Chicago US      North America  15000  14000  13000

To tidy this data, you would want to keep the first three columns as they are. Can you tidy this data with gather()? Yes, an you already know how. Think about the problem and then tidy cases2 into a data set with five columns: city, country, continent, year, and cases.

Spreading columns

Narrow data

The pollution dataset below displays the amount of small and large particulate in the air of three cities. It illustrates another common type of untidy data. Narrow data uses a literal key column and a literal value column to store multiple variables. Can you tell here which is which?

pollution
## # A tibble: 6 x 3
##   city     size  amount
##   <chr>    <chr>  <dbl>
## 1 New York large     23
## 2 New York small     14
## 3 London   large     22
## 4 London   small     16
## 5 Beijing  large    121
## 6 Beijing  small    121

Quiz 4 - Which is the key column?

pollution
## # A tibble: 6 x 3
##   city     size  amount
##   <chr>    <chr>  <dbl>
## 1 New York large     23
## 2 New York small     14
## 3 London   large     22
## 4 London   small     16
## 5 Beijing  large    121
## 6 Beijing  small    121

Quiz 5 - Which is the value column?

pollution
## # A tibble: 6 x 3
##   city     size  amount
##   <chr>    <chr>  <dbl>
## 1 New York large     23
## 2 New York small     14
## 3 London   large     22
## 4 London   small     16
## 5 Beijing  large    121
## 6 Beijing  small    121

Pollution

A tidy version of pollution

spread()

You can “spread” the keys in a key column across their own set of columns with the spread() function in the tidyr package. To use spread() pass it the name of a data set to spread (provided here by the pipe %>%). Then tell spread which column to use as a key column and which column to use as a value column.

pollution %>% spread(key = size, value = amount)
## # A tibble: 3 x 3
##   city     large small
##   <chr>    <dbl> <dbl>
## 1 Beijing    121   121
## 2 London      22    16
## 3 New York    23    14

spread() will give each unique value in the key column its own column. The name of the value will become the column name. spread() will then redistribute the values in the value column across the new columns in a way that preserves every relationship in the original dataset.

Exercise 3 - Tidy table2

Use spread() to tidy table2 into a dataset with four columns: country, year, cases, and population. In short, convert table2 to look like table1.

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

To quote or not to quote

You may notice that both gather() and spread() take key and value arguments. And, in each case the arguments are set to column names. But in the gather() you must surround the names with quotes and in the spread() case you do not. Why is this?

table4b %>% gather(key = "year", value = "population", -country)
pollution %>% spread(key = size, value = amount)

Don’t let the difference trip you up. Instead think about what the quotes mean.

  • In R, any sequence of characters surrounded by quotes is a character string, which is a piece of data in and of itself.
  • Likewise, any sequence of characters not surrounded by quotes is the name of an object, which is a symbol that contains or points to a piece of data. Whenever R evaluates an object name, it searches for the object to find the data that it contains. If the object does not exist somewhere, R will return an error.

In our gather() code above, “year” and “population” refer to two columns that do not yet exist. If R tried to look for objects named year and population it wouldn’t find them (at least not in the table4b dataset). When we use gather() we are passing R two values (character strings) to use as the name of future columns that will appear in the result.

In our spread() code, key and value point to two columns that do exist in the pollution dataset: size and amount. When we use spread(), we are telling R to find these objects (columns) in the dataset and to use their contents to create the result. Since they exist, we do not need to surround them in quotation marks.

In practice, whether or not you need to use quotation marks will depend on how the author of your function wrote the function (For example, spread() will still work if you do include quotation marks). However, you can use the intuition above as a guide for how to use functions in the tidyverse.

Boys and girls in babynames

Let’s apply spread() to a real world inquiry. The plot below visualizes an aspect of the babynames data set from the babynames package. (See Work with Data for an introduction to the babynames data set.)

The ratio of girls to boys in babynames is not constant across time. We can explore this phenomenon further by recreating the data in the plot.

Review - Make the data

To make the data displayed in the plot above, I first grouped babynames by year and sex. Then I computed a summary for each group: total, which is equal to the sum of n for each group.

Use dplyr functions to recreate this process in the chunk below.

Review - Make the plot

Use the data below to make the plot above, which was built with ggplot2 functions.

## [1] "Good job! You can see that the data shows that less boys than girls were born for the years prior to 1936, and less girls than boys for the years after 1936."

A better way to look at the data

A better way to explore this phenomena would be to directly plot a ratio of boys to girls over time. To make such a plot, you would need to compute the ratio of boys to girls for each year from 1880 to 2015:

\[\text{ratio male} = \frac{\text{total male}}{\text{total female}}\]

But how can we plot this data? Our current iteration of babynames places the total number of boys and girls for each year in the same column, which makes it hard to use both totals in the same calculation.

babynames %>%
  group_by(year, sex) %>% 
  summarise(total = sum(n))
## # A tibble: 276 x 3
## # Groups:   year [138]
##     year sex    total
##    <dbl> <chr>  <int>
##  1  1880 F      90993
##  2  1880 M     110491
##  3  1881 F      91953
##  4  1881 M     100743
##  5  1882 F     107847
##  6  1882 M     113686
##  7  1883 F     112319
##  8  1883 M     104627
##  9  1884 F     129020
## 10  1884 M     114442
## # … with 266 more rows

A goal

It would be easier to calculate the ratio of boys to girls if we could reshape our data to place the total number of boys born per year in one column and the total number of girls born per year in another:

## # A tibble: 138 x 3
## # Groups:   year [138]
##     year      F      M
##    <dbl>  <int>  <int>
##  1  1880  90993 110491
##  2  1881  91953 100743
##  3  1882 107847 113686
##  4  1883 112319 104627
##  5  1884 129020 114442
##  6  1885 133055 107799
##  7  1886 144533 110784
##  8  1887 145981 101413
##  9  1888 178622 120851
## 10  1889 178366 110580
## # … with 128 more rows

Then we could compute the ratio by piping our data into a call like mutate(ratio = M / F).

Exercise 4 - Make the plot

Add to the code below to:

  1. Reshape the layout to place the total number of boys per year in one column and the total number of girls born per year in a second column.
  2. Compute the ratio of boys to girls.
  3. Plot the ratio of boys to girls over time.
## [1] "Good job!"

Interesting

Our results reveal a conspicuous oddity, that is easier to interpret if we turn the ratio into a percentage.

The percent of recorded male births is unusually low between 1880 and 1936. What is happening? One insight is that the data comes from the United States Social Security office, which was only created in 1936. As a result, we can expect the data prior to 1936 to display a survivorship bias.

Recap

Your data will be easier to work with in R if you reshape it into a tidy layout at the start of your analysis. Data is tidy if:

  1. Each variable is in its own column
  2. Each observation is in its own row
  3. Each value is in its own cell

You can use gather() and spread(), or some iterative sequence of the two, to reshape your data into any possible configuration that:

  1. Retains all of the values in your original data set, and
  2. Retains all of the relationships between values in your original data set.

In particular, you can use these functions to recast your data into a tidy layout.

Food for thought

It is not always clear whether or not a data set is tidy. For example, the version of babynames that was tidy when we wanted to plot total children by year, was no longer tidy when we wanted to compute the ratio of male to female children.

The ambiguity comes from the definition of tidy data. Tidiness depends on the variables in your data set. But what is a variable depends on what you are trying to do.

To identify the variables that you need to work with, describe what you want to do with an equation. Each variable in the equation should correspond to a variable in your data.

So in our first case, we wanted to make a plot with the following mappings (e.g. equations)

\[x = year\] \[y = total\] \[color = sex\]

To do this, we needed a data set that placed \(year\), \(total\), and \(sex\) each in their own columns.

In our second case we wanted to compute \(ratio\), where

\[\text{ratio} = \frac{\text{male}}{\text{female}}\] This formula has three variables: \(ratio male\), \(total male\), and \(total female\). To create the first variable, we required a data set that isolated the second and third variables (\(total male\) and \(total female\)) in their own columns.