Hi,
Check the ?str() of testxls.? I guess the two columns "studya" and
"studyb", would be character().
I don't have package xlsx installed. So, I read the file using
library(XLConnect).? Had the same problem.? You can use ?setMissingValue in
XLConnect.
library(XLConnect)
library(reshape2)
?wb <- loadWorkbook("test.xlsx")
?testxls <- readWorksheet(wb,1,header=TRUE)
?str(testxls)
#'data.frame':??? 5 obs. of? 4 variables:
# $ comp? : chr? "chemA" "chemB" "chemC"
"chemD" ...
# $ item? : chr? "x" "x" "y" "y" ...
# $ studya: chr? "11" "NA" "NA" "12" ...
####check the difference
# $ studyb: chr? "21" "22" "23" "24" ...
testcsv <-
read.csv("test-1.csv",header=TRUE,sep="\t",stringsAsFactors=FALSE)
?str(testcsv)
#'data.frame':??? 5 obs. of? 4 variables:
# $ comp? : chr? "chemA" "chemB" "chemC"
"chemD" ...
# $ item? : chr? "x" "x" "y" "y" ...
# $ studya: int? 11 NA NA 12 13? ######
# $ studyb: int? 21 22 23 24 NA
setMissingValue(wb,value=c("NA"))
testxls2 <- readWorksheet(wb,1,header=TRUE)
?str(testxls2)
#'data.frame':??? 5 obs. of? 4 variables:
# $ comp? : chr? "chemA" "chemB" "chemC"
"chemD" ...
# $ item? : chr? "x" "x" "y" "y" ...
# $ studya: num? 11 NA NA 12 13
# $ studyb: num? 21 22 23 24 NA
?res1 <- melt(testxls2,id=c("comp","item"),na.rm=TRUE)
res1
#?? comp item variable value
#1 chemA??? x?? studya??? 11
#4 chemD??? y?? studya??? 12
#5 chemE??? y?? studya??? 13
#6 chemA??? x?? studyb??? 21
#7 chemB??? x?? studyb??? 22
#8 chemC??? y?? studyb??? 23
#9 chemD??? y?? studyb??? 24
##Another way would be:
testxls[,3:4] <- lapply(testxls[,3:4],as.numeric)
?res2 <- melt(testxls,id=c("comp","item"),na.rm=TRUE)
?identical(res1,res2)
#[1] TRUE
A.K.
Dear all,
I m trying to use the xlsx package to read the excel file.
However, I found after I read the file in R.
I can't "melt" it with "na.rm" arguement.
For example:
(reading the xls file, (I can't attach xlsx file, so I attached the csv file
instead...))
testxls=read.xlsx("test.xlsx", sheetName="1")
I could not remove the na items in the dataframe:
melt(testxls, id=c("comp","item"), na.rm=TRUE)
na still there~
Could anyone kindly tell me how to use "na.rm"?
Many thanks.
ps: I could work it perfectly with csv file...
I wonder what I have missed...
-vivian