[R] how to un-crosstabulate data without changing numeric values to text?

[David Winsemius]

On Aug 28, 2010, at 11:31 AM, David Winsemius wrote:

>
> On Aug 28, 2010, at 6:07 AM, Nevil Amos wrote:
>
>> I have a large amount of data read in from over 140 excel files in  
>> the format of x.  r1 to r5 are repeat measures for a given  
>> Wavelength and ANWC_NO.
>>
>> I need to rearrange x into 3 columns, ANWC_NO,Wavelegth, value ie
>>
>> ANWC_NO    Wavelength     r1
>> ANWC_NO    Wavelength,    r2
>> ANWC_NO    Wavelength     r3
>>
>>
>> etc...
>>
>> I can rearrange the data using the code below, however all the  
>> columns end up as strings, not numeric values.  I cannot then  
>> summaries the data, ( whcih I need to do in bins of wavelanght for  
>> each ANWC_NO)
>>
>>
>> > x
>> Wavelength       r1       r2       r3       r4       r5 ANWC_NO
>> 1        300 0.003126 0.005382 0.001094 0.012529 0.005632   39239
>> 2        302 0.004924 0.006280 0.002366 0.015234 0.006204   39239
>> 3        304 0.004769 0.005960 0.002759 0.015856 0.006804   39239
>> 4        306 0.005181 0.006717 0.004033 0.017380 0.007675   39239
>> 5        308 0.005872 0.008083 0.004429 0.018334 0.008504   39239
>> 6        310 0.007164 0.010775 0.005949 0.019952 0.009594   39239

Here are two other ways (the first perhaps less explicit and relying  
on argument recycling in the data.frame function for repeating the  
values for the first and last columns):

 > data.frame(Wave=x$Wavelength,
              ANWC =x$ANWC_NO,
              values = unlist( x[ ,grep("^r",names(x))] ) )

And of course:

 > require(reshape)
 > melt(x, measure.vars=2:6)

The last one arguable the cleanest.

-- 
David.

>
> Try:
>
> reshape(x, idvar=c("Wavelength", "ANWC_NO"),
>           varying=2:6, v.names="r",
>           direction="long")
>
> I think that melt in package reshape would also work.
>
> -- 
> David
>
>> > y =NULL
>> > rows<-nrow(x)
>> > for(r in 1:rows){
>> + for(c in 2:6){
>> + row<-c(c(x[r,7]),as.numeric(c(x[r,1])),as.numeric(c(x[r,c])))
>> + y<-rbind(y,row)
>> + }}
>> > colnames(y)<-c("ANWC_NO","WAVELENGTH","VALUE")
>> > head (y)
>>  ANWC_NO WAVELENGTH VALUE
>> row "39239" "300"      "0.003126"
>> row "39239" "300"      "0.005382"
>> row "39239" "300"      "0.001094"
>> row "39239" "300"      "0.012529"
>> row "39239" "300"      "0.005632"
>> row "39239" "302"      "0.004924"
>>
>> > mean(y$VALUE)
>> Error in y$VALUE : $ operator is invalid for atomic vectors
>>
>> how do I get the data arranged in three columns but maintaining  
>> WavelENGTH and the values as numeric in a data.frame?
>> Many thanks
>>
>> Nevil Amos
>> Monash University