What is the best way to transpose a data.frame in R and to set one of the columns to be the header for the new transposed table?

Question

What is the best way to transpose a data.frame in R and to set one of the columns to be the header for the new transposed table? I have coded up a way to do this below. As I am still new to R. I would like suggestions to improve my code as well as alternatives that would be more R-like. My solution is also unfortunately a bit hard coded (i.e. the new column headings are in a certain location).

# Assume a data.frame called fooData
# Assume the column is the first column before transposing

# Transpose table
fooData.T <- t(fooData)

# Set the column headings
colnames(fooData.T) <- test[1,]

# Get rid of the column heading row
fooData.T <- fooData.T[2:nrow(fooData.T), ]

#fooData.T now contains a transposed table with the first column as headings

Hawsehole

possible duplicate of R-Transposing a data frame – Liable

With as.data.frame(t(df)), the row names and column names are transposed and the data remains numeric, where df is a dataframe. – Kostman

Answer 1

Well you could do it in 2 steps by using

# Transpose table YOU WANT
fooData.T <- t(fooData[,2:ncol(fooData)])

# Set the column headings from the first column in the original table
colnames(fooData.T) <- fooData[,1] 

The result being a matrix which you're probably aware of, that's due to class issues when transposing. I don't think there will be a single line way to do this given the lack of naming abilities in the transpose step.

Thomsen

Good point, this should make it faster as far as I can tell, as it is having to transpose less data. Plus looks neater. The matrix issue is one of the issues I ran into also and had to use a class(fooData.T) <- "numeric", which won't work for any other data types. – Hawsehole

I am making this the answer as there aren't any other alternatives, which is strange for programming as there is usually a multitude of ways to solve something. – Hawsehole

There is a problem with the second instruction, it should be colnames(fooData.T) <- t(fooData[,1]) – Gnarled

Answer 2

You can do it even in one line:

fooData.T <- setNames(data.frame(t(fooData[,-1])), fooData[,1])

There are already great answers. However, this answer might be useful for those who prefer brevity in code.

Cythera

Answer 3

Here are my two cents using dplyr for a data.frame that has grouping columns and an id column.

id_transpose <- function(df, id){
  df %>% 
    ungroup() %>% 
    select(where(is.numeric)) %>% 
    t() %>% 
    as_tibble() %>% 
    setNames(., df %>% pull({{id}}))
}

Friable

Answer 4

Here is another tiyderse/dplyr approach taken from here.

mtcars %>%
  tibble::rownames_to_column() %>%  
  tidyr::pivot_longer(-rowname) %>% 
  tidyr::pivot_wider(names_from=rowname, values_from=value)

Catawba

Answer 5

Use transpose from data.table, suppose the column you want to use as header after transpose is the variable group.

fooData.transpose = fooData %>% transpose (make.name = "group")

In addition, if you want to assign a name for the transposed column name, use argument keep.names.

fooData.transpose = fooData %>% transpose (make.name = "group", keep.names = "column_name")

Maroon

Answer 6

There's now a dedicated function to transpose data frames, rotate_df from the sjmisc package. If the desired names are in the first column of the original df, you can achieve this in one line thanks to the cn argument.

Here's an example data frame:

df <- data.frame(name = c("Mary", "John", "Louise"), class = c("A", "A", "B"), score = c(40, 75, 80))

df
#    name class score
#1   Mary     A    40
#2   John     A    75
#3 Louise     B    80

Executing the function with cn = T:

rotate_df(df, cn = T)

#      Mary John Louise
#class    A    A      B
#score   40   75     80

Hassler

Answer 7

I had a similar problem to this -- I had a variable of factors in a long format and I wanted each factor to be a new column heading; using "unstack" from the stats library did it in one step. If the column you want as a header isn't a factor, "cast" from the reshape library might work.

Unfortunate