tidyr Tutorial

This is a short tutorial outlining the syntax of the four basic data tidying functions of the tidyr package, namely:

• pivot_longer(),
• pivot_wider(),
• separate(), and
• unite().

1 Tidy data

Recall that tidy data is tabular data that is organised such that:

1. each observation has its own row,
2. each variable has its own column, and
3. each value has its own cell.

This is sometimes referred to ‘tall’ or ‘long’ form data because of its shape. By contrast you may have ‘wide’ data which, for example, has columns containing measurements of the same variable but taken at different time points.

Both wide or tidy are valid ways to store data but I’d argue that tidy data is easier to manipulate. This is the philosophy behind the omnibus of packages that constitutes the tidyverse bundle of R packages.

2 COVID-19 time-series

The example dataset used in this tutorial is curated by the Center for Systems Science and Engineering at Johns Hopkins University, Whiting School of Engineering.

It is time series data updated nightly with the count of COVID-19 cases globally, grouped by various geographical regions. We can read the .CSV directly into a data frame.

library(readr)

url_string <- "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"

covid19_cases <- read_csv(url(url_string))

In this tutorial we’ll just examine the counts for Australian cases only.

library(dplyr)

au_cases <- covid19_cases %>%
  filter(`Country/Region` == "Australia") %>%
  select(State = `Province/State`, ends_with("20")) %>%
  mutate(State = factor(State))
au_cases

Notice that the column headers (aside from the first) are dates in m/d/yy format. This is a clue that this data frame is in ‘wide’ format since the column names, rather than being generic variable names, are themselves encoded with data about the observations.

In other words, these headers are not just labels indicating which column contains dates, they themselves are the dates.

3 Pivoting Longer

Let’s reorganise this data frame into ‘long’ format using the pivot_longer() function in tidyr.

The cols = argument specifies which columns we want to pivot. Here we want to pivot all columns except the State column. This is achieved using the exclaimation prefix ! before the column name we want to exclude. The names_to = argument indicates the name of the new column for storing what were previously column names. The third argument values_to = is the name of another new column for storing the observation’s value.

library(tidyr)

au_cases_long <- au_cases %>%
  pivot_longer(cols = !State,
               names_to = "Date",
               values_to = "NumCases")
au_cases_long

Observe that each of the 8 rows of 108 elements in the wide data frame has been pivoted into 107 rows of 2 elements in the long data frame.

Also notice that the new Date column is assumed to beof type character.

4 Separating

In the Date column of the long data frame, observe that it comprises a tuple of integers corresponding to the month, day, and year of the observation. For the purposes of this tutorial let’s break out each of these date components into its own column. This is achieved using separate().

col = indicates which column we wish to split namely Date, into = is a vector of new column names ordered by how the components appear in the column being split, the separator character is given to sep =, and setting convert = to TRUE instructs R to automatically convert the new columns into an appropriate data type, e.g., integer.

au_cases_tidy <- au_cases_long %>%
  separate(col = Date, 
           into = c("Month", "Day", "Year"), 
           sep = "/",
           convert = TRUE)
au_cases_tidy

We now have each date component stored as integers in its own column, ready to be plotted.

For example, perhaps we want to plot the number of cases in Australia as observed on the first day of each month since records started. Having tidy data makes the filter()ing straightforward and ggplot()ing.

library(ggplot2)

au_cases_tidy %>%
  filter(Day == 1) %>%
  ggplot(aes(x = Month, y = NumCases, colour = State)) +
    geom_line(size = 2, alpha = 0.5) +
    ggtitle("Australian COVID-19 cases at month's start in 2020") +
    ylab("No. of cases") +
    guides(colour = guide_legend(title = "State/Territory"))

However, rather than expressing months as numerals it would make sense to store them as proper date objects.

5 Uniting

Having split the original Date column into components, we can reconstitute it back using the unite() function and follow it up by converting the dates to ISO format: YYYY-MM-DD.

The col = argument specifies the name of the newly reconstituted column, the next arguments are the column names being united together in their desired order, and the sep = indicates a separator character that should be inserted between components.

We then pipe %>% this into a mutate() command and use the dmy() function in the lubridate package to convert the character strings into true date objects.

library(lubridate)

au_cases_tidy_date <- au_cases_tidy %>%
  unite(col = "Date",
        Day, Month, Year, 
        sep = "-") %>%
  mutate(Date = dmy(Date))
au_cases_tidy_date

Now with proper date objects, the x-axis of our plots become more intuitive.

au_cases_tidy_date %>%
  ggplot(aes(x = Date, y = NumCases, colour = State)) +
    geom_line(size = 2, alpha = 0.5) +
    ggtitle(label = "Australian COVID-19 cases in April 2020", 
            subtitle = "Note logarithmic scale") +
    ylab("No. of cases") +
    guides(colour = guide_legend(title = "State/Territory")) +
    scale_y_log10()

6 Pivoting wider

Finally, we can revert to something similar to our original dataset by applying pivot_wider().

The id_cols = argument specifies which columns to include in the pivot (in this case all of them), names_from = indicates from which column to get the new column names, and values_from = tells R from which current column to fill in the new columns.

au_cases_wide <- au_cases_tidy_date %>%
  pivot_wider(id_cols = everything(),
              names_from = Date,
              values_from = NumCases)
au_cases_wide

This final data frame is similar to the first wide data frame except that the column names are now dates in ISO format.

7 One fell swoop

Using some of the other arguments to pivot_longer() we could in a single command perform the pivoting and separation of the Date without using separate().

The *_ptypes = arguments indicate what data types the resultant columns should be.

au_cases %>%
  pivot_longer(cols = !State,
               names_to = c("Month", "Day", "Year"),
               names_sep = "/",
               names_transform = list(Month = as.integer, Day = as.integer, Year = as.integer),
               values_to = "NumCases",
               values_transform = list(NumCases = as.integer)) %>%
  select(State, Day, Month, Year, NumCases)

au_cases

8 Duplicated entries and non-tidy data

To conform to the ‘tidy’ principles of data organisation we’ll sometimes end up introducing some duplication of values within a column, e.g., the State column in the above examples.

The original wide data frame had 8 \(\times\) 149 = 1192 entries compared with 1184 \(\times\) 5 = 5920 entries in the tidied data frame.

This is a potential downside for adopting tidy data formats and it reminds us that non-tidy data isn’t necessarily to be avoided as something inherently deficient. Data often comes to us organised in a particular structure because it makes most sense to a human reader or the data collection process.

The tidyr package is designed to assist with wrangling such data into a consistent representation that can leverage the vectorisation features in R more broadly.

9 Resources

• R for Data Science, Wickham & Grolemund.
• Introduction to R (tidyverse), WEHI.
• Dataframe Manipulation with tidyr, The Carpentries.