Search code examples
rreshapereshape2

R wide to long reshape with column names

I have data in this format

A1 A2 B1 B2  C1  C2
10  5 11  5  21  10

And I want to convert it to:

  1  2
A 10 5
B 11 5
C 21 10

How can I do it in R?

Solution

  • We can gather into 'long' format, then separate the 'key' column into two by splitting before the numeric part, spread it to 'wide' and change the 'key1' column to row names

    library(tidyverse)
gather(df1) %>%
    separate(key, into = c('key1', 'key2'), sep="(?=\\d)") %>% 
    spread(key2, value) %>% 
    column_to_rownames('key1')
#  1  2
#A 10  5
#B 11  5
#C 21 10

    data

    df1 <- structure(list(A1 = 10L, A2 = 5L, B1 = 11L, B2 = 5L, C1 = 21L, 
     C2 = 10L), class = "data.frame", row.names = c(NA, -1L))