CODEWITHSUNDEEP
rreshapereshape2
Alex T
Alex T

Reputation: 3754

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?

Upvotes: 1

Views: 1476

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269371

The tags to the question are r, reshape and reshape2 so we show solutions using each of those.

1) xtabs A base R solution is the following. 

let <- gsub("\\d", "", names(DF))
num <- gsub("\\D", "", names(DF))
tab <- xtabs(unlist(DF) ~ let + num)

giving:

> tab
   num
let  1  2
  A 10  5
  B 11  5
  C 21 10

or for a data frame:

cbind(let = rownames(tab), as.data.frame.matrix(tab))

giving:

  let  1  2
A   A 10  5
B   B 11  5
C   C 21 10

2) reshape Another base R solution is the following. let and num are from above.

varying <- split(names(DF), num)
reshape(DF, dir = "long", varying = varying, v.names = names(varying),
  times = unique(let), timevar = "let")[-4]

giving:

    let  1  2
1.A   A 10  5
1.B   B 11  5
1.C   C 21 10

3) reshape2 Using let and num from above:

library(reshape2)

dcast(let ~ num, data = data.frame(value = unlist(DF)), value.var = "value")

giving:

  let  1  2
1   A 10  5
2   B 11  5
3   C 21 10

Note

The input in reproducible form:

Lines <- "
A1 A2 B1 B2  C1  C2
10  5 11  5  21  10"
DF <- read.table(text = Lines, header = TRUE)

Upvotes: 2

s_baldur
s_baldur

Reputation: 33498

A data.table solution:

library(data.table)
library(magrittr)
melt(df1, measure.vars = names(df1)) %>%
  .[, c("l", "n") := tstrsplit(variable, "")] %>%
  dcast(l ~ n)

   l  1  2
1: A 10  5
2: B 11  5
3: C 21 10

Upvotes: 1

akrun
akrun

Reputation: 886938

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))

Upvotes: 3

Related Questions