Pivoting Data With {tidyverse} and {data.table} in R
Pivoting data can be a little scary sometimes, even if you know exactly what you want to do. But it doesn’t need to be. I’m hoping that by the end of this post, you’ll feel like the pivot pro you always knew you could be.
Getting Started
As usual, the first thing we want to do is load our libraries. We’ll be using pivot_wider and pivot_longer from tidyverse as well as the pipe operator, %>%, from magrittr which is loaded through tidyverse. We’re also using dcast and melt from data.table as they are twins to pivot_wider and pivot_longer, respectively. While they are not identical, they perform the same functions and operate somewhat similarly.
Next, we want to create our dummy data. I’ve been working with theme park data a lot recently, so I modeled the dummy data after that. The data description is as follows:
Once created, the data looks like this:
What Are…
While I will show both pivot_wider/dcast and pivot_longer/melt in this post, I will default to the pivot_* term as it generally makes more sense on the surface.
`pivot_wider` and `dcast`
pivot_wider and dcast take data and reshape it so that there are more columns and fewer rows than the input data. It allows you to specify a column as a unique identifier and use the values in one or more columns as new column names. The last column is the values that you want to be placed in the proper intersection between the identifier column value and each new column value.
In the example above, I’ve pivoted the actualWait using the time and shortName columns. We can see that the first five rows of values in actualWait have become the first row of values in the wider data. The last row with The Amulet from Below at 8:05 becomes the value for amulet in the second row of the wider data where the time is 8:05.
While pivot_wider and dcast perform the same function, they behave a little bit differently. pivot_wider uses three main arguments id_cols, names_from, and values_from. dcast, on the other hand, uses a formula argument in place of both id_cols and names_from and value.vars in place of values_from. The second big difference is that column names pivot_wider don’t have to be in quotes. They can be, but they don’t have to be. On the other hand, only the formula dcast can go without quotes, and in that case, they must not have quotes while value.var must have quotes.
`pivot_longer` and `melt`
pivot_longer and melt take data and reshape it so that there are more rows and fewer columns than the input data. It allows you to specify columns that you want to be aggregated and the new column names for what were previously separate values columns.
In the example above, the estimatedWait and actualWait columns are consolidated into the waitType the column where each row specifies if the corresponding waitTime the column is an estimated or actual wait time.
Like the previous two, pivot_longer and melt have some differences, although these are smaller. pivot_longer uses three primary arguments: cols, names_to, and values_to which correspond to measure.vars, variable.name, and value.name, respectively, from melt. Again, the data.table version, melt, requires quotes around each argument value while pivot_longer only requires quotes around names_to and values_to because cols is acting as a select statement.
Checking for Duplicates
One of the most important things to keep in mind before using pivot_wider or dcast is knowing if you should have duplicate data or not. If you shouldn’t have any duplicates, you’ll want to double-check to make sure. To show you how to do this, I’ve added the first five rows of dt to the end of dt. If you were to try and pivot the resulting data.table, each value cell would become a list rather than a single value and would look something like this:
That’s okay if you’re expecting multiple values, you’ll just need to remember use use values_fn for pivot_wider or fun.aggregate for dcast to make sure they are being handled appropriately. A common use-case would be something similar to pivot tables in Excel where you want to count each item. In that case, your function argument would be count.
The basic format to check for duplicates is x[duplicated(x)]. However, duplicated() only returns TRUE for the second or more occurrence of an item in x. In order to check for all duplicates, we need to also check fromLast like this: x[duplicated(x) | duplicated(x, fromLast = TRUE)]. Once you have identified your duplicate rows, it is up to you what to do. In some cases, you might be okay using unique(x) to get only unique items, or you might want to discard all duplicated items altogether with x[!duplicated(x) & !duplicated(x, fromLast = TRUE)].
Pivot Wider
Wider with One Value Column
In this example, we want to know how long the actualWait is for each ride throughout the day with each recording time as a row, and each ride as a column.
In the pivot_wider, we want our new column names_from to be the shortName and our values_from actualWait. The tricky bit here is the id_cols argument. id_cols are columns whose values should uniquely identify each row in the data. We need to use it because the rideName and shortName columns are perfectly correlated, that is, for each row the value of both columns will always be the same. Any column that is not in the names_from or values_from the argument will be part of the default id_cols argument. We can use the argument in two ways; we can use a numeric vector to select the columns that we want to be used as identifiers, or we can specify columns that we do not want to be used as a character or column name vector. In the example below, I’ve included both methods. For the rest of the post, I’ll be using the second method.
As mentioned previously, the biggest difference for dcast is that it uses a formula instead of the id_cols and names_from arguments. The left-hand side of the formula is the columns that you want to stay as columns to be used as the unique identifier for each row. The right-hand side of the formula is the column whose values will be used as the new column names. Lastly, value.var defines the column with the desired values.
Wider with Two or More Value Columns
Wider data with more than one value column is very similar to one value column. The only changes you need to make in pivot_wider are changing your id_cols argument if using the column name method and adding the new column to the values_from argument. The only change needed in dcast is adding to the value.var argument. One important thing to note is that the column names will now be a combination of the names_from/right hand side argument and the values_from/value.var argument. The column name order in the value* argument determines the column order in the resulting data.
Wider with Two or More ID Columns
Again, this is pretty similar to a “normal” wider operation. With pivot_wider you expand your id_cols to include the new columns, or, in my case, drop the attendance column so that it is now included. With dcast, you simply add the new column to the left hand side of the formula.
Pivot Longer
pivot_longer and melt are very similar. The largest differences are the argument names and where they require argument values to be in quotes. cols/measure.vars are the columns that you want to be aggregated into a single new column. This argument must be in quotes for melt, but does not have to be for pivot_longer and should be the names of existing columns. names_to/values_to is the name of the column that will say which column was used for that row and values_to/value.name are the corresponding values. The name for each of these new columns is up to you. I have decided on waitType to describe if it is an estimatedWait or actualWait and waitTime to hold the value.
I’ve saved a copy of the longer data as dtLonger so that we can explore pivot_wider and dcast a bit more.
Pivot Wider (from Longer)
Wider with Two or More Name Columns
We want to take our new longer data and change it to wider data where we know how long the actualWait and estimatedWait are for each ride throughout the day with each recording time as a row, using the waitType and each ride’s shortName.
For pivot_wider, we want our column names to use both the waitType and shortName and our values from waitTime. id_cols is then the remaining columns that we don’t want. With dcast, we want to add waitType and shortName to the right hand side of the formula so they are used as the column names, and time to the left hand side for the row identifiers. For both, values_from and value.var are our waitTime column.
Like when using multiple values in value_from/value.var, the new column names are all combinations of values in names_from or the right hand side. The combinations will be given in the order the column names are specified.
Wider with Mutiple ID and Name Columns
If you’ve been following along so far, the code below should make sense. We want to use time and attendance as the row identifiers and the estimatedWait and actualWait as the values for each ride.
Wrapping Things Up
A Quick Benchmark
I decided to benchmark the methods inPivotWider.R and PivotLonger.R to see how they stack up. data.table methods are faster than tidyverse and wider to longer is faster than longer to wider. Of course, the actual speeds can vary a lot depending on data size. You might also want to consider how readable your code is and the standard set of libraries you’re using if you’re pivoting data at work.
Conclusion
I’m hoping that by now you’re more comfortable with pivot_wider/dcast and pivot_longer/melt than you were before. Generally, before you begin reshaping data, you want to know what your resulting data should look like and if you’ll have any duplicate rows. From there, decide if you want to use tidyverse or data.table and then write your pivots!
All the code for this article is available here. If you want to see more from me, check out my GitHub or guslipkin.github.io. If you want to hear from me, I’m also on Twitter at guslipkin.
Gus Lipkin is a Data Scientist, Business Analyst, and occasional bike mechanic