tidyr

The output of most of the R chunks isn’t included in the HTML version of the file to keep it to a more reasonable file size. You can run the code in R to see the output.

Setup

It’s important you have {tidyr} version 1.1.0 (at least) installed. Check this with:

packageVersion("tidyr")

## [1] '1.3.1'

If needed, update it with:

install.packages("tidyr")

Load it as part of tidyverse

library(tidyverse)

Tidy data

The goal of {tidyr} is to help you create tidy data. Tidy data is data where:

1. Each variable is a column; each column is a variable.

2. Each observation is a row; each row is an observation.

3. Each value is a cell; each cell is a single value.

Tidy data describes a standard way of storing data that is used wherever possible throughout the tidyverse. If you ensure that your data is tidy, you’ll spend less time fighting with the tools and more time working on your analysis.

Data

We’re going to work with a dataset that’s part of the {tidyr} package: who

The details: A subset of data from the World Health Organization Global Tuberculosis Report, and accompanying global populations. The data uses the original codes given by the World Health Organization.

The column names for columns 5 through 60 are made by combining “new_” to a code for method of diagnosis (rel = relapse, sn = negative pulmonary smear, sp = positive pulmonary smear, ep = extrapulmonary) to a code for gender (f = female, m = male) to a code for age group (014 = 0-14 yrs of age, 1524 = 15-24 years of age, 2534 = 25 to 34 years of age, 3544 = 35 to 44 years of age, 4554 = 45 to 54 years of age, 5564 = 55 to 64 years of age, 65 = 65 years of age or older).

Column names:

names(who)

##  [1] "country"      "iso2"         "iso3"         "year"         "new_sp_m014" 
##  [6] "new_sp_m1524" "new_sp_m2534" "new_sp_m3544" "new_sp_m4554" "new_sp_m5564"
## [11] "new_sp_m65"   "new_sp_f014"  "new_sp_f1524" "new_sp_f2534" "new_sp_f3544"
## [16] "new_sp_f4554" "new_sp_f5564" "new_sp_f65"   "new_sn_m014"  "new_sn_m1524"
## [21] "new_sn_m2534" "new_sn_m3544" "new_sn_m4554" "new_sn_m5564" "new_sn_m65"  
## [26] "new_sn_f014"  "new_sn_f1524" "new_sn_f2534" "new_sn_f3544" "new_sn_f4554"
## [31] "new_sn_f5564" "new_sn_f65"   "new_ep_m014"  "new_ep_m1524" "new_ep_m2534"
## [36] "new_ep_m3544" "new_ep_m4554" "new_ep_m5564" "new_ep_m65"   "new_ep_f014" 
## [41] "new_ep_f1524" "new_ep_f2534" "new_ep_f3544" "new_ep_f4554" "new_ep_f5564"
## [46] "new_ep_f65"   "newrel_m014"  "newrel_m1524" "newrel_m2534" "newrel_m3544"
## [51] "newrel_m4554" "newrel_m5564" "newrel_m65"   "newrel_f014"  "newrel_f1524"
## [56] "newrel_f2534" "newrel_f3544" "newrel_f4554" "newrel_f5564" "newrel_f65"

Let’s look at a few random rows:

sample_n(who, 10)

In this data set, each row (each observation) is a country-year. Each column is a measure-age value.

There are situations though where we might want each row to be a different unit of observation. We could make a really wide data set (one with lots of columns), where each row is a single country and there are columns for every measure in every year. Or we could make a data set that’s really long, where each row is a country-year-measure-age observation. Or one that’s in between, with each row being country-year-age, and there are columns for each measure.

These different configurations support different types of statistical models, data transformations, and data visualizations. No configuration is necessarily the right one or better than the others in a vacuum - it depends on what you want to do with the data!

I’m going to reduce this dataset to just a few countries that have at least some data filled in to make it easier for us to work with:

who2 <- filter(who, country %in% c("Canada", "Czech Republic", "Morocco"))

Pivot Longer

We make a data set longer by reducing the number of columns and increasing the number of rows. We’ll stack some of the columns on top of each other, and duplicate the values in other columns to make this happen. This changes what the unit of observation is (what each row represents).

Image source: https://epirhandbook.com/en/pivoting-data.html

A Mini Example

Before we work with our larger data set, let’s look at a very small one so it’s easier to see what’s happening.

tribble() is a function that makes it easier to manually create a tibble (data frame)

foods <- tribble(~id, ~a, ~b, ~c,
                 1, "apple", "banana", "cod", 
                 2, "asparagus", "bacon", "chocolate")
foods

In foods, each row (observation) is a person – each person’s favorite food starting with each letter. But we want to get this data set to look like:

    id category item     
     1 a        apple    
     1 b        banana   
     1 c        cod      
     2 a        asparagus
     2 b        bacon    
     2 c        chocolate

Where each row (observation) is a person-food combination: each food is in a row by itself, and the column names have become encoded in a variable.

To do this, we use the function pivot_longer() - in previous versions of {tidyr}, this function was called gather. The syntax was similar, but the arguments had different names. In base R, you can do similar transformations with the reshape function.

Like other tidyverse functions, we’ll start with the data frame name. Then we’ll select which we’re going to collapse – meaning there won’t be columns with those names anymore because we’ll stack the names and values on top of each other to make more rows. Then we need to tell it what to name the new variable it will make that will have the column names in it (our new categorical variable), and a name as well for the new column that will have the values in it (the values that were in the columns originally).

We specify which columns we want using any of the select syntax or select helpers we’ve been talking about in previous sessions.

pivot_longer(foods, 
             cols = -id, # collapse all columns except (-) id
             names_to = "category",  # name for the new column that will have the old column names as values
             values_to = "item")  # name for the new column that will store the data from the old columns

The id column has values duplicated, once for each of the original 3 columns that we collapsed, and each value of our new category column appears twice, because there were 2 rows in food when we started.

EXERCISE 1

Pivot the pops data frame below to be in a longer format, with rows for each country-year instead of each country.

Hint: Break down the problem into steps. Think about which columns of the original dataset need to be collapsed together to make the data “long”.

pops <- tribble(~country,  ~year2011,  ~year2012,  ~year2013,
                "Brazil",  196935134, 198656019, 200361925,
                "Germany",  82892904,  82800121,  82726626,
                "Kenya",    42027891,  43178141,  44353691)

WHO data

Now, the WHO TB data is a bit more complicated than the simple example. We know how to take the data set into completely long format, by collapsing all of the columns except the first 4 that have country name info and year:

names(who2)

##  [1] "country"      "iso2"         "iso3"         "year"         "new_sp_m014" 
##  [6] "new_sp_m1524" "new_sp_m2534" "new_sp_m3544" "new_sp_m4554" "new_sp_m5564"
## [11] "new_sp_m65"   "new_sp_f014"  "new_sp_f1524" "new_sp_f2534" "new_sp_f3544"
## [16] "new_sp_f4554" "new_sp_f5564" "new_sp_f65"   "new_sn_m014"  "new_sn_m1524"
## [21] "new_sn_m2534" "new_sn_m3544" "new_sn_m4554" "new_sn_m5564" "new_sn_m65"  
## [26] "new_sn_f014"  "new_sn_f1524" "new_sn_f2534" "new_sn_f3544" "new_sn_f4554"
## [31] "new_sn_f5564" "new_sn_f65"   "new_ep_m014"  "new_ep_m1524" "new_ep_m2534"
## [36] "new_ep_m3544" "new_ep_m4554" "new_ep_m5564" "new_ep_m65"   "new_ep_f014" 
## [41] "new_ep_f1524" "new_ep_f2534" "new_ep_f3544" "new_ep_f4554" "new_ep_f5564"
## [46] "new_ep_f65"   "newrel_m014"  "newrel_m1524" "newrel_m2534" "newrel_m3544"
## [51] "newrel_m4554" "newrel_m5564" "newrel_m65"   "newrel_f014"  "newrel_f1524"
## [56] "newrel_f2534" "newrel_f3544" "newrel_f4554" "newrel_f5564" "newrel_f65"

Each of the measurement columns has the count of the number of people with the given diagnosis, gender, and age group.

pivot_longer(who2, 
             -country:-year,  # all except first 4 columns (this is the cols argument)
             names_to = "measure",  # make up a new name
             values_to = "count")  # make up a new name

There were 56 columns that we collapsed – that weren’t the ID variables. So the first row in the original data expanded to now be 56 rows in the longer data set.

But what if we don’t want to collapse them completely? What if we want one column per age group? or per measure instead? Or what if all of the columns aren’t of the same type? If some are numeric while others have character or factor data?

In really complicated cases, you may need to split your data into multiple data sets, reshape the parts, and then join the results back together.

In most cases though, you can pivot to a completely long format, and then spread columns back out wider after some additional transformations. If there are different data types, they get converted to the more general one, following: boolean -> integer -> numeric -> character. While you need to be careful if you are working with precise numerical values (decimal values), if you’re working with whole numbers or the precision (number of significant digits) in the data is small, converting from numeric to character and back again will not lose information.

Separate variable names

Ok, back to our WHO data. One trick before we learn how to pivot wider to undo what we did. When we pivot longer, our new “measure” variable actually has multiple pieces of information in it. The variable names are of the format: “new_” diagnosis _ m/f age. Ideally, we’d like this information separated, so we have a column for the diagnosis method, a column for the gender, and a column for the age group.

We can tell pivot_longer to do this for us:

names(who2)

pivot_longer(who2, -country:-year,
             # we'll keep 3 columns: make up a name for each
             names_to=c("diagnosis", "gender", "age"),  
             # this is a regular expression that captures the pattern
             # each () set is a value to keep and turn into a column;
             # we are dropping "new"
             names_pattern = "new_?(.+)_([mf])(\\d+)",
             values_to="count")  

If the variable names had a simpler pattern, for example: year_measure, then we could use the “names_sep” argument instead of the more complicated “names_pattern” to split up the column.

Pivot Wider

Now, we’ve got our completely long data. How do we get it into a form where there’s one row per country-year-gender-age, with one column per measure? Essentially, we want to split the diagnosis column into multiple columns for each type of diagnosis.

Image Source: https://epirhandbook.com/en/pivoting-data.html

# same as above, just save it
long_data <- pivot_longer(who2, -country:-year,
             names_to=c("diagnosis", "gender", "age"), 
             values_to="count",
             names_pattern = "new_?(.+)_([mf])(\\d+)") 

names(long_data)

## [1] "country"   "iso2"      "iso3"      "year"      "diagnosis" "gender"   
## [7] "age"       "count"

# spread it back out
pivot_wider(long_data, 
            names_from=diagnosis,
            values_from=count)

What happens if our long data doesn’t have every combination of values to fill into our new data set? For example, if the data started long instead of wide?

For example, if we had data that looked like:

long_data %>%
  filter(!is.na(count)) %>%
  sample_n(20) %>%
  arrange(country, year, diagnosis, age, gender)

It’s ok - we can still pivot wider. By default, it will fill in NA where it doesn’t have a value, or we can tell it to fill with another value (such as 0).

long_data %>%
  filter(!is.na(count)) %>%
  sample_n(20) %>%
  arrange(country, year, diagnosis, age, gender) %>%
  pivot_wider(names_from=diagnosis, 
              values_from=count)

We could also pivot wider more than one column at once. Let’s have one row per country-year-diagnosis, and one column for each age-gender combination:

names(long_data)

## [1] "country"   "iso2"      "iso3"      "year"      "diagnosis" "gender"   
## [7] "age"       "count"

long_data %>%
  pivot_wider(names_from=c(age, gender),
              # controls how new variable names are created
              # uses syntax from the glue package
              names_glue="{gender}_{age}", 
              values_from=count)

EXERCISE 2

Pivot the long_data data frame wider so that each row is a country-diagnosis-gender-age observation (pivot “year” so you have one column for each year in the output).

Hint: Break down the problem into steps. Think about which column(s) in your original dataset need to be spread out into multiple columns to make the data “wide”.

Separate

Much like we had pivot_longer() split up the components of the variable names for us into measure, age, and gender, we can use the separate() function to do that to columns outside of pivoting.

nobels <- tribble(~name, ~category,
                    "Bob Dylan", "Literature",
                    "Anne L'Huillier", "Physics",
                    "Narges Mohammadi", "Peace",
                    "Roger Penrose", "Physics",
                    "Jennifer Doudna", "Chemistry",
                    "Abhijit Banerjee", "Economics",
                    "Jean-Pierre Sauvage", "Chemistry")
nobels

We could split the name column into first name and last name, using a space character as a delimiter.

separate(nobels,  # data frame name
         name,  # existing column name to split
         into=c("first", "last"),  # new column names 
         sep=" ")  # what to split on

You need to know the maximum number of pieces that your data will be split into, or you’ll lose some of the information. If Jean-Pierre Sauvage’s name had a space instead of a hyphen:

nobels <- tribble(~name, ~category,
                    "Bob Dylan", "Literature",
                    "Anne L'Huillier", "Physics",
                    "Narges Mohammadi", "Peace",
                    "Roger Penrose", "Physics",
                    "Jennifer Doudna", "Chemistry",
                    "Abhijit Banerjee", "Economics",
                    "Jean Pierre Sauvage", "Chemistry")

separate(nobels,
         name,
         into=c("first", "last"),
         sep=" ")

## Warning: Expected 2 pieces. Additional pieces discarded in 1 rows [7].

We get a warning, and “Sauvage” was just dropped completely! (You can control this behavior to some extent with the extra argument.)

It’s better to specify the maximum number of columns possible instead:

separate(nobels,
         name,
         into=c("first1", "first2", "last"),
         sep=" ")

## Warning: Expected 3 pieces. Missing pieces filled with `NA` in 6 rows [1, 2, 3, 4, 5,
## 6].

Now the warning is telling us it filled in NA values where it didn’t find 3 pieces after separating. Also, the new column names are not exactly suitable since other observations in the dataset all have last names (not NA).

But separate really works best when you’re separating something that will separate into a fixed number of columns.

Side note: working with names is HARD! For some examples why, see https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

EXERCISE 3

Separate the “location” variable in addresses into separate “city” and “state” columns. Hint: the “sep” value can be more than one character in length.

addresses <- tribble(~name, ~location,
                     "Katherine", "Chicago, IL",
                     "Bob", "Bend, OR",
                     "Louka", "Honolulu, HI",
                     "Alex", "San Jose, CA")

separate_rows()

In cases where you want to separate out a column, but you don’t know how many values might be in it, another option is to make one row, instead of one column, per value. It’s combining a separate with a pivot longer.

This can be useful if you have a column with a delimited list of values in it. For example, if you ask a multiple choice question that allows multiple answers in Qualtrics (survey platform), you’ll get a single column with all of the answer that people checked:

survey <- tribble(~id, ~q1, ~q2,
                  45, 4, "Time",
                  46, 3, "Quality;Cost",
                  47, 4, "Time;Cost;Compatibility;Warranty")
survey

How do we deal with q2?

separate_rows(survey, 
              q2,  # which column to separate
              sep=";")

Creating Indicator Variables

Then, we may want to create one column per answer choice – create indicator variables (aka dummy variables or one-hot encoding).

separate_rows(survey, 
              q2,  # which column to separate
              sep=";") %>%
  mutate(exists=1) %>%  # add a column to encode values later
  pivot_wider(names_from=q2,  # new column names, one for each value
              values_from=exists,  # so there will be a 1 where there was a value
              values_fill=0) ## fills in missing vals with 0 instead of NA

Learning More

The {tidyr} documentation has a tutorial on pivoting: https://tidyr.tidyverse.org/articles/pivot.html

Recap

You now can use different {tidyr} commands, such as pivot_longer, pivot_wider, separate, separate_rows, to reshape your data in a wide variety of ways.

Next session: the {ggplot2} functions for visualizing your data.

Answers to the exercises

EXERCISE 1

# solution 1
pivot_longer(pops, 
             cols=c("year2011", "year2012", "year2013"), # columns to collapse together
             names_to = "year",                          # column that will store the year names
             values_to = "population")                   # column that will store population values

# solution 2
pivot_longer(pops, 
             cols=-country, # same as selecting all the year columns
             names_to = "year", 
             values_to = "population")

EXERCISE 2

# check columns in starting dataset
names(long_data)

## [1] "country"   "iso2"      "iso3"      "year"      "diagnosis" "gender"   
## [7] "age"       "count"

# solution 1
long_data %>% 
  pivot_wider(names_from = year, 
              values_from = count)

# solution 2 - more elegant columns names after pivoting
long_data %>% 
  pivot_wider(names_from = year, 
              names_glue = "year_{year}",
              values_from = count)

EXERCISE 3

separate(addresses, 
         location, 
         into=c("city", "state"), 
         sep=", ")