From this topic, students are anticipated to be able to:
recognize whether a given data set is ‘tidy’ or ‘untidy’ for their analysis
understand why ‘tidy’ data can be useful
reshape a data set between ‘long’ and ‘wide’ formats, using tidyr::pivot_longer() and tidyr::pivot_wider()
understand how to grapple with explicit missing values created by pivoting
Tidy Data and the Tidyverse
In the last two weeks, we learned about the dplyr package for data manipulation and the ggplot2 package for graphing. These two packages are part of the “tidyverse”: a collection of data science packages that are designed to have input data frames and output data frames that are tidy. In fact, we can load all packages in the tidyverse at once with the single command library(tidyverse).
Here, we are using the word “tidy” in a technical sense - we’re not talking about how “neat” or “organized” your data is. Instead, “tidy” is a very specific set of rules for storing data.
For example, the following data set containing cat and dog names by family is not tidy. We have multiple observations per row (a cat, and a dog observation):
Family
Cat Name
Dog Name
Tompkins
Sumo
Mochi
Truong
Mr. Meow
Bowser
Maclean
Goose
Instead, a tidy version of this data set may look like this:
Pet Name
Type
Family
Sumo
Cat
Tompkins
Mochi
Dog
Tompkins
Mr. Meow
Cat
Truong
Bowser
Dog
Truong
Goose
Cat
Maclean
Each row is an observation, each variable is a column, and each cell is a single measurement. Our data is tidy!
All of the data we used before this week were already tidy. This made it easy to use the tidyverse packages dplyr and ggplot2 to do what we needed to do. Oftentimes however, data is not collected in a tidy way. So, what happens do we do when we have untidy data? Let’s explore it!
Example: Drinks
The fivethirtyeight R package contains a dataset called drinks. This dataset was compiled as part of a FiveThirtyEight article that explored (among other things) which countries consumes the most alcohol. Let’s look at a subset of the data:
# install.packages("fivethirtyeight") #uncomment if not installedlibrary(fivethirtyeight)library(tibble)library(tidyverse)drinks_tbl1 <-as_tibble(drinks) %>%select(-total_litres_of_pure_alcohol) #remove total liters variablehead(drinks_tbl1) #view the first few rows of the data
The following graphic was made from the drinks dataset.
Exercise 1
With a partner or a small group discuss the following questions think about if the data is tidy or untidy. If tidy, what would the ggplot code look like to reproduce this graph? If untidy, what would the tidy format look like? Sketch the first few rows of the data. Now, what would the ggplot code look like to reproduce this graph?
Example: Bake-off
It’s clear from the definition that tidiness is an attribute of a dataset. But did you know that tidiness also depends on what you are planning to do with the data? That’s because what’s an observation and what’s a variable depends on the data analysis plan!
We will demonstrate using data from “The Great British Bake Off” compiled by Allison Hill in the R package bakeoff. The graphics that follow (and the code to produce the graphics) were lightly adapted from Allison’s Plot Twist talk.
First, let’s decide on some questions we can address with this data.
How did viewership change as new series came out?
The show moved channels after Series 7. Was viewership higher, lower, or about the same before and after the move?
These questions have implicitly defined our observations: they are individual units of the most granular populations we are trying to describe or compare. Here, the populations to be compared are series, and units within them are episodes. The variables now fall into place: they are measured attributes of our observations (episodes): episode number, viewership, series membership, etc. This means that the following representation of viewership data is tidy for the “change in viewership over series” analysis:
# install.packages("bakeoff") #uncomment if not yet installedlibrary(bakeoff)library(tidyverse)ratings_tbl1 <- ratings %>%#save output to new tibble called ratings_tbl1mutate(ep_id =row_number()) %>%# create variable for episode numberselect(ep_id, viewers_7day, series, episode) #select specific columnshead(ratings_tbl1) #view first few rows of tibble
Every row is an observation (a unique episode), and the columns are variables (episode number across series, 7-day viewership, series, and episode number within series).
This is a typical example where the tidy format makes it easy to do our analysis. For example, to investigate these questions, we might make a bar plot of the number of viewers in millions within a 7-day window per episode, coloured by series. The following code uses the tidy tibble ratings_tbl1 to make this bar plot. Notice that it was easy to use our graphing environment of choice (ggplot2 in the tidyverse) to make the plot because our data is tidy, and the tidyverse is designed to work with tidy data.
series_labels <- ratings_tbl1 %>%#save output to series_labelsmutate(series=as.factor(series)) %>%#ensure series variable is a factor (categorical variable)group_by(series) %>%#group by seriessummarize(y_position =median(viewers_7day) +1, # calculate positions for the bar chartsx_position =mean(ep_id)) ## make the plotratings_tbl1 %>%mutate(series=as.factor(series)) %>%# ensure series is a factor variableggplot(aes(x = ep_id, y = viewers_7day, fill = series)) +#set x and y axes, tell ggplot that we want things coloured by seriesgeom_col(alpha = .9) +#tell ggplot we want a boxplot, change translucency with alphaggtitle("7-Day Viewership across Series 1-10") +#add a titlegeom_text(data = series_labels, aes(label = series, #add text for series numbersx = x_position, y = y_position)) +theme_classic() +#add a classic themescale_fill_manual(values =bakeoff_palette(),guide ="none") +#set the colours so they aren't rainbowxlab("Episode Number") +#add x axis labelylab("7-Day Viewership (millions)") #add y axis label
Now let’s consider a different set of questions:
How did viewership grow between premiere to final episode in each series?
Does the premiere-to-final-episode growth vary across series?
To investigate these questions, we might make a bar plot like the one below displaying percentage increase in the number of viewers in millions within a 7-day window from the premiere episode to finale episode for the first 10 series, using the tidy tibble ratings_tbl2:
head(ratings_tbl2)
# A tibble: 6 × 3
series first last
<fct> <dbl> <dbl>
1 1 2.24 2.75
2 2 3.1 5.06
3 3 3.85 6.74
4 4 6.6 9.45
5 5 8.51 13.5
6 6 11.6 15.0
First, we can calculate the percentage change in viewership using mutate():
ratings_tbl2 <- ratings_tbl2 %>%#overwrite original df so new variable savesmutate(pct_change = (last - first)/first) #calculate percent changehead(ratings_tbl2) #view first few rows of tibble
ratings_tbl2 %>%ggplot(aes(x =fct_rev(series), y=pct_change)) +#initialize the plotgeom_col(fill = bakeoff::bakeoff_colors("baltic"), alpha = .5) +#set bar chart with fill colours, semi-translucent labs(x ="Series", y ="% Increase in Viewers, First to Last Episode") +#add x labelsggtitle("% Increase in Viewers from Premiere to Finale") +#add y labelsscale_y_continuous(labels = scales::percent) +#change y axis to percentage theme_classic() +#add classic themecoord_flip() #flip horizontally
Exercise 2
With a partner or a small group:
What do you think ratings_tbl2 looks like?
Why is it tidy? (Hint: what are the observations and variables?)
Could you have calculated the information in ratings_tbl2 using ratings_tbl1? (No need to write code - just discuss whether it’s possible.)
Pivoting
Once you have figured out what’s tidy for you, you may come to realize that your data is not tidy. As we have discussed, it will typically save you time and frustration to tidy it before moving on in your analysis.
Very often this will involve using “pivoting” type functions. For example, the tidyr package in the tidyverse has two main pivoting functions:
pivot_longer() makes datasets longer: it moves some information in the columns into new rows, thereby increasing the number of rows of the dataset.
pivot_wider() makes datasets wider: it moves some information in the rows into new columns, thereby decreasing the number of rows of the dataset.
By now, you should have a sense for why this might be useful for tidying!
Pivoting Wider
Here is some code to create a variable for whether an episode is the first or last episode of the season to ratings_tbl1 and subset to only the data from the first and last episodes of each season.
ratings_tbl1 <- ratings_tbl1 %>%#overwrite ratings_tbl1group_by(series) %>%#group by seriesfilter(episode ==1| episode ==max(episode)) %>%#get only the first and last episodesungroup() %>%#ungroup the datamutate(episode_fl =recode(episode, `1`="first", .default ="last")) #add a new variable indicatign whether or not the episode was first or last, and recode the variable to "first" or "last"head(ratings_tbl1)
# A tibble: 6 × 5
ep_id viewers_7day series episode episode_fl
<int> <dbl> <dbl> <dbl> <chr>
1 1 2.24 1 1 first
2 6 2.75 1 6 last
3 7 3.1 2 1 first
4 14 5.06 2 8 last
5 15 3.85 3 1 first
6 24 6.74 3 10 last
This is not the same format as ratings_tbl2, which was the tidy format for our earlier “viewership growth within series” analysis. But it does contain the same information. To finish converting ratings_tbl1 into ratings_tbl2, we need to make ratings_tbl1wider: we need to move some information in the rows (the info about whether each episode is the first or last episode of each season) into new columns.
We can solve this problem using pivot_wider, which needs three pieces of information.
What is a set of columns that uniquely identifies each observation? Put their names in the id_cols argument.
Where should the names for the new columns come from? Put the name of the column you want to take the new variable names from in the names_from argument.
What values should the new columns contain? Put the name of the columns you want to take the values from to values_from in the values_from argument.
Note that if you don’t specify an id_cols argument, pivot_wider will assume that you want it to be every column except those in names_from and values_from.
ratings_tbl2 <- ratings_tbl1 %>%#overwrite ratings_tbl2pivot_wider(id_cols = series, #pivot with id as seriesnames_from=episode_fl, #get column names from episode_flvalues_from=viewers_7day) #fill in values form viewers_7dayhead(ratings_tbl2)
# A tibble: 6 × 3
series first last
<dbl> <dbl> <dbl>
1 1 2.24 2.75
2 2 3.1 5.06
3 3 3.85 6.74
4 4 6.6 9.45
5 5 8.51 13.5
6 6 11.6 15.0
Also note that any columns not included in id_cols, names_from, and values_from (e.g. ep_id) will simply be dropped.
If we wanted to keep the info in ep_id as well, we would add it to the values_from argument:
ratings_tbl1 %>%pivot_wider(id_cols = series, names_from=episode_fl, values_from=c(viewers_7day, ep_id)) #now including ep_id in the values_from call to include it in the output
Here is a snippet of WHO data on the number of tuberculosis cases in different years in different countries.
# A tibble: 3 × 3
country `1999` `2000`
<chr> <dbl> <dbl>
1 Afghanistan 745 2666
2 Brazil 37737 80488
3 China 212258 213766
If we wanted to compare tuberculosis cases over time by country (e.g. by plotting the year on the x-axis and case count on the y-axis with a line for each country), then this format is not tidy. We want to (graphically) compare years within countries, so there should be one observation per unit within each population (country-years). In this case, we do not observe units within each country-year, so each observation is a country-year. The variables then fall into place: the country and year labels, and the case counts.
(Aside: if we had measured more data, then perhaps there would be more units within each population! Imagine if we had case-level information, like severity. Then we could view cases as observations within the country-year populations, and we would have variables like country, year, case ID, and severity.)
So the tidy format here puts the variables (the year, the country, and the case counts) on the columns. There are 6 rows, one for each unique country-year combination. In this example, the tidy format is longer. That means to produce it using table4a, we need to lengthen it by moving some information in the column names (the info about the measurement year) into new rows.
We can solve this problem using pivot_longer, which needs three pieces of information.
Which are the columns that we want to expand into more rows? Put their names in the cols argument.
We want to save the information in the names of those columns as values in new column(s) of our dataset. What should we name these new column(s)? This is the names_to argument.
We also want to preserve the information in the values of those columns - so we should save them as values in a new column of our dataset. What should we name it? This is the values_to argument.
table4a %>%pivot_longer(cols =c(`1999`, `2000`), #pivot these columnsnames_to ="year", #new column name is yearvalues_to ="cases") #use cases as the values of the column
# A tibble: 6 × 3
country year cases
<chr> <chr> <dbl>
1 Afghanistan 1999 745
2 Afghanistan 2000 2666
3 Brazil 1999 37737
4 Brazil 2000 80488
5 China 1999 212258
6 China 2000 213766
This time, cases are broken down by gender (f/m) and by age range (014\1524\2534\3544\4554\5564\65).
Suppose now that we are interested in comparing tuberculosis rates over time across (potentially) gender, age, and country. Then the most granular population we are trying to describe is a country, gender, age, and year combination, and like in the last example, we have no measured sub-units within that population, so an observation is a unique combination of country, gender, age, and year. (What a mouthful!)
Once we’ve sorted that out, the variables fall into place: country, year, gender, age range, and case count. Values for gender and age range are currently located in the column names of who_demo, and values for case count are currently spread across multiple columns. So to tidy who_demo up, we need to use pivot_longer() to move the info in the columns into new rows.
Conceptually, this is pretty similar to the last example: we want to use the information in m_014, m_1524, etc. to create new rows. So we should put those column names into the cols argument. But now, we want the information in their column names - the gender and age - to go into two new columns: gender and age. We can do this by specifying two column names in the names_to argument: gender and age.
But how is pivot_longer() to know which part of the column name m_014 corresponds to the gender, and which part corresponds to the age? You need to tell it that the pieces of information are separated by the “_” character using the names_sep argument.
Finally, we can specify the name of the new column we want the values in the m_014, m_1524, etc. columns to go into with the values_to argument.
who_demo %>%pivot_longer(cols =!(country:year), # all columns aside from country to yearnames_to =c("gender", "age"), #new columns named age and gendernames_sep ="_",#current gender and age are a single variable separated by _values_to ="cases") #use the cases column for the values
# A tibble: 84 × 5
country year gender age cases
<chr> <dbl> <chr> <chr> <dbl>
1 Afghanistan 1999 m 014 8
2 Afghanistan 1999 m 1524 55
3 Afghanistan 1999 m 2534 55
4 Afghanistan 1999 m 3544 47
5 Afghanistan 1999 m 4554 34
6 Afghanistan 1999 m 5564 21
7 Afghanistan 1999 m 65 8
8 Afghanistan 1999 f 014 25
9 Afghanistan 1999 f 1524 139
10 Afghanistan 1999 f 2534 160
# ℹ 74 more rows
Example: Column Names Contain Variable Names And Values
So far we have seen examples where the column names contain variable values. But what if they contain names AND values?
Let’s have a look at the household dataset (loaded with the tidyr package), which has the date of birth and names of two children in families. Let’s say that we wanted to investigate how children names relate to their date of birth.
head(household)
# A tibble: 5 × 5
family dob_child1 dob_child2 name_child1 name_child2
<int> <date> <date> <chr> <chr>
1 1 1998-11-26 2000-01-29 Susan Jose
2 2 1996-06-22 NA Mark <NA>
3 3 2002-07-11 2004-04-05 Sam Seth
4 4 2004-10-10 2009-08-27 Craig Khai
5 5 2000-12-05 2005-02-28 Parker Gracie
We are trying to learn about the population from which these children belong; it is hard to say precisely what that is without having more information about how this data was collected, but it is likely something like “all children living in a particular place in a particular year”. The units in this population are children. So to tidy this data, we’d want “date of birth” and “name” to be two variables/columns associated with an observation/row (a child). We know we want to use pivot_longer(), because we want to make household longer by creating new variables. But wait! The names of the “date of birth”/“name” variables AND the values of the “child” variable are BOTH in the column names of household!
Inspecting the documentation for pivot_longer() very carefully reveals that you can use a special specification of the names_to argument to resolve this problem.
household %>%pivot_longer(cols =-family, # all columns except familynames_to =c(".value", "child"), #change column names, .value is a placeholdernames_sep ="_") # dob and child are currently separated by _ in one single variable
# A tibble: 10 × 4
family child dob name
<int> <chr> <date> <chr>
1 1 child1 1998-11-26 Susan
2 1 child2 2000-01-29 Jose
3 2 child1 1996-06-22 Mark
4 2 child2 NA <NA>
5 3 child1 2002-07-11 Sam
6 3 child2 2004-04-05 Seth
7 4 child1 2004-10-10 Craig
8 4 child2 2009-08-27 Khai
9 5 child1 2000-12-05 Parker
10 5 child2 2005-02-28 Gracie
The special ".value" specification says that we want to use the first component of the pivoted column name as a variable name, and make a new column with values coming from the second component of the pivoted column name. The second thing we pass into names_to names that new column.
But wait! Row 4 is a bunch of NAs! Does that mean this data isn’t tidy??
The fact that there is an NA does not necessarily mean that this data is untidy. To be clear: for the purpose of the tidy data definition, an indicator for a missing value is a value.
Whether this data is untidy depends on the data context. Essentially, the question we should ask is: “Is row 4 an observation that we are missing information about? Or is it simply an artifact of our pivoting procedure?”
Suppose this study was designed to only sample families with two children. Then, row 4 could be a real observation that we are missing information about: family 2 should have only been included if they had two children. Perhaps this reflects family 2 filling out a survey that asks them the number of children (in which they listed 2), but then getting distracted and forgetting to fill out the information for their second child. In this case, our data is tidy, and the tidy data format is a real advantage: it reveals missing information in our data set that was not obvious from the original untidy format.
Now suppose this study just samples families at large. We know from experience about the world that some families have one children, some families have two, and some families have more. Then, it seems possible that row 4 is not a real observation: family 2 might just have a single child. In this case, we have a row for something that is not an observation, so we would like to tidy up by dropping it. We could actually have done this by altering our original pivot_wider() call as follows:
# A tibble: 9 × 4
family child dob name
<int> <chr> <date> <chr>
1 1 child1 1998-11-26 Susan
2 1 child2 2000-01-29 Jose
3 2 child1 1996-06-22 Mark
4 3 child1 2002-07-11 Sam
5 3 child2 2004-04-05 Seth
6 4 child1 2004-10-10 Craig
7 4 child2 2009-08-27 Khai
8 5 child1 2000-12-05 Parker
9 5 child2 2005-02-28 Gracie
This discussion highlights the importance of knowing the context in which your data is collected for tidying (and for your analysis at large). Here and elsewhere, it really pays to be in close contact with the people who generated your data.
Separating and Uniting
The tidyr package has a function for gluing columns together (unite) and for cutting columns apart (separate). Why might this help us tidy? Here is another snippet of WHO Tuberculosis data.
# A tibble: 6 × 3
country year rate
<chr> <dbl> <chr>
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
The rate column contains the values of two variables: case counts and population counts. We would like to snip it apart at the “/” character to create two columns:
table5 <- table3 %>%separate(col = rate, into =c("cases", "population"))table5
# A tibble: 6 × 4
country year cases population
<chr> <dbl> <chr> <chr>
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
The col argument specifies the column we want to separate, and the into argument specifies the names of the new columns. The sep argument (not specified here) specifies where we want to cut. The default is pretty clever - it separates at any non-alphanumeric value. (How this is accomplished involves regular expressions, which are very useful when working with character data. We will learn more about regular expressions in STAT 545B. )
The Merits of Untidy Data
As we’ve seen, tidy data is often very helpful. But there are also times when untidy data is good. Here are a few reasons:
The format that lends itself best to fast computations might not be tidy. Case Study: Tidy Genomics.
We could lose important information about the context in which the data was collected by cleaning and tidying raw data. This can have important ethical implications; see Chapter 5 of the book “Data Feminism” by Catherine D’Ignazio and Lauren F. Klein.
In summary, tidiness is a very useful concept, and tidying data is often useful. But we should remember that absolutes are few and far between in data science and statistics. Just because tidying data is often useful, doesn’t mean it’s always useful.
Worksheet A4
Spend the rest of this class and next class working through Worksheet A4.
Finished attempting all of the questions? Then do the optional R4DS Tidying reading, and maybe even do some of the exercises for extra practice.
Albert Y. Kim inspired the in-class exercises using the drinks data set from fivethirtyeight. Allison Horst and Julia Lowndes created the illustrated tidy data series. Alison Hill inspired the Great British Bakeoff example. We are immensely grateful to these people for creating amazing educational materials!
We would also like to thank Samantha Tyner for pointing us towards the Data Feminism book during her week as the curator of the @WomenInStat Twitter account.