How to reshape a matrix

Question

I have a matrix (d) that looks like:

d <- 
as.matrix(read.table(text = "
    month    Q1    Q2   Q3    Q4    Q5    Q6    Q7    Q8    Q9   Q10   Q11   Q12   Q13         
X10    10  7.04  8.07  9.4  8.17  9.39  8.13  9.43  9.06  8.59  9.37  9.79  8.47  8.86  
X11    11 12.10 11.50 12.6 13.70 11.90 11.50 13.10 17.20 19.00 14.60 13.70 13.20 16.10  
X12    12 24.00 22.00 22.2 20.50 21.60 22.50 23.10 23.30 30.50 34.10 36.10 37.40 28.90 
X1      1 18.30 16.30 16.2 14.80 16.60 15.40 15.20 14.80 16.70 14.90 15.00 13.80 15.90  
X2      2 16.70 14.40 15.3 14.10 15.50 16.70 15.20 16.10 18.00 26.30 28.00 31.10 34.20",
header=TRUE))

going from Q1 to Q31 (its the days in each month). what I would like to get is:

month day Q
10    1   7.04
10    2   8.07

and so on for the 31 days and the 12 months.

I have tried using the following code:

reshape(d, direction="long", varying = list(colnames(d)[2:32]), v.names="Q", idvar="month", timevar="day") 

but I get the error :

Error in d[, timevar] <- times[1L] : subscript out of bounds

Can anyone tell me what is wrong with the code? I don't really understand the help file on "reshape", it's a bit confusing... Thanks!

Answer 1

The help file is confusing as heck, not least because (as I've learned) the necessary information almost always actually is in there --- somewhere.

As a prime example, midway through the help file, there is this bit:

The function will attempt to guess the ‘v.names’ and ‘times’ from these names [i.e. the ones in the 'varying' argument]. The default is variable names like ‘x.1’, ‘x.2’, where ‘sep = "."’ specifies to split at the dot and drop it from the name. To have alphabetic followed by numeric times use ‘sep = ""’.

That last sentence is the one you need here: "Q1", "Q2", etc. are indeed "alphabetic followed by numeric", so you need to set sep = "" argument if reshape() is to know how to split apart those column names.

Try this:

res <- reshape(as.data.frame(d), idvar="month", timevar="day", 
               varying = -1, direction = "long", sep = "")

head(res[with(res, order(month,day)),])
#     month day    Q
# 1.1     1   1 18.3
# 1.2     1   2 16.3
# 1.3     1   3 16.2
# 1.4     1   4 14.8
# 1.5     1   5 16.6
# 1.6     1   6 15.4

Answer 2

Almost there - you're just missing as.data.frame(d) to make your matrix into a data frame. Also you don't need the list in varying - just a vector, so

reshape(as.data.frame(d), varying=colnames(d)[2:32], v.names="Q", 
        direction="long", idvar="month", timevar="day")

Answer 3

The help file on reshape is not a bit confusing. It's a LOT confusing. Assuming your matrix has 12 rows(1 for each month) and 31 columns (I'm guessing you have NA values months with fewer than 31), you could easily construct this by hand.

d <- data.frame(month = rep(d[,1], 31), day = rep(1:31, each = 12), Q = as.vector(d[,2:32])

Now, back to your reshape... I'm guessing it's not parsing the names of your columns correctly. It might work better with Q.1, Q.2, etc. BTW, my reshaping above really depends on what you presented actually being a matrix and not a data.frame.