Chapter 11 Tidying Data

Even before we conduct analysis or calculations, we need to put our data into the correct format. The goal here is to rearrange a messy dataset into one that is tidy.

The two most important properties of tidy data are:

  1. Each column is a variable.
  2. Each row is an observation.

Tidy data is easier to work with because you have a consistent way of referring to variables (as column names) and observations (as row indices). The data then becomes easier to manipulate, visualize, and model.

For more on the concept of tidy data, read Hadley Wickham’s paper here

11.1 Wide vs. Long Formats

“Tidy datasets are all alike but every messy dataset is messy in its own way.” – Hadley Wickham

Tabular datasets can be arranged in many ways. For instance, consider the data below. Each data set displays information on heart rates observed in individuals across 3 different time periods. But the data are organized differently in each table.

wide <- data.frame(
  name = c("Wilbur", "Petunia", "Gregory"),
  time1 = c(67, 80, 64),
  time2 = c(56, 90, 50),
  time3 = c(70, 67, 101)
)
kable(wide)
name time1 time2 time3
Wilbur 67 56 70
Petunia 80 90 67
Gregory 64 50 101

long <- data.frame(
  name = c("Wilbur", "Petunia", "Gregory", "Wilbur", "Petunia", "Gregory", "Wilbur", "Petunia", "Gregory"),
  time = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
  heartrate = c(67, 80, 64, 56, 90, 50, 70, 67, 10)
)
kable(long)
name time heartrate
Wilbur 1 67
Petunia 1 80
Gregory 1 64
Wilbur 2 56
Petunia 2 90
Gregory 2 50
Wilbur 3 70
Petunia 3 67
Gregory 3 10

Question: Which one of these do you think is the tidy format?

Answer: The first dataframe (the “wide” one) would not be considered tidy because values (i.e., heart rate) are spread across multiple columns.

We often refer to these different structures as “long” vs. “wide” formats. In the “long” format, you usually have 1 column for the observed variable and the other columns are ID variables.

For the “wide” format each row is often a site/subject/patient and you have multiple observation variables containing the same type of data. These can be either repeated observations over time, or observation of multiple variables (or a mix of both). In the above case, we had the same kind of data (heart rate) entered across 3 different columns, corresponding to three different time periods.

knitr::include_graphics(path = "img/tidyr-fig1.png")

You may find data input in the “wide” format to be simpler, and some other applications may prefer “wide” format data. However, many of R’s functions have been designed assuming you have “long” format data.

11.2 Tidying the Gapminder Data

Let’s look at the structure of our original gapminder dataframe:

gap <- read.csv("data/gapminder-FiveYearData.csv", stringsAsFactors = TRUE)
kable(head(gap))
country year pop continent lifeExp gdpPercap
Afghanistan 1952 8425333 Asia 28.8 779
Afghanistan 1957 9240934 Asia 30.3 821
Afghanistan 1962 10267083 Asia 32.0 853
Afghanistan 1967 11537966 Asia 34.0 836
Afghanistan 1972 13079460 Asia 36.1 740
Afghanistan 1977 14880372 Asia 38.4 786

Question: Is this data frame wide or long?

Answer: This data frame is somewhere in between the purely ‘long’ and ‘wide’ formats. We have 3 “ID variables” (continent, country, year) and 3 “Observation variables” (pop, lifeExp, gdpPercap).

Despite not having ALL observations in 1 column, this intermediate format makes sense given that all 3 observation variables have different units. As we have seen, many of the functions in R are often vector based, and you usually do not want to do mathematical operations on values with different units.

On the other hand, there are some instances in which a purely long or wide format is ideal (e.g. plotting). Likewise, sometimes you’ll get data on your desk that is poorly organized, and you’ll need to reshape it.

11.3 tidyr Functions

Thankfully, the tidyr package will help you efficiently transform your data regardless of original format.

# Install the "tidyr" package (only necessary one time)
# install.packages("tidyr") # Not Run

# Load the "tidyr" package (necessary every new R session)
library(tidyr)

11.3.1 gather

Until now, we’ve been using the nicely formatted original gapminder dataset. This dataset is not quite wide and not quite long – it’s something in the middle, but ‘real’ data (i.e. our own research data) will never be so well organized. Here let’s start with the wide format version of the gapminder dataset.

gap_wide <- read.csv("data/gapminder_wide.csv", stringsAsFactors = FALSE)
kable(head(gap_wide))
continent country gdpPercap_1952 gdpPercap_1957 gdpPercap_1962 gdpPercap_1967 gdpPercap_1972 gdpPercap_1977 gdpPercap_1982 gdpPercap_1987 gdpPercap_1992 gdpPercap_1997 gdpPercap_2002 gdpPercap_2007 lifeExp_1952 lifeExp_1957 lifeExp_1962 lifeExp_1967 lifeExp_1972 lifeExp_1977 lifeExp_1982 lifeExp_1987 lifeExp_1992 lifeExp_1997 lifeExp_2002 lifeExp_2007 pop_1952 pop_1957 pop_1962 pop_1967 pop_1972 pop_1977 pop_1982 pop_1987 pop_1992 pop_1997 pop_2002 pop_2007
Africa Algeria 2449 3014 2551 3247 4183 4910 5745 5681 5023 4797 5288 6223 43.1 45.7 48.3 51.4 54.5 58.0 61.4 65.8 67.7 69.2 71.0 72.3 9279525 10270856 11000948 12760499 14760787 17152804 20033753 23254956 26298373 29072015 31287142 33333216
Africa Angola 3521 3828 4269 5523 5473 3009 2757 2430 2628 2277 2773 4797 30.0 32.0 34.0 36.0 37.9 39.5 39.9 39.9 40.6 41.0 41.0 42.7 4232095 4561361 4826015 5247469 5894858 6162675 7016384 7874230 8735988 9875024 10866106 12420476
Africa Benin 1063 960 949 1036 1086 1029 1278 1226 1191 1233 1373 1441 38.2 40.4 42.6 44.9 47.0 49.2 50.9 52.3 53.9 54.8 54.4 56.7 1738315 1925173 2151895 2427334 2761407 3168267 3641603 4243788 4981671 6066080 7026113 8078314
Africa Botswana 851 918 984 1215 2264 3215 4551 6206 7954 8647 11004 12570 47.6 49.6 51.5 53.3 56.0 59.3 61.5 63.6 62.7 52.6 46.6 50.7 442308 474639 512764 553541 619351 781472 970347 1151184 1342614 1536536 1630347 1639131
Africa Burkina Faso 543 617 723 795 855 743 807 912 932 946 1038 1217 32.0 34.9 37.8 40.7 43.6 46.1 48.1 49.6 50.3 50.3 50.6 52.3 4469979 4713416 4919632 5127935 5433886 5889574 6634596 7586551 8878303 10352843 12251209 14326203
Africa Burundi 339 380 355 413 464 556 560 622 632 463 446 430 39.0 40.5 42.0 43.5 44.1 45.9 47.5 48.2 44.7 45.3 47.4 49.6 2445618 2667518 2961915 3330989 3529983 3834415 4580410 5126023 5809236 6121610 7021078 8390505

The first step towards getting our nice intermediate data format is to first convert from the wide to the long format.

The function gather() will ‘gather’ the observation variables into a single variable. This is sometimes called “melting” your data, because it melts the table from wide to long. Those data will be melted into two variables: one for the variable names, and the other for the variable values.

gap_long <- gap_wide %>%
    gather(obstype_year, obs_values, 3:38)
kable(head(gap_long))
continent country obstype_year obs_values
Africa Algeria gdpPercap_1952 2449
Africa Angola gdpPercap_1952 3521
Africa Benin gdpPercap_1952 1063
Africa Botswana gdpPercap_1952 851
Africa Burkina Faso gdpPercap_1952 543
Africa Burundi gdpPercap_1952 339

Notice that we put 3 arguments into the gather() function:

  1. The name the new column for the new ID variable (obstype_year),
  2. The name for the new amalgamated observation variable (obs_value),
  3. The indices of the old observation variables (3:38, signalling columns 3 through 38) that we want to gather into one variable. Notice that we don’t want to melt down columns 1 and 2, as these are considered “ID” variables.

We can also select observation variables using:

  • variable indices
  • variable names (without quotes)
  • x:z to select all variables between x and z
  • -y to exclude y
  • starts_with(x, ignore.case = TRUE): all names that starts with x
  • ends_with(x, ignore.case = TRUE): all names that ends with x
  • contains(x, ignore.case = TRUE): all names that contain x

See the select() function in dplyr for more options.

For instance, here we do the same thing with (1) the starts_with function, and (2) the - operator:

# 1. with the starts_with() function
gap_long <- gap_wide %>%
    gather(obstype_year, obs_values, starts_with('pop'),
           starts_with('lifeExp'), starts_with('gdpPercap'))
kable(head(gap_long))
continent country obstype_year obs_values
Africa Algeria pop_1952 9279525
Africa Angola pop_1952 4232095
Africa Benin pop_1952 1738315
Africa Botswana pop_1952 442308
Africa Burkina Faso pop_1952 4469979
Africa Burundi pop_1952 2445618

# 2. with the - operator
gap_long <- gap_wide %>% 
  gather(obstype_year, obs_values, -continent, -country)
kable(head(gap_long))
continent country obstype_year obs_values
Africa Algeria gdpPercap_1952 2449
Africa Angola gdpPercap_1952 3521
Africa Benin gdpPercap_1952 1063
Africa Botswana gdpPercap_1952 851
Africa Burkina Faso gdpPercap_1952 543
Africa Burundi gdpPercap_1952 339

However you choose to do it, notice that the output collapses all of the measured variables into two columns: one containing the new ID variable, the other containing the observation value for that row.

11.3.2 separate

You’ll notice that in our long dataset, obstype_year actually contains 2 pieces of information, the observation type (pop, lifeExp, or gdpPercap) and the year.

We can use the separate() function to split the character strings into multiple variables:

gap_long_sep <- gap_long %>% 
  separate(obstype_year, into = c('obs_type','year'), sep = "_") %>% 
  mutate(year = as.integer(year))
kable(head(gap_long_sep))
continent country obs_type year obs_values
Africa Algeria gdpPercap 1952 2449
Africa Angola gdpPercap 1952 3521
Africa Benin gdpPercap 1952 1063
Africa Botswana gdpPercap 1952 851
Africa Burkina Faso gdpPercap 1952 543
Africa Burundi gdpPercap 1952 339

11.3.3 spread

The opposite of gather() is spread(). It spreads our observation variables back out to make a wider table. We can use this function to spread our gap_long() to the original “medium” format.

gap_medium <- gap_long_sep %>% 
  spread(obs_type, obs_values)
kable(head(gap_medium))
continent country year gdpPercap lifeExp pop
Africa Algeria 1952 2449 43.1 9279525
Africa Algeria 1957 3014 45.7 10270856
Africa Algeria 1962 2551 48.3 11000948
Africa Algeria 1967 3247 51.4 12760499
Africa Algeria 1972 4183 54.5 14760787
Africa Algeria 1977 4910 58.0 17152804

All we need is some quick fixes to make this dataset identical to the original gapminder dataset:

gap <- read.csv("data/gapminder-FiveYearData.csv")
kable(head(gap))
country year pop continent lifeExp gdpPercap
Afghanistan 1952 8425333 Asia 28.8 779
Afghanistan 1957 9240934 Asia 30.3 821
Afghanistan 1962 10267083 Asia 32.0 853
Afghanistan 1967 11537966 Asia 34.0 836
Afghanistan 1972 13079460 Asia 36.1 740
Afghanistan 1977 14880372 Asia 38.4 786
# rearrange columns
gap_medium <- gap_medium[,names(gap)]
kable(head(gap_medium))
country year pop continent lifeExp gdpPercap
Algeria 1952 9279525 Africa 43.1 2449
Algeria 1957 10270856 Africa 45.7 3014
Algeria 1962 11000948 Africa 48.3 2551
Algeria 1967 12760499 Africa 51.4 3247
Algeria 1972 14760787 Africa 54.5 4183
Algeria 1977 17152804 Africa 58.0 4910
# arrange by country, continent, and year
gap_medium <- gap_medium %>% 
  arrange(country,continent,year)
kable(head(gap_medium))
country year pop continent lifeExp gdpPercap
Afghanistan 1952 8425333 Asia 28.8 779
Afghanistan 1957 9240934 Asia 30.3 821
Afghanistan 1962 10267083 Asia 32.0 853
Afghanistan 1967 11537966 Asia 34.0 836
Afghanistan 1972 13079460 Asia 36.1 740
Afghanistan 1977 14880372 Asia 38.4 786

What we just told you will become obsolete…

gather and spread are being replaced by pivot_longer and pivot_wider in tidyr 1.0.0, which use ideas from the cdata package to make reshaping easier to think about. In future classes, we’ll migrate to those functions.

11.4 More tidyverse

dplyr and tidyr have many more functions to help you wrangle and manipulate your data. See the Data Wrangling Cheat Sheet for more.

There are some other useful packages in the tidyverse:

  • ggplot2 for plotting (we’ll cover this in the Visualization module.)
  • readr and haven for reading in data
  • purrr for working iterations.
  • stringr, lubridate, forcats for manipulating strings, dates, and factors, respectively
  • many many more! Take a peak at the tidyverse github page

**Pro Tip:** To install and load the core tidyverse packages (includes tidyr, dplyr, and ggplot2, among others), try:

## NOT run
# install.packages("tidyverse")
library(tidyverse)

11.5 Challenges

Challenge 1.

Subset the results from Challenge #3 (of the previous chapter) to select only the country, year, and gdpPercap_diff columns. Use tidyr put it in wide format so that countries are rows and years are columns.

Challenge 2.

Now turn the dataframe above back into the long format with three columns: country, year, and gdpPercap_diff.

Acknowledgments

Some of these materials in this module were adapted from:

library(kableExtra)