R: reshape dataframe with duplicated variable names labeled var.1, var.2

Question

I'm hoping to reshape a dataframe in R so that a set of columns read in with duplicated names, and then renamed as var, var.1, var.2, anothervar, anothervar.1, anothervar.2 etc. can be treated as independent observations. I would like the number appended to the variable name to be used as the observation so that I can melt my data.

For example,

dat <- data.frame(ID=1:3, var=c("A", "A", "B"),
                  anothervar=c(5,6,7),var.1=c(C,D,E),
                  anothervar.1 = c(1,2,3))

> dat
  ID var anothervar var.1 anothervar.1
1  1   A          5     C            1
2  2   A          6     D            2
3  3   B          7     E            3

How can I reshape the data so it looks like the following:

ID   obs   var   anothervar
1      1    A      5   
1      2    C      1  
2      1    A      6  
2      2    D      2    
3      1    B      7  
3      2    E      3

Thank you for your help!

score 0 · Accepted Answer · answered Jun 25 '18 at 22:54

We can use melt from data.table that takes multiple patterns in the measure

library(data.table)
melt(setDT(dat), measure = patterns("^var", "anothervar"), 
      variable.name = "obs", value.name = c("var", "anothervar"))[order(ID)]
#    ID obs var anothervar
#1:  1   1   A          5
#2:  1   2   C          1
#3:  2   1   A          6
#4:  2   2   D          2
#5:  3   1   B          7
#6:  3   2   E          3

score 0 · Answer 2 · answered Jun 25 '18 at 23:06

As for a tidyverse solution, we can use unite with gather

dat %>%
    unite("1", var, anothervar) %>%
    unite("2", var.1, anothervar.1) %>%
    gather(obs, value, -ID) %>%
    separate(value, into = c("var", "anothervar"))
#  ID obs var anothervar
#1  1   1   A          5
#2  2   1   A          6
#3  3   1   B          7
#4  1   2   C          1
#5  2   2   D          2
#6  3   2   E          3

R: reshape dataframe with duplicated variable names labeled var.1, var.2

2 Answers2