33

Using basic R, I can transpose a dataframe, say mtcars, which has all columns of the same class:

as.data.frame(t(mtcars))

Or with pipes:

library(magrittr)
mtcars %>% t %>% as.data.frame

How to accomplish the same within tidyr or tidyverse packages?

My attempt below gives:

Error: Duplicate identifiers for rows

library(tidyverse)
mtcars %>% gather(var, value, everything()) %>% spread(var, value)
user438383
  • 5,716
  • 8
  • 28
  • 43
Irakli
  • 959
  • 1
  • 11
  • 18
  • 4
    If you want to transpose it it should be a matrix and not a data.frame. What's wrong with using `t`? – Roland Oct 28 '16 at 13:39
  • 4
    Try `add_rownames(mtcars) %>% gather(var, value, -rowname) %>% spread(rowname, value)` – akrun Oct 28 '16 at 13:40
  • 2
    Why do you want to do this? You cannot meaningfully transpose a data.frame that contains many variable classes without loss of information. – Thomas Oct 28 '16 at 14:17
  • 2
    A data frame, such as mtcars, might have all columns of the same class. – Irakli Oct 28 '16 at 19:48

2 Answers2

78

Try with add_rownames

add_rownames(mtcars) %>% 
         gather(var, value, -rowname) %>% 
         spread(rowname, value)

In the newer version, rownames_to_column replaces add_rownames

mtcars %>%
   rownames_to_column %>% 
   gather(var, value, -rowname) %>% 
   spread(rowname, value)

In the even newer version, pivot_wider replaces spread:

mtcars %>%
   tibble::rownames_to_column() %>%  
   pivot_longer(-rowname) %>% 
   pivot_wider(names_from=rowname, values_from=value)
jthurner
  • 190
  • 1
  • 7
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thanks @akrun. Looks like add_rownames is depreciated, but this works: mtcars %>% rownames_to_column %>% gather(var, value, -rowname) %>% spread(rowname, value) – Irakli Oct 28 '16 at 19:46
  • Rows in the transformed data are by default ordered (alphabetically the mtcars case). If you want to order the rows according to the initial column order (i.e. `names(mtcars)`) an additional step is required: `mtcars.t[order(match(mtcars.t$var,names(mtcars))),]`, where mtcars.t is the transformed data. – fabern Sep 08 '17 at 12:26
  • just after i hit submit i realized that `gather()` is now `pivot_longer()` :/ – keithpjolley Mar 09 '20 at 18:24
  • 7
    Puuh, that's pretty code intense as compared to ```t()```. Seems like something on the to do list for the next dplyr version. – MsGISRocker Aug 24 '21 at 19:50
8

There's now a purpose-built function to do this, rotate_df() from sjmisc.

library(sjmisc)
mtcars %>% rotate_df()

#     Mazda RX4 Mazda RX4 Wag Datsun 710 Hornet 4 Drive Hornet Sportabout Valiant Duster 360
#mpg      21.00        21.000      22.80         21.400             18.70   18.10      14.30
#cyl       6.00         6.000       4.00          6.000              8.00    6.00       8.00
#disp    160.00       160.000     108.00        258.000            360.00  225.00     360.00
#hp      110.00       110.000      93.00        110.000            175.00  105.00     245.00
#drat      3.90         3.900       3.85          3.080              3.15    2.76       3.21

#etc

The function also allows you to convert rownames to real df data with the rotate. Many thanks to the package creators.

Joe
  • 8,073
  • 1
  • 52
  • 58