How to Transpose (t) in the Tidyverse Using Tidyr
library(tidyr)
library(dplyr)
Df <- Df %>% group_by(Code1, Code2, Level) %>%
summarise_all(funs(count = sum(!is.na(.)))) %>%
gather(var, val, 2:ncol(Df)) %>%
spread(Code1, val)
The general idiom in the tidyverse is to gather()
your data to the maximal extent, forming a "long" data frame with one measurement per row. Then, spread()
can revert this long data frame into whichever "wide" format that you like best. This procedure can effectively transpose the data: just gather()
all the identifier columns except the row names, and then spread()
the row names.
For example, here is how to effectively transpose mtcars
:
require(tidyverse)
mtcars %>%
rownames_to_column %>%
gather(variable, value, -rowname) %>%
spread(rowname, value)
Your data does not have "row names" as understood in R, but Code1
effectively serves as a row name because it uniquely identifies each (original) row of your data.
Df1 <- Df %>%
group_by(Code1, Code2, Level) %>%
summarise_all(funs(count = sum(!is.na(.)))) %>%
gather(column, value, -Code1) %>%
spread(Code1, value)
UPDATE for tidyr 1.0 or higher (late 2019 onwards)
The new pivot_wider()
and pivot_longer()
functions are now preferred over the older (but still supported) gather()
and spread()
. Thus the preferred way to transpose mtcars is probably
require(tidyverse)
mtcars %>%
rownames_to_column() %>%
pivot_longer(-rowname, 'variable', 'value') %>%
pivot_wider(variable, rowname)