[R] Reshape or Stack? (To produce output as columns)

[Steve Murray]

Dear all,

Many thanks for the suggestions put forward. I've decided to go with the 'melt' command from the 'reshape' library, as this seems to run the quickest.

I do have a couple of questions however, regarding the use of the 'melt' command. Below are the last few lines of the 'melted' data. The first column shows the latitude values (the row names of the former data frame), the second column are the longitude values (the column names of the former data frame) and the third column shows the actual values (-99.9 is correct in this case).

85.25719    179.75 -99.9
85.75719    179.75 -99.9
86.25719    179.75 -99.9
86.75719    179.75 -99.9
87.25719    179.75 -99.9
87.75719    179.75 -99.9
88.25719    179.75 -99.9
88.75719    179.75 -99.9
89.25719    179.75 -99.9
89.75719    179.75 -99.9


As you can see, each value in the 'latitude' column is followed by '719'. As far as I can tell, this value seems to represent the number of times the value is repeated in this column (I could be wrong though). Remember that this is the end of a fairly sizeable dataset - these 'surplus' figures range from '1' further up the column to '719' as shown here. How do I go about removing these values?

Also, I hope to add headings to these columns ("Latitude", "Longitude", "Value"). The best I've been able to manage so far is by using the 'names' command, however this only allows me to add headings to the second and third columns. Below is what happens when I've tried to add headings to all three:

> names(melted) <- c("Latitude", "Longitude", "Value")
Error in "names<-.default"(`*tmp*`, value = c("Latitude", "Longitude",  : 
        'names' attribute [3] must be the same length as the vector [2]

If anyone is able to offer any advice and suggestions as to how I might overcome these issues, then I'd be very grateful.

Many thanks again,

Steve




----------------------------------------
> Date: Tue, 17 Jun 2008 09:09:06 -0500
> From: h.wickham at gmail.com
> To: smurray444 at hotmail.com
> Subject: Re: [R] Reshape or Stack? (To produce output as columns)
> CC: r-help at r-project.org
> 
> On Tue, Jun 17, 2008 at 5:59 AM, Steve Murray  wrote:
>>
>> Dear all,
>>
>> I have used 'read.table' to create a data frame of 720 columns and 360 rows (and assigned this to 'Jan'). The row and column names are numeric:
>>
>>> columnnames <- sprintf("%.2f", seq(from = -179.75, to = 179.75, length = 720)).
>>> rnames <- sprintf("%.2f", seq(from = -89.75, to = 89.75, length = 360))
>>> colnames(Jan) <- columnnames
>>> rownames(Jan) <- rnames
>>
>> A sample of the data looks like this:
>>
>>> head(Jan)
>>       -179.75 -179.25 -178.75 -178.25 -177.75 -177.25 -176.75 -176.25 -175.75
>> -89.75   -56.9   -64.2    56.2   -90.0    56.9   -29.0   -91.0   34.0     -9.1
>> -89.25    37.9    19.3     -0.4   -12.3   -11.8   -92.1     9.2   -23.5     -0.2
>> -88.75    47.4      3.1   -47.4    46.4    34.2      6.1   -41.3    44.7   -10.3
>> -88.25   -20.3    34.5   -67.3   -99.9    37.9     -9.3    17.7   -17.2    63.4
>> -87.75   -46.4    47.4    12.4   -48.3      9.3   -33.8    38.1    10.8   -34.1
>> -87.25   -48.4    10.3   -89.3   -33.0     -1.1   -33.1    81.2    -8.3   -47.2
>>
>>
>> I'm hoping to get the whole dataset into the form of columns, so that, for example, the first row (as shown above) would look like this:
>>
>> Latitude   Longitude   Value
>> -89.75      -179.75     -56.9
>> -89.75      -179.25     -64.2
>> -89.75      -178.75      56.2
>> -89.75      -178.25     -90.0
>> -89.75      -177.75      56.9
>> -89.75      -177.25     -29.0
>> -89.75      -176.75     -91.0
>> -89.75      -176.25      34.0
>> -89.75      -175.75      -9.1
>>
>>
>> As you can see, this would require the repeated printing of the the row and column names (in this case '-89.75') - so it's not just a case of rearranging the data, but creating 'more' data too.
>>
>> I've tried to achieve this using 'reshape' and 'stack' (their help files and after looking through the mailing archives), but I'm obviously doing something wrong. For reshape, I'm getting errors relating to the commands I enter, and for stack, I can only produce two columns from my data (with the additional 3rd column being a row count). In any case, these two columns refer to the wrong values (it's producing output in the form of: row count number, Longitude, Value).
> 
> This is pretty easy with the reshape package:
> 
> install.packages("reshape")
> library(reshape)
> jan_long <- melt(Jan)
> 
> You can find out more at http://had.co.nz/reshape
> 
> Hadley
> 
> -- 
> http://had.co.nz/