Nevil Amos
2010-Aug-28 10:07 UTC
[R] how to un-crosstabulate data without changing numeric values to text?
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
> 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
David Winsemius
2010-Aug-28 15:31 UTC
[R] how to un-crosstabulate data without changing numeric values to text?
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 39239Try: 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.
David Winsemius
2010-Aug-28 18:32 UTC
[R] how to un-crosstabulate data without changing numeric values to text?
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 39239Here 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