tidyr
1.0.2

Introduction

Data tables come in different sizes and shape; they can be a very simple two column dataset or they can consist of many columns and “sub-columns”. Understanding its structure, and learning how to reshape it into a workable form is critical to an effective and error free analysis.

For example, a median earnings data table downloaded from the U.S. census bureau’s website might look something like this:

We are conditioned to think of a table as consisting of three components: rows, columns and data values. Implicit in this paradigm is that each column represents a unique attribute. However, this may not always be the case. For example, in the above table, each column represents two distinct variables: gender and educational attainment (two distinct sets of attributes).

Another way of describing a dataset is by defining its variable(s), values and observations. In the above example, we have four variables: gender, education, region and income. Each variable consists of either categorical values (e.g. region, gender and education) or numerical values (income).

An observation consists of a unique set of attribute values. For example the values West Region, Female, Graduate and $57,914 make up one observation: there is just one instance of these combined values in the data. This perspective affords us another option in presenting the dataset: we can assign each column its own variable, and each row its own observation.

Note that each row of the table is part of a unique set of variable attributes. A dataset in this format may not be human “readable” (unlike its wide counterpart), but is the format of choice for many data analysis and visualization operations.

The next sections will demonstrate how one can convert a wide format to a long format and vice versa.

Wide and long table formats

A 2014 Boston (Logan airport) flight data summary table will be used in this exercise. The summary displays average mean delay time (in minutes) by day of the work week and quarter.

Reshaping a table involves modifying its layout (or “shape”). In our example, df is in a “wide” format.

  Weekday   Q1   Q2   Q3  Q4
1     Mon  9.9  5.4  8.8 6.9
2    Tues  4.9  9.7  7.9 5.0
3     Wed  8.8 11.1 10.2 9.3
4   Thurs 12.2 10.2  9.2 9.7
5     Fri 12.2  8.1  7.9 5.6

There are three unique variables: day of week, quarter of year, and mean departure delay.

Creating a long table from a wide table

A package that facilitates converting from wide to long (and vice versa) is tidyr. To go from wide to long we use the pivot_longer function. Note that if you are using a version of tidyr older than 1.0 you will want to use the gather() function..

The pivot_longer function takes three arguments:

  • cols: list of columns that are to be collapsed. The columns can be referenced by column number or column name.
  • names_to: This is the name of the new column which will combine all column names (e.g. Q1, Q2, Q3 and Q4).
  • values_to: This is the name of the new column which will combine all column values (e.g. average delay times) associated with each variable combination.

In our example, the line of code needed to re-express the table into a long form can be written in one of three ways:

All three lines produce the same output, they differ only by how we are referencing the columns that are to be collapsed. Note that we assigned the names Quarter and Delay to the two new columns.

The first 10 lines of the output table are shown here. Note how each Delay value has its own row.

   Weekday Quarter Delay
1      Mon      Q1   9.9
2     Tues      Q1   4.9
3      Wed      Q1   8.8
4    Thurs      Q1  12.2
5      Fri      Q1  12.2
6      Mon      Q2   5.4
7     Tues      Q2   9.7
8      Wed      Q2  11.1
9    Thurs      Q2  10.2
10     Fri      Q2   8.1

The following figure summarizes the wide to long conversion.

Creating a wide table from a long table

If a table is to be used for a visual assessment of the values, a long format may be difficult to work with. A long table can be re-expressed into a wide form by picking the two variables that will define the new column names and values.

Continuing with our example, we will convert df.long back to a wide format using the pivot_wider() function. This replaces the spread() function from earlier versions of tidyr (<1.0). The pivot_wider() function takes at least two arguments:

  • names_from: Variable whose values will be converted to column names.
  • values_from: Variable whose values will populate the table’s block of cell values.

We’ve now recreated the wide version of our table.

# A tibble: 5 x 5
  Weekday    Q1    Q2    Q3    Q4
  <fct>   <dbl> <dbl> <dbl> <dbl>
1 Mon       9.9   5.4   8.8   6.9
2 Tues      4.9   9.7   7.9   5  
3 Wed       8.8  11.1  10.2   9.3
4 Thurs    12.2  10.2   9.2   9.7
5 Fri      12.2   8.1   7.9   5.6

The following figure summarizes the long to wide conversion.

Advanced pivot_longer options

Here’s a subset of median income by sex and by work experience for 2017.

          state male_fulltime male_other female_fulltime female_other
1         Maine         50329      18099           40054        13781
2 Massachusetts         66066      18574           53841        14981
3 New Hampshire         59962      20274           46178        15121
4       Vermont         50530      17709           42198        14422

At first glance, it might seem that we have three variables as in the earlier example, but upon closer examination, we see that we can tease out two variables from the column names: sex (male and female) and work experience (fulltime and other).

pivot_longer has an argument, names_sep, that is passed the character that is used to delimit the two variable values. In our example, this character is _. Since the column values will be split across two variables we will also need to pass two column names to the names_to argument.

# A tibble: 16 x 4
   state         sex    work     income
   <fct>         <chr>  <chr>     <dbl>
 1 Maine         male   fulltime  50329
 2 Maine         male   other     18099
 3 Maine         female fulltime  40054
 4 Maine         female other     13781
 5 Massachusetts male   fulltime  66066
 6 Massachusetts male   other     18574
 7 Massachusetts female fulltime  53841
 8 Massachusetts female other     14981
 9 New Hampshire male   fulltime  59962
10 New Hampshire male   other     20274
11 New Hampshire female fulltime  46178
12 New Hampshire female other     15121
13 Vermont       male   fulltime  50530
14 Vermont       male   other     17709
15 Vermont       female fulltime  42198
16 Vermont       female other     14422

Advanced pivot_wider options

Combining variable names when spreading

Continuing with the df2.long dataframe, we can spread the long table back to a wide table while combining the sex and work variables. We’ll add the names_sep argument which defines the character to use to separate the two variable names. We’ll use a dot . separator in this example.

# A tibble: 4 x 5
  state         male.fulltime male.other female.fulltime female.other
  <fct>                 <dbl>      <dbl>           <dbl>        <dbl>
1 Maine                 50329      18099           40054        13781
2 Massachusetts         66066      18574           53841        14981
3 New Hampshire         59962      20274           46178        15121
4 Vermont               50530      17709           42198        14422

Spreading duplicate variable combinations

If your long table has more than one unique combination of variables, pivot_wider() will return a list. This differs from the spread()’s behavior which would have returned an error.

  var1 var2 val
1    a    x   5
2    a    x   3
3    b    y   1
4    b    y   4
# A tibble: 2 x 3
  var1  x         y        
  <fct> <list>    <list>   
1 a     <dbl [2]> <NULL>   
2 b     <NULL>    <dbl [2]>

Since the intersections of a:x and b:y each have two possible values, the function returns a list of values. Assuming that the duplicate records are not an erroneous entry, you will need to instruct the function on how to summarize the multiple values using the values_fn argument. For example, to return the maximum value, type:

# A tibble: 2 x 3
  var1      x     y
  <fct> <dbl> <dbl>
1 a         3    NA
2 b        NA     1

You’ll note the empty cells resulting from there not being a valid combination for a:y and b:x. You can specify the missing values using the values_fill argument. For example, to replace NA with 0 type:

# A tibble: 2 x 3
  var1      x     y
  <fct> <dbl> <dbl>
1 a         3     0
2 b         0     1

Separating elements in one column into separate columns

The tidyr package offers other functions not directly tied to pivoting. For example, to split a column into two or more columns based on a column’s delimited value, use the separate() function.

# A tibble: 16 x 3
   state         var1            income
   <fct>         <chr>            <dbl>
 1 Maine         male_fulltime    50329
 2 Maine         male_other       18099
 3 Maine         female_fulltime  40054
 4 Maine         female_other     13781
 5 Massachusetts male_fulltime    66066
 6 Massachusetts male_other       18574
 7 Massachusetts female_fulltime  53841
 8 Massachusetts female_other     14981
 9 New Hampshire male_fulltime    59962
10 New Hampshire male_other       20274
11 New Hampshire female_fulltime  46178
12 New Hampshire female_other     15121
13 Vermont       male_fulltime    50530
14 Vermont       male_other       17709
15 Vermont       female_fulltime  42198
16 Vermont       female_other     14422
# A tibble: 16 x 4
   state         sex    work     income
   <fct>         <chr>  <chr>     <dbl>
 1 Maine         male   fulltime  50329
 2 Maine         male   other     18099
 3 Maine         female fulltime  40054
 4 Maine         female other     13781
 5 Massachusetts male   fulltime  66066
 6 Massachusetts male   other     18574
 7 Massachusetts female fulltime  53841
 8 Massachusetts female other     14981
 9 New Hampshire male   fulltime  59962
10 New Hampshire male   other     20274
11 New Hampshire female fulltime  46178
12 New Hampshire female other     15121
13 Vermont       male   fulltime  50530
14 Vermont       male   other     17709
15 Vermont       female fulltime  42198
16 Vermont       female other     14422

Splitting rows into multiple rows based on delimited values

You can also split delimited values across rows.

# A tibble: 32 x 3
   state         var1     income
   <fct>         <chr>     <dbl>
 1 Maine         male      50329
 2 Maine         fulltime  50329
 3 Maine         male      18099
 4 Maine         other     18099
 5 Maine         female    40054
 6 Maine         fulltime  40054
 7 Maine         female    13781
 8 Maine         other     13781
 9 Massachusetts male      66066
10 Massachusetts fulltime  66066
# ... with 22 more rows

Note that the output is a tibble even if the input is a dataframe.

Replicate rows by count

You can expand rows based on a count column using the uncount() function. This is the opposite of a group_by(...) %>% count() operation that tallies up the observations based on a grouping variable. Here, we’ll replicate rows based on the column count value.

  var1 var2 count
1    a    x     1
2    b    y     3
    var1 var2
1      a    x
2      b    y
2.1    b    y
2.2    b    y

If you want to add an index column that identifies the replicated rows, add an .id argument.

    var1 var2 id
1      a    x  1
2      b    y  1
2.1    b    y  2
2.2    b    y  3

Combining elements from many columns into a single column

Another practical function in the tidyr package is unite(). It combines columns into a single column by chaining the contents of the combined columns. For example, the following table has hours, minutes and seconds in separate columns.

  Index Hour Min Sec
1     1    2  34  55
2     2   14   2  17
3     3   20  55  23

To combine the three time elements into a single column, type:

  Index     Time
1     1  2:34:55
2     2  14:2:17
3     3 20:55:23

The col parameter defines the new column name; the paremeter 2:4 tells unite that columns two through four are to be combined into column Time; sep=":" tells the function what characters are to be used to separate the elements (here, we are separating the time elements using :); remove=TRUE tells the function to remove columns two through four.

Creating unique combinations of variable values

You can use expand_grid to automatically generate a table with unique combinations of a set of variable values. For example, to fill a table with a combination of student names and homework assignments, type:

# A tibble: 12 x 3
   student assignment value
   <chr>   <chr>      <lgl>
 1 Joe     HW1        NA   
 2 Joe     HW2        NA   
 3 Joe     HW3        NA   
 4 Joe     HW4        NA   
 5 Jane    HW1        NA   
 6 Jane    HW2        NA   
 7 Jane    HW3        NA   
 8 Jane    HW4        NA   
 9 Kim     HW1        NA   
10 Kim     HW2        NA   
11 Kim     HW3        NA   
12 Kim     HW4        NA   

We can then create a wide version of the table using pivot_wider.

# A tibble: 3 x 5
  student HW1   HW2   HW3   HW4  
  <chr>   <lgl> <lgl> <lgl> <lgl>
1 Joe     NA    NA    NA    NA   
2 Jane    NA    NA    NA    NA   
3 Kim     NA    NA    NA    NA   

Filling gaps in a table with NA

It’s not uncommon to be handed a table with incomplete combinations of observations. For example, the following table gives us yield and data source values for each combination of year and grain type. However, several combinations of year/grain are missing.

  Year Grain Yield Src
1 1999  Oats    23   a
2 1999  Corn    45   a
3 2000  Oats    24   b
4 2000  Corn    40   c
5 2001  Oats    20   a
6 2003  Oats    19   a
7 2003  Corn    41   c
8 2005  Oats    22   a

We are missing records for 2001 and Corn, 2003 and Corn, and data for both grains are missing for 2002 and 2004. To add rows for all missing pairs of year/grain values, use the complete function. Here, we’ll assign 0 to missing Yield values and NA to the Src values.

# A tibble: 14 x 4
    Year Grain Yield Src  
   <dbl> <chr> <dbl> <chr>
 1  1999 Corn     45 a    
 2  1999 Oats     23 a    
 3  2000 Corn     40 c    
 4  2000 Oats     24 b    
 5  2001 Corn      0 <NA> 
 6  2001 Oats     20 a    
 7  2002 Corn      0 <NA> 
 8  2002 Oats      0 <NA> 
 9  2003 Corn     41 c    
10  2003 Oats     19 a    
11  2004 Corn      0 <NA> 
12  2004 Oats      0 <NA> 
13  2005 Corn      0 <NA> 
14  2005 Oats     22 a    

If you want to show just the missing rows, use dplyr::anti_join().

# A tibble: 6 x 4
   Year Grain Yield Src  
  <dbl> <chr> <dbl> <chr>
1  2001 Corn      0 <NA> 
2  2002 Corn      0 <NA> 
3  2002 Oats      0 <NA> 
4  2004 Corn      0 <NA> 
5  2004 Oats      0 <NA> 
6  2005 Corn      0 <NA> 

Identifying missing combination in dataframes

In the previous example, we had the function automatically add the missing combinations using explicitly defined ranges of values. If you just want to output the missing combinations from the existing set of values in both columns, use the expand() function.

# A tibble: 10 x 2
    Year Grain
   <dbl> <chr>
 1  1999 Corn 
 2  1999 Oats 
 3  2000 Corn 
 4  2000 Oats 
 5  2001 Corn 
 6  2001 Oats 
 7  2003 Corn 
 8  2003 Oats 
 9  2005 Corn 
10  2005 Oats 

Note that this only outputs the columns of interest. If you need to see the other columns in the output, perform a join.

# A tibble: 10 x 4
    Year Grain Yield Src  
   <dbl> <chr> <dbl> <chr>
 1  1999 Corn     45 a    
 2  1999 Oats     23 a    
 3  2000 Corn     40 c    
 4  2000 Oats     24 b    
 5  2001 Corn     NA <NA> 
 6  2001 Oats     20 a    
 7  2003 Corn     41 c    
 8  2003 Oats     19 a    
 9  2005 Corn     NA <NA> 
10  2005 Oats     22 a    

Auto-fill down or up

The fill() function is used to replace NA values with the closest non-NA value in a column. For example, to fill down, set the .direction argument to "down".

   Month Year
1      1 2000
2      2   NA
3      3   NA
4      4   NA
5      5   NA
6      6 2001
7      7   NA
8      8   NA
9      9   NA
10    10   NA
11    11   NA
12    12   NA
   Month Year
1      1 2000
2      2 2000
3      3 2000
4      4 2000
5      5 2000
6      6 2001
7      7 2001
8      8 2001
9      9 2001
10    10 2001
11    11 2001
12    12 2001