rdataframedplyrtidyversetranspose

Transpose dataframe in R and maintain first column


I need to transpose a dataframe in R, making the first column the headers of the transposed df, and the current columns headers the values in column 1 of the df. Like this -

df = 

 | |S1|S2|S3|
1|A|1 |2 | 3|
2|B|1 |2 | 3|

df_transposed = 

 |  |A |B |
1|S1|1 |1 |
2|S2|2 |2 |
3|S3|3 |3 |

I'm able to get part of the way there with the following code from this example - Transposing a dataframe maintaining the first column as heading

# Sample data frame
df <- data.frame(
  ID = c(10, 20, 30),
  Name = c("Alice", "Bob", "Charlie"),
  Age = c(25, 30, 22)
)

# Transpose the data frame
df_transposed <-setNames(data.frame(t(df[,-1])), df[,1])

But every time it takes the column header values, in this case 'ID','Name','Age', and sets them as the index (or row.name in the r parlance). I've tried various solutions but nothing so far has worked. They're primarily geared towards the opposite - taking the first column and making it the index/row.names.


Solution

  • I think you're looking for this.

    > setNames(data.frame(names(df)[-1], t(unname(df[, -1]))), c('Row.names', df[, 1]))
      Row.names    10  20      30
    1      Name Alice Bob Charlie
    2       Age    25  30      22
    

    Please note, however, that column names beginning with a number are not considered syntactically valid, see ?make.names on this.

    To get automatically valid names we can exploit the data.frame which internally does make.names.

    > data.frame(`colnames<-`(cbind(names(df)[-1], t(unname(df[, -1]))), c('Row.names', df[, 1])))
      Row.names   X10 X20     X30
    1      Name Alice Bob Charlie
    2       Age    25  30      22
    

    Data:

    > dput(df)
    structure(list(ID = c(10, 20, 30), Name = c("Alice", "Bob", "Charlie"
    ), Age = c(25, 30, 22)), class = "data.frame", row.names = c(NA, 
    -3L))