Reshape data with separator using reshape2

Question

Im trying to use the reshape2 package to reshape my data but I'm getting an error. My data is as below:

mydata<-read.csv("Data.csv")
head(mydata)    
      Date  A.Price B.Price C.Price A.Rating B.Rating C.Rating
31/01/2012    1.273    3.11   1.215        5        4        3  
29/02/2012    1.393    3.19   1.205       10        8        7  
30/03/2012    1.367    3.15   1.076     10.5      9.5      7.5

My goal is to get it reshaped to this:

Date       ID  Price  Rating  
31/01/2012  A  1.273  5  
31/01/2012  B  3.11  4  
31/01/2012  C  1.215  3  
29/02/2012  A  1.393  10  
29/02/2012  B  3.19  8  
....

So far my code is:

mydata$ID <- sequence(nrow(mydata))  
out<-melt(reshape(mydata, direction = "long", 
  timevar = "Group", varying = names(mydata), sep = "."), 
       id.vars = c("ID", "Price","Rating"))

but im getting the error:

Error in reshapeLong(data, idvar = idvar, timevar = timevar, varying = varying, : 'varying' arguments must be the same length

Any ideas on how to solve this?

akrun · Accepted Answer · 2016-02-07T06:29:02.107

We can use melt from data.table which can take multiple measure columns

library(data.table)
dM <- melt(setDT(mydata), measure=patterns('Price', 'Rating'), 
    variable.name='ID', value.name=c('Price', 'Rating'))

By default, the 'variable'(i.e. 'ID') column output as numeric index. We can use sub to get the prefix from the column names and update the 'ID' column.

dM[, ID:= sub('\\..*', '', names(mydata)[-1])[ID]]
dM
#         Date ID Price Rating
#1: 31/01/2012  A 1.273    5.0
#2: 29/02/2012  A 1.393   10.0
#3: 30/03/2012  A 1.367   10.5
#4: 31/01/2012  B 3.110    4.0
#5: 29/02/2012  B 3.190    8.0
#6: 30/03/2012  B 3.150    9.5
#7: 31/01/2012  C 1.215    3.0
#8: 29/02/2012  C 1.205    7.0
#9: 30/03/2012  C 1.076    7.5

Or using reshape from base R and specify the varying as a list of column index. We can get the index with grep

nm1 <- unique(sub('.*\\.', '', names(mydata)[-1])) 
res <- reshape(mydata, direction='long', varying= lapply(nm1, 
           grep, names(mydata)))
row.names(res) <- NULL
head(res)
#        Date time A.Price A.Rating id
#1 31/01/2012    1   1.273      5.0  1
#2 29/02/2012    1   1.393     10.0  2
#3 30/03/2012    1   1.367     10.5  3
#4 31/01/2012    2   3.110      4.0  1
#5 29/02/2012    2   3.190      8.0  2
#6 30/03/2012    2   3.150      9.5  3

Or another option would be merged.stack from library(splitstackshape). If we need to convert to 'long' format based on the suffix part in the column names, get the suffix of column names with sub, and use the unique elements in var.stubs along with specifying the sep as 'var.stubsinmerged.stack`.

library(splitstackshape)
nm1 <- unique(sub('.*\\.', '', names(mydata)[-1]))#from above
merged.stack(mydata, var.stubs =nm1, atStart=FALSE, 
    sep='var.stubs')[, .time_1:= sub('[.]+', '', .time_1)][]
#         Date .time_1 Price Rating
#1: 29/02/2012       A 1.393   10.0
#2: 29/02/2012       B 3.190    8.0
#3: 29/02/2012       C 1.205    7.0
#4: 30/03/2012       A 1.367   10.5
#5: 30/03/2012       B 3.150    9.5
#6: 30/03/2012       C 1.076    7.5
#7: 31/01/2012       A 1.273    5.0
#8: 31/01/2012       B 3.110    4.0
#9: 31/01/2012       C 1.215    3.0

Thanks so much akrun! but is there a way to do it so that i can use the "." as a separator instead of typing out the names of the columns? — richard_jokes, Feb 07 '16 at 05:26
@richard_jokes As both the prefix and suffix are varying across, one way would be to specify the columns in `varying` as a `list` in your reshape. i.e. `reshape(mydata, direction='long', varying=list(2:4, 5:7))` — akrun, Feb 07 '16 at 05:36
I see i see. Just to confirm, the reshape option requires me to specify the number of columns with the varying "variable" (eg price/rating sorry dont know what the proper term is..) but the other options do it automatically right? — richard_jokes, Feb 07 '16 at 06:18
sorry bout that..a newbie here haha. thank you so much akrun :) — richard_jokes, Feb 07 '16 at 06:28

Reshape data with separator using reshape2

1 Answers1