[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_NOWavelength r1
ANWC_NOWavelength,r2
ANWC_NOWavelength 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
1300 0.003126 0.005382 0.001094 0.012529 0.005632 39239
2302 0.004924 0.006280 0.002366 0.015234 0.006204 39239
3304 0.004769 0.005960 0.002759 0.015856 0.006804 39239
4306 0.005181 0.006717 0.004033 0.017380 0.007675 39239
5308 0.005872 0.008083 0.004429 0.018334 0.008504 39239
6310 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
__
R-help@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.
Re: [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_NOWavelength r1
ANWC_NOWavelength,r2
ANWC_NOWavelength 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
1300 0.003126 0.005382 0.001094 0.012529 0.005632 39239
2302 0.004924 0.006280 0.002366 0.015234 0.006204 39239
3304 0.004769 0.005960 0.002759 0.015856 0.006804 39239
4306 0.005181 0.006717 0.004033 0.017380 0.007675 39239
5308 0.005872 0.008083 0.004429 0.018334 0.008504 39239
6310 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@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.
__
R-help@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.
Re: [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_NOWavelength r1
ANWC_NOWavelength,r2
ANWC_NOWavelength 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
1300 0.003126 0.005382 0.001094 0.012529 0.005632 39239
2302 0.004924 0.006280 0.002366 0.015234 0.006204 39239
3304 0.004769 0.005960 0.002759 0.015856 0.006804 39239
4306 0.005181 0.006717 0.004033 0.017380 0.007675 39239
5308 0.005872 0.008083 0.004429 0.018334 0.008504 39239
6310 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
__
R-help@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.
