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

[David Winsemius]

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

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
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.