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
______________________________________________
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.
