Chapter 12 Wide versus Narrow Data

12.1 Data verbs for converting wide to narrow and vice versa

Transforming from wide to narrow is the action of a data verb: a wide data frame is the input and a narrow data frame is the output. The reverse task, transforming from narrow to wide, involves another data verb. [Click to see note.]Different authors use different names for these verbs: e.g., melting versus casting, stacking versus unstacking, folding versus unfolding, gathering versus spreading. We primarily use pivot wider and pivot longer implemented by pivot_wider( ) and pivot_longer( ) in the tidyr package. [Click to see note.]tidyr is a close relation to dplyr, the main data wrangling package used in this book. Both were created by Hadley Wickham and feature prominently among the tidyverse packages. [Click to see note.]A prior iteration of the tidyr package utilized spread() and gather() functions to accomplish the purposes of pivot_wider() and pivot_longer(), respectively.

Pivoting wider

The pivot_wider( ) function converts from a relatively narrow form to wider form. Carrying out this operation involves specifying some information in the arguments to the function. The mechanics of this transformation are dictated by specifying a pair of arguments which define the names to be associated with new variables to be established for the wider form, as well as the corresponding values to be populated for each case.

For instance, in the narrow form BP_narrow (Table 12.2) the values found in the sbp variable will be spread between two variables–before and after–in the corresponding wide form, BP_wide (Table 12.1). Note that the different categorical levels in when (e.g., before, and after) associated with each case in BP_narrow determines the destination for that sbp value in BP_wide.

Notice that each case had been defined by a visit for a subject (e.g., BHO + before) in the BP_narrow form, and now each case is defined only by a subject in the BP_wide form of the data. Each form simply re-organizes the same spb information in a different way with different meanings to a case.

Translating BP_narrow to BP_wide can be done like this:

BP_narrow %>%
  pivot_wider(names_from = when, values_from = sbp)

Pivot longer

Now consider how to transform BP_wide into BP_narrow. The names of the variables to be gathered together, before and after, will become the categorical levels in the narrow form. That is, all systolic blood pressure values will be gathered into a single variable sbp and we will use the accompanying before or after information to specify when the blood pressure reading for that subject was recorded.

The name when is suggested, but in fact the data analyst has to invent a name for this variable. There are all sorts of sensible possibilities, for instance before_or_after would have been just as effective.

Similarly, a name must be invented for the variable that is to hold the values in the variables being gathered. Again, there are many reasonable possibilities. It’s sensible to choose a name that reflects the kind of thing those value are, in this case systolic blood pressure. So, sbp was a good choice.

Finally, the analyst needs to specify which are the variables to be gathered (or not). For instance, it hardly makes sense to gather subject with the other variables; it will remain as a separate variable in the narrow result. Values in subject will be repeated as necessary to give each case in the narrow format its own correct value of subject.

Here’s how to gather variables from BP_wide and convert the result into BP_narrow:

BP_wide %>%  
  pivot_longer(cols = c(before, after), names_to = "when", values_to = "sbp")

Again, notice the distinction in the function arguments between new variable names invented by the data analyst (e.g., "when" and "sbp") to be used in the resulting narrow form in contrast to the original variable names specified from the wide form by the argument cols = c(before, after).

12.2 Exercises

Problem 12.1: The BabyNames data table (in the dcData package) looks like this:

The same data can be presented as a wide-format table:

An R statement to convert from narrow to wide format can have the following form:

BothSexes <-
  BabyNames %>%
  pivot_wider(names_from = ??? , values_from = count) %>%
  filter(M > 5 | F > 5)

1. Which variable should replace ??? in the R statement?

Now that you have BothSexes …

2. Find the 10 names with the closest balance between females and males. You can define gender-balance quantitatively as abs(log(F / M)). The smaller this number, the more balanced the name count.
3. Find the 10 names with the closest balance that have more than 100 babies of either sex.
4. Challenge. Find the 10 names with the closest balance that have lasting popularity. Define lasting popularity as having more than 100 babies in at least 20 of the years.
5. Challenge. Are there names that have switched gender balance over the years. Find, for each year, the gender ratio. Pull out ones where the maximum and minumum over the years differ by a large amount. Then plot out over time.

Problem 12.2: Consider the following forms for organizing a subset of the BabyNames data as we examine the most popular name of either sex each year.

Table 12.8: Version One

Table 12.9: Version Two

Table 12.10: Version Three

1. What is the meaning of a case in each of the tables?

   • Version One
   • Version Two
   • Version Three

2. What should the chain of R commands look like to make “Version Two” from the data table “Version One” shown above? Also, explain which of the two versions is “wider.”

3. What should the chain of R commands look like to make “Version One” from the data table “Version Three” shown above? Also, explain which of the two versions is “wider.”

4. Suppose you want to calculate the ratio of male to female in each name in each year. Is it better to start from “Version Two” or “Version Three?” Why?

5. If you were given “Version One,” should you immediately begin using data verbs to calculate the ratio of male to female, or should you first translate to “Version Two” or “Version Three?” Explain.

Problem 12.3: Here are three data frames, A, B, and C, with different organizations of the same data:

Data Frame A

Data Frame B

Data Frame C

1. Comparing each of the following pairs of tables, is one considered “wider” as described in the chapter? If so, say which is wider; if not, explain.

   • A versus C
   • B versus C
   • A versus B

2. Which table format do you think would make it easiest to find the change from 2000 to 2001 for each country. How would you do it?

3. Suppose you have another table, ContinentData, which gives the continent that each country is in. Which table format do you think would make it easiest to find the sum of the values for each continent for each of the years? How would you do it?

Problem 12.4: Consider the data table BP_wide shown here:

Table 12.11: BP_wide

Think about the result of the following conversion from wide to narrow.

BP_wide %>%
  pivot_longer(cols = c(subject, before, after), 
               names_to = "when", values_to = "sbp")

1. Explain why the resulting data frame might be considered “too narrow” meaning that we have converted it to a narrow form that is now less useful for most practical purposes.

2. Show how the code should be modified in order to convert BP_wide to a more useful narrow form. Recall that “narrow” as defined in the chapter may not necessarily result in fewer columns.