Hello,
I am a new user and need help to export the estimated results to Excel. Many thanks.
mydata<- read.csv("Benthic_final.csv", TRUE, ",")
mydata=as.matrix(data)
mydata[,sapply(mydata,is.numeric)]
data=mydata[,sapply(mydata,is.numeric)]
library("Hmisc")
my_data=as.matrix(data)
rcorr(my_data, type="pearson")
There are several libraries that allows you to write xls files, one of those is xls,you can do something like this.
library("Hmisc")
library(xlsx)
mydata <- read.csv("Benthic_final.csv", TRUE, ",")
mydata <- as.matrix(data)
data <- mydata[,sapply(mydata,is.numeric)]
my_data <- as.matrix(data)
results <- rcorr(my_data, type="pearson")
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
Dear @andresrcs
Thanks for the reply.
I got this error when run your suggested codes:
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
Error in write.xlsx(as.data.frame(result$r), file = "results.xlsx") :
could not find function "write.xlsx"
You have to install and load xlsx package first
install.packages('xlsx')
library(xlsx)
Dear @andresrcs
The package was already installed. If you don't mind could you kindly download the R project from the below link (which also has the data file) to test the codes.
https://drive.google.com/drive/folders/1k1As_pptboA5LfpUNA7WfpNKGAZi5BZn?usp=sharing
This works for my with your data on rstudio server linux version, but If you are on windows you might be having problems with xlsx package and your Java installation, if you don't mind the .csv extension you can use readr::write_excel_csv() instead.
library(Hmisc)
library(xlsx)
my_data <- data.frame(
Annelida. = c(-0.131778305, -0.030880161, 3.995076341, 0.045341,
-0.161345401, -0.045219353, 3.977922041, -0.262785757,
-0.206141008, -0.160736468, 3.711160423, -0.223712782,
-0.189363136, 1.002411927, 1.979772939, 1.684596561),
Arthropoda = c(-0.094260992, 0.769075751, -0.130124631, 0.255073383,
-0.204691037, -0.12415447, -0.174578416, -0.083796551,
-0.223197192, -0.210846358, -0.247095059, -0.123972223,
-0.259272506, -0.344794918, 0.211751103, -0.427072885),
Cnidaria = c(-0.259337169, -0.31086473, -0.264059728, -0.282365849,
-0.248036673, -0.262290926, -0.281878169, -0.262785757,
-0.23684214, -0.24553782, -0.318802586, -0.243660894,
-0.259272506, -0.344794918, -0.426701227, -0.427072885),
Echinodermata = c(-0.259337169, -0.31086473, -0.264059728, -0.282365849,
-0.248036673, -0.208023032, -0.271148194, -0.262785757,
-0.233430903, -0.24553782, -0.333144092, -0.248647922,
-0.259272506, 0.318116387, -0.181142639, 1.409161416),
Mollusca = c(4.002629584, 3.871761896, 0.046669696, 3.964714909,
4.006027853, 4.000205413, 0.233160647, 4.00312365,
4.006736539, 4.006093603, 1.287446015, 4.005286922, 4.005199083,
3.760978323, 3.305789316, 3.092376191),
Nemertea. = c(-0.214316393, -0.299436788, -0.264059728, -0.269257575,
-0.229459972, -0.257357481, -0.206768342, -0.262785757,
-0.219785955, -0.230119392, -0.304461081, -0.233686838,
-0.212666259, -0.195105268, -0.37758951, -0.21284555),
Nemotoda = c(-0.259337169, -0.31086473, -0.264059728, -0.295474123,
-0.248036673, -0.257455656, -0.281878169, -0.262785757,
-0.243664613, -0.24553782, -0.289413973, -0.248647922,
-0.259272506, -0.366179153, -0.426701227, -0.45767679),
H = c(-0.258516586, -0.310350875, -0.263423863, -0.29396544,
-0.247400525, -0.261868287, -0.280743747, -0.260208491,
-0.243258602, -0.245235106, -0.331887246, -0.24807019,
-0.257924494, -0.364629858, -0.423752036, -0.453681971),
Dep = c(-0.249243246, -0.305147795, -0.255658461, -0.27724616,
-0.233637653, -0.253873975, -0.257683464, -0.19672979,
-0.232120043, -0.235579408, -0.282339984, -0.232898389,
-0.150736596, -0.288020707, -0.180680989, -0.308416517),
Temp = c(-0.247196727, -0.298691082, -0.256405238, -0.281995094,
-0.239577871, -0.252169008, -0.266459571, -0.231883693,
-0.240116893, -0.239676451, -0.312819187, -0.243582205,
-0.228055451, -0.345390349, -0.362254684, -0.427232027),
Salinity = c(-0.244958746, -0.302530531, -0.253542958, -0.269790375,
-0.236221853, -0.25506751, -0.260803529, -0.204114523,
-0.236931466, -0.239851823, -0.304918276, -0.238847442,
-0.214986029, -0.33439863, -0.329341801, -0.397285512),
DO = c(-0.250882094, -0.307038446, -0.258031226, -0.279260683,
-0.24076242, -0.258905194, -0.269893382, -0.225880927,
-0.23958563, -0.242759749, -0.317169419, -0.242460379,
-0.232914871, -0.348470706, -0.374009308, -0.419875019),
Chl_a = c(-0.25732373, -0.310325051, -0.26261275, -0.292426781,
-0.246143724, -0.261578666, -0.279225632, -0.255764008,
-0.242803163, -0.24457332, -0.33029591, -0.247627663,
-0.252735595, -0.362899326, -0.418711673, -0.451419975)
)
result <- rcorr(as.matrix(my_data), type="pearson")
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
Created on 2019-01-06 by the reprex package (v0.2.1)
Dear @andresrcs
Still I didn't get anything. I also run only library(xlsx) and got the below error:
library(xlsx)
Error: package or namespace load failed for ‘xlsx’:
.onLoad failed in loadNamespace() for 'rJava', details:
call: fun(libname, pkgname)
error: JAVA_HOME cannot be determined from the Registry
In addition: Warning message:
package ‘xlsx’ was built under R version 3.5.2
PS: how to complete the "readr::write_excel_csv()" for my datat?
For working with excel, you have also those 
The advantage of these ones is that they do not have a Java dependency.
You have several discussions here on this topic
Dear @cderv
How can I use it for for my data.
Could you kindly download the R project from the below link (which also has the data file) to test the codes.
https://drive.google.com/drive/folders/1k1As_pptboA5LfpUNA7WfpNKGAZi5BZn?usp=sharing
The three packages listed by @cderv are your best bet, for sure.
Use this syntax to install and load the package:
install.packages("writexl")
library(writexl)
you can read about writexl from the package documentation
Dear @jdlong,
I installed the suggested package, but still need your help to complete the below code to export the estimated results in excel etc. file format. Thank you for the help.
mydata<- read.csv("Benthic_final.csv", TRUE, ",")
mydata=as.matrix(data)
mydata[,sapply(mydata,is.numeric)]
data=mydata[,sapply(mydata,is.numeric)]
library(writexl)
library("Hmisc")
my_data=as.matrix(data)
rcorr(my_data, type="pearson")
jdlong already told you how you can learn to do that, we are not here to do your job for you, we are here to point you in the right direction.
If you can't implement yourself the solutions we already gave you, maybe you need to work a little more in your r basic skills, this online book by Roger Peng is a very good starting point.
What have you tried?
What result did you get?
What is your specific question?
Dear @jdlong
Thanks for the kind reply. I run the code like this:
mydata<- read.csv("Benthic_final.csv", TRUE, ",")
mydata=as.matrix(data)
mydata[,sapply(mydata,is.numeric)]
data=mydata[,sapply(mydata,is.numeric)]
library(writexl)
library("Hmisc")
my_data=as.matrix(data)
rcorr(my_data, type="pearson")
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
and got this error again:
write.xlsx(as.data.frame(result$r), file = "results.xlsx")
Error in write.xlsx(as.data.frame(result$r), file = "results.xlsx") :
could not find function "write.xlsx"
PS: Dear @andresrcs, I try my self but still got the error. I am going to read Sir. Roger Peng' book and just downloaded it. But right now I need to find a quick way to export the estimated results in excel format, so I can summarize the findings and write them into my research report. Thanks for your helps.
If you read the package documentation you will see that in this particular package the function is written with an underscore instead of a dot
write_xlsx(as.data.frame(result$r), path = "results.xlsx")
Dear @andresrcs
This time, I got this error:
write_xlsx(as.data.frame(result$r), file = "results.xlsx")
Error in write_xlsx(as.data.frame(result$r), file = "results.xlsx") :
unused argument (file = "results.xlsx")
With that library the correct parameter name is path
write_xlsx(as.data.frame(result$r), path = "results.xlsx")
Dear @andresrcs
Sir this time the error is like:
write_xlsx(as.data.frame(result$r), path = "results.xlsx")
Error in as.data.frame(result$r) : object 'result' not found
You are missing this
result <- rcorr(my_data, type="pearson")
Dear @andresrcs
This time it works perfectly and my estimated results are exported as xls file into my directory. But my estimated results had two section:
So how I can also export the results about p value?