Chapter 21 Reshaping data
As we have seen through the book, having data in tidy format is what makes the tidyverse flow. After the first step in the data analysis process, importing data, a common next step is to reshape the data into a form that facilitates the rest of the analysis. The tidyr package includes several functions that are useful for tidying data.
We will use the fertility wide format dataset described in Section 4.1 as an example in this section.
library(tidyverse)
library(dslabs)
path <- system.file("extdata", package="dslabs")
filename <- file.path(path, "fertility-two-countries-example.csv")
wide_data <- read_csv(filename)21.1 gather
One of the most used functions in the tidyr package is gather, which is useful for converting wide data into tidy data.
As with most tidyverse functions, the gather function’s first argument is the data frame that will be converted. Here we want to reshape the wide_data dataset so that each row represents a fertility observation, which implies we need three columns to store the year, country, and the observed value. In its current form, data from different years are in different columns with the year values stored in the column names. Through the second and third argument we will tell gather the column names we want to assign to the columns containing the current column names and observations, respectively. In this case a good choice for these two arguments would be year and fertility. Note that nowhere in the data file does it tell us this is fertility data. Instead, we deciphered this from the file name. Through the fourth argument we specify the columns containing observed values; these are the columns that will be gathered. The default is to gather all columns so, in most cases, we have to specify the columns. In our example we want columns 1960, 1961 up to 2015.
The code to gather the fertility data therefore looks like this:
We can also use the pipe like this:
We can see that the data have been converted to tidy format with columns year and fertility:
head(new_tidy_data)
#> # A tibble: 6 x 3
#> country year fertility
#> <chr> <chr> <dbl>
#> 1 Germany 1960 2.41
#> 2 South Korea 1960 6.16
#> 3 Germany 1961 2.44
#> 4 South Korea 1961 5.99
#> 5 Germany 1962 2.47
#> # … with 1 more rowand that each year resulted in two rows since we have two countries and this column was not gathered. A somewhat quicker way to write this code is to specify which column will not be gathered, rather than all the columns that will be gathered:
The new_tidy_data object looks like the original tidy_data we defined this way
data("gapminder")
tidy_data <- gapminder %>%
filter(country %in% c("South Korea", "Germany") & !is.na(fertility)) %>%
select(country, year, fertility)with just one minor difference. Can you spot it? Look at the data type of the year column:
The gather function assumes that column names are characters. So we need a bit more wrangling before we are ready to make a plot. We need to convert the year column to be numbers. The gather function includes the convert argument for this purpose:
new_tidy_data <- wide_data %>%
gather(year, fertility, -country, convert = TRUE)
class(new_tidy_data$year)
#> [1] "integer"Note that we could have also used the mutate and as.numeric.
Now that the data is tidy, we can use this relatively simple ggplot code:
21.2 spread
As we will see in later examples, it is sometimes useful for data wrangling purposes to convert tidy data into wide data. We often use this as an intermediate step in tidying up data. The spread function is basically the inverse of gather. The first argument is for the data, but since we are using the pipe, we don’t show it. The second argument tells spread which variable will be used as the column names. The third argument specifies which variable to use to fill out the cells:
new_wide_data <- new_tidy_data %>% spread(year, fertility)
select(new_wide_data, country, `1960`:`1967`)
#> # A tibble: 2 x 9
#> country `1960` `1961` `1962` `1963` `1964` `1965` `1966` `1967`
#> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 Germany 2.41 2.44 2.47 2.49 2.49 2.48 2.44 2.37
#> 2 South Korea 6.16 5.99 5.79 5.57 5.36 5.16 4.99 4.85The following diagram can help remind you how these two functions work:
(Image courtesy of RStudio77. CC-BY-4.0 license78. Cropped from original.)
21.3 separate
The data wrangling shown above was simple compared to what is usually required. In our example spreadsheet files, we include an illustration that is slightly more complicated. It contains two variables: life expectancy and fertility. However, the way it is stored is not tidy and, as we will explain, not optimal.
path <- system.file("extdata", package = "dslabs")
filename <- "life-expectancy-and-fertility-two-countries-example.csv"
filename <- file.path(path, filename)
raw_dat <- read_csv(filename)
select(raw_dat, 1:5)
#> # A tibble: 2 x 5
#> country `1960_fertility` `1960_life_expe… `1961_fertility`
#> <chr> <dbl> <dbl> <dbl>
#> 1 Germany 2.41 69.3 2.44
#> 2 South … 6.16 53.0 5.99
#> # … with 1 more variable: `1961_life_expectancy` <dbl>First, note that the data is in wide format. Second, notice that this table includes values for two variables, fertility and life expectancy, with the column name encoding which column represents which variable. Encoding information in the column names is not recommended but, unfortunately, it is quite common. We will put our wrangling skills to work to extract this information and store it in a tidy fashion.
We can start the data wrangling with the gather function, but we should no longer use the column name year for the new column since it also contains the variable type. We will call it key, the default, for now:
dat <- raw_dat %>% gather(key, value, -country)
head(dat)
#> # A tibble: 6 x 3
#> country key value
#> <chr> <chr> <dbl>
#> 1 Germany 1960_fertility 2.41
#> 2 South Korea 1960_fertility 6.16
#> 3 Germany 1960_life_expectancy 69.3
#> 4 South Korea 1960_life_expectancy 53.0
#> 5 Germany 1961_fertility 2.44
#> # … with 1 more rowThe result is not exactly what we refer to as tidy since each observation is associated with two, not one, rows. We want to have the values from the two variables, fertility and life expectancy, in two separate columns. The first challenge to achieve this is to separate the key column into the year and the variable type. Notice that the entries in this column separate the year from the variable name with an underscore:
dat$key[1:5]
#> [1] "1960_fertility" "1960_fertility" "1960_life_expectancy"
#> [4] "1960_life_expectancy" "1961_fertility"Encoding multiple variables in a column name is such a common problem that the readr package includes a function to separate these columns into two or more. Apart from the data, the separate function takes three arguments: the name of the column to be separated, the names to be used for the new columns, and the character that separates the variables. So, a first attempt at this is:
Because _ is the default separator assumed by separate, we do not have to include it in the code:
dat %>% separate(key, c("year", "variable_name"))
#> Warning: Expected 2 pieces. Additional pieces discarded in 112 rows [3,
#> 4, 7, 8, 11, 12, 15, 16, 19, 20, 23, 24, 27, 28, 31, 32, 35, 36, 39,
#> 40, ...].
#> # A tibble: 224 x 4
#> country year variable_name value
#> <chr> <chr> <chr> <dbl>
#> 1 Germany 1960 fertility 2.41
#> 2 South Korea 1960 fertility 6.16
#> 3 Germany 1960 life 69.3
#> 4 South Korea 1960 life 53.0
#> 5 Germany 1961 fertility 2.44
#> # … with 219 more rowsThe function does separate the values, but we run into a new problem. We receive the warning Too many values at 112 locations: and that the life_expectancy variable is truncated to life. This is because the _ is used to separate life and expectancy, not just year and variable name! We could add a third column to catch this and let the separate function know which column to fill in with missing values, NA, when there is no third value. Here we tell it to fill the column on the right:
var_names <- c("year", "first_variable_name", "second_variable_name")
dat %>% separate(key, var_names, fill = "right")
#> # A tibble: 224 x 5
#> country year first_variable_name second_variable_name value
#> <chr> <chr> <chr> <chr> <dbl>
#> 1 Germany 1960 fertility <NA> 2.41
#> 2 South Korea 1960 fertility <NA> 6.16
#> 3 Germany 1960 life expectancy 69.3
#> 4 South Korea 1960 life expectancy 53.0
#> 5 Germany 1961 fertility <NA> 2.44
#> # … with 219 more rowsHowever, if we read the separate help file, we find that a better approach is to merge the last two variables when there is an extra separation:
dat %>% separate(key, c("year", "variable_name"), extra = "merge")
#> # A tibble: 224 x 4
#> country year variable_name value
#> <chr> <chr> <chr> <dbl>
#> 1 Germany 1960 fertility 2.41
#> 2 South Korea 1960 fertility 6.16
#> 3 Germany 1960 life_expectancy 69.3
#> 4 South Korea 1960 life_expectancy 53.0
#> 5 Germany 1961 fertility 2.44
#> # … with 219 more rowsThis achieves the separation we wanted. However, we are not done yet. We need to create a column for each variable. As we learned, the spread function can do this:
dat %>%
separate(key, c("year", "variable_name"), extra = "merge") %>%
spread(variable_name, value)
#> # A tibble: 112 x 4
#> country year fertility life_expectancy
#> <chr> <chr> <dbl> <dbl>
#> 1 Germany 1960 2.41 69.3
#> 2 Germany 1961 2.44 69.8
#> 3 Germany 1962 2.47 70.0
#> 4 Germany 1963 2.49 70.1
#> 5 Germany 1964 2.49 70.7
#> # … with 107 more rowsThe data is now in tidy format with one row for each observation with three variables: year, fertility, and life expectancy.
21.4 unite
It is sometimes useful to do the inverse of separate, unite two columns into one. To demonstrate how to use unite, we show code that, although not the optimal approach, serves as an illustration. Suppose that we did not know about extra and used this command to separate:
dat %>%
separate(key, var_names, fill = "right")
#> # A tibble: 224 x 5
#> country year first_variable_name second_variable_name value
#> <chr> <chr> <chr> <chr> <dbl>
#> 1 Germany 1960 fertility <NA> 2.41
#> 2 South Korea 1960 fertility <NA> 6.16
#> 3 Germany 1960 life expectancy 69.3
#> 4 South Korea 1960 life expectancy 53.0
#> 5 Germany 1961 fertility <NA> 2.44
#> # … with 219 more rowsWe can achieve the same final result by uniting the second and third columns, then spreading the columns and renaming fertility_NA to fertility:
dat %>%
separate(key, var_names, fill = "right") %>%
unite(variable_name, first_variable_name, second_variable_name) %>%
spread(variable_name, value) %>%
rename(fertility = fertility_NA)
#> # A tibble: 112 x 4
#> country year fertility life_expectancy
#> <chr> <chr> <dbl> <dbl>
#> 1 Germany 1960 2.41 69.3
#> 2 Germany 1961 2.44 69.8
#> 3 Germany 1962 2.47 70.0
#> 4 Germany 1963 2.49 70.1
#> 5 Germany 1964 2.49 70.7
#> # … with 107 more rows21.5 Exercises
1. Run the following command to define the co2_wide object:
co2_wide <- data.frame(matrix(co2, ncol = 12, byrow = TRUE)) %>%
setNames(1:12) %>%
mutate(year = as.character(1959:1997))Use the gather function to wrangle this into a tidy dataset. Call the column with the CO2 measurements co2 and call the month column month. Call the resulting object co2_tidy.
2. Plot CO2 versus month with a different curve for each year using this code:
If the expected plot is not made, it is probably because co2_tidy$month is not numeric:
Rewrite the call to gather using an argument that assures the month column will be numeric. Then make the plot.
3. What do we learn from this plot?
- CO2 measures increase monotonically from 1959 to 1997.
- CO2 measures are higher in the summer and the yearly average increased from 1959 to 1997.
- CO2 measures appear constant and random variability explains the differences.
- CO2 measures do not have a seasonal trend.
4. Now load the admissions data set, which contains admission information for men and women across six majors and keep only the admitted percentage column:
If we think of an observation as a major, and that each observation has two variables (men admitted percentage and women admitted percentage) then this is not tidy. Use the spread function to wrangle into tidy shape: one row for each major.
5. Now we will try a more advanced wrangling challenge. We want to wrangle the admissions data so that for each major we have 4 observations: admitted_men, admitted_women, applicants_men and applicants_women. The trick we perform here is actually quite common: first gather to generate an intermediate data frame and then spread to obtain the tidy data we want. We will go step by step in this and the next two exercises.
Use the gather function to create a tmp data.frame with a column containing the type of observation admitted or applicants. Call the new columns key and value.
6. Now you have an object tmp with columns major, gender, key and value. Note that if you combine the key and gender, we get the column names we want: admitted_men, admitted_women, applicants_men and applicants_women. Use the function unite to create a new column called column_name.
7. Now use the spread function to generate the tidy data with four variables for each major.
8. Now use the pipe to write a line of code that turns admissions to the table produced in the previous exercise.