[R] About xlsx package and reshape

[arun]

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