Converting rows into columns and columns into rows using R

Question

I have a dataframe with unique row names and unique column names. I want to convert the rows into columns and column into rows.

For example, this code:

starting_df <- data.frame(row.names= c(LETTERS[1:4]),
                          a = c(1:4),
                          b = seq(0.02,0.08,by=0.02),
                          c = c("Aaaa","Bbbb","Cccc","Dddd")
                )

results in the following:

> starting_df
  a    b    c
A 1 0.02 Aaaa
B 2 0.04 Bbbb
C 3 0.06 Cccc
D 4 0.08 Dddd

I want to convert it into another data frame containing exactly the same data, except that what were formerly rows were now columns and vice versa:

> final_df
     A    B    C    D
a    1    2    3    4
b 0.02 0.04 0.06 0.08
c Aaaa Bbbb Cccc Dddd

Absorption

I could have done this in another language, but I was sure that there was a quick way to do it in R. I felt very silly when I realized what the answer was, but thought that this would be useful for anyone trying to figure it out. By the way, I searched Stackoverflow with an [r] tag for many variations on the following: Convert rows into columns and vice versa; Transform columns into rows and rows into columns; Rotate dataframe; Swap rows and columns; – Absorption

Answer 1

Simply use the base transpose function t, wrapped with as.data.frame:

final_df <- as.data.frame(t(starting_df))
final_df
     A    B    C    D
a    1    2    3    4
b 0.02 0.04 0.06 0.08
c Aaaa Bbbb Cccc Dddd

Above updated. As docendo discimus pointed out, t returns a matrix. As Mark suggested wrapping it with as.data.frame gets back a data frame instead of a matrix. Thanks!

Absorption

By transposing the data.frame, you have "secretly" converted it to class matrix: class(final_df) # [1] "matrix" – Dermatoglyphics

wrap a as.data.frame() around it to make sure it's a data.frame – Dinge

@Mark, conversion to matrix may have some side effects if you have columns of different class since matrices in R can only have columns of the same class. – Dermatoglyphics

@docendodiscimus Great point - I usually wait for that problem to bite me in the but. Though, in this case, all your rows would have to be the same class to have transposition make sense at all. – Dinge

Answer 2

Here is a tidyverse option that might work depending on the data, and some caveats on its usage:

library(tidyverse)

starting_df %>% 
  rownames_to_column() %>% 
  gather(variable, value, -rowname) %>% 
  spread(rowname, value)

rownames_to_column() is necessary if the original dataframe has meaningful row names, otherwise the new column names in the new transposed dataframe will be integers corresponding to the orignal row number. If there are no meaningful row names you can skip rownames_to_column() and replace rowname with the name of the first column in the dataframe, assuming those values are unique and meaningful. Using the tidyr::smiths sample data would be:

smiths %>% 
    gather(variable, value, -subject) %>% 
    spread(subject, value)

Using the example starting_df with the tidyverse approach will throw a warning message about dropping attributes. This is related to converting columns with different attribute types into a single character column. The smiths data will not give that warning because all columns except for subject are doubles.

The earlier answer using as.data.frame(t()) will convert everything to a factor if there are mixed column types unless stringsAsFactors = FALSE is added, whereas the tidyverse option converts everything to a character by default if there are mixed column types.

Avertin