I like using R for statistical analysis but find it difficult to compare output of different models.
Is there any way, we can export output to excel to make it more readable (using some formatting like scientific to number notation, conditional formatting etc)?
As suggested by @42 How to Copy Summary() output from R to Excel, I tried capture.output() but it doesn't work properly. I've searched a lot, couldn't find a solution.
Using the package XLConnect you can write R output to Excel files.
Here's an example, where I write a model and send the summary to excel:
library(XLConnect)
dat <- data.frame(rsp = rnorm(100, 0, 1),
pred1 = rnorm(100, 0, 1),
pred2 = rnorm(100, 0, 1))
model <- lm(rsp ~ pred1 + pred2, data = dat)
writeWorksheetToFile("model1.xlsx",
data = summary(dat),
sheet = "summary",
header = TRUE,
clearSheets = TRUE)
If you are trying to export the output of summary function, try this
write.csv(summary(data_frame),"output.csv")
I had the same problem a while ago and started using the package stargazer. It doesn't export output to Excel, but makes nice HTML, Latex and ASCII tables which can be copy pasted to Excel. In my opinion the strength of the package is that it allows to quickly create a table which compares different models.
More info: https://cran.r-project.org/web/packages/stargazer/vignettes/stargazer.pdf
As @David_B said, stargazer package is really nice for simple tables and can output to txt,html.
If you want to output your dataframe as an Excel file, then have a look at the xlsx package.
Be aware that xlsx doesn't work with a dplyr tbl_df and you will need to define it as a dataframe e.g.
write.xlsx (x = as.data.frame(df), file = "foo.xlsx")
I propose a simple solution via the clipboard. Function tabout sends the normal output to the console plus a tab-delimited version thereof to the clipboard. Then, you can directly paste e.g. into excel.
tabout <- function(output){
print(output)
capture.output(output, file = "clipboard", append = FALSE,
type = "output", split = FALSE)
lines <- readClipboard()
for(i in 1 : 5) {lines <- gsub(" ", " ", lines, fixed=TRUE)}
lines <- gsub(" ", "\t", lines, fixed=TRUE)
writeClipboard(lines)
}
myanova <- Anova(mymodel, type="III")
tabout(myanova)
While this is mocog = most ordinary code of the galaxy ;^), it does the main job of placing numbers in columns. A slightly more elaborate version below uses a set of phrases that include blanks, but should be kept in one piece in the output (i.e should not be split by inserting tab's).
glmphrases <- c(
"Sum Sq", "F value", "Std. Error", "t value", "test statistic",
"test stat", "approx F", "num Df", "den Df", "p adj",
" = ", " ~ ", " : ", " on ", " and ", "Signif. codes: 0",
"'***' 0.001", "'**' 0.01", "'*' 0.05", "'.' 0.1", "' ' 1"
)
tabout <- function(output, phrases = glmphrases){
# send "output" to the console and a copy to the clipboard
print(output)
capture.output(output, file = "clipboard", append = FALSE,
type = "output", split = FALSE)
lines <- readClipboard()
# collapse repeated blanks and replace with tabs
for(i in 1 : 5) {lines <- gsub(" ", " ", lines, fixed=TRUE)}
lines <- gsub(" ", "\t", lines, fixed=TRUE)
# retain each phrase in one piece and write back to clipboard
phrases.tab <- gsub(" ", "\t", phrases, fixed=TRUE)
for(i in 1 : length(phrases)){
lines <- gsub(phrases.tab[i], phrases[i], lines, fixed=TRUE)
}
writeClipboard(lines)
}
myanova <- Anova(mymodel, type="III")
tabout(myanova)
Hope this is useful, best wishes
Kleks
You can write data to a .csv file using write.csv() or write.csv2(). CSV documents can be opened in Excel and saved as .xls if you want to edit the formatting etc.
And this is not a solution, but run options(scipen=100). That configures R not to use scientific notation, so when you view a dataframe you see the numeric values in regular decimal form.
Long back, I had asked this question as I was curious about how to get publication-ready results & tables from the output of different models in R.
Later, I found many beautiful ways to do so and even wrote a LinkedIn post about the same. Today, someone asked me the same question & It clicked to my mind that I should answer this question on SO too as many R users might not be knowing.
#SPSS users usually look for table format that can be pasted in excel/word or pdf.
Here are the available options :
https://cran.r-project.org/web/packages/broom/vignettes/broom.html
The broom package takes the messy output of built-in functions in R, such as lm, nls, or t.test, and turns them into tidy tibbles.
broom::tidy() _ It constructs a tibble that summarizes the model’s statistical findings. This includes coefficients and p-values for each term in a regression, per-cluster information in clustering applications, or per-test information for multtest functions broom::augment() _ add columns to the original data that was modeled. This includes predictions, residuals, and cluster assignments broom::glance() _ It constructs a concise one-row summary of the model. This typically contains values such as R^2, adjusted R^2, and residual standard error that are computed once for the entire model.
Both 1 & 2 are my favorite
sjPlot package https://strengejacke.wordpress.com/2014/02/20/no-need-for-spss-beautiful-output-in-r-rstats/
expss package for tables with labels https://gdemin.github.io/expss/
r2spss pacakge https://cran.r-project.org/web/packages/r2spss/vignettes/r2spss-intro.pdf
Some screenshots might be helpful.
1. There is actually a way to copy-paste tabular data (say "data.frame" class objects) to Excel, though Windows-only. Try this:
write.table(your_object, "clipboard", sep = "\t", row.names = FALSE)
Replace your_object with a name of an actual data frame, but other arguments should stay as they are, including "clipboard".
Execution of the command will return nothing, but now you can switch to Excel and paste your table.
If you don’t need column names, add col.names = FALSE. For comma as a decimal separator, add dec = “,”.
2. Now, since you need to copy-paste some model output, you should preliminarily convert it into something table-like. The package broom is rather popular in this regard and was already suggested here, so I just bring it up once again.
I used the "readxl" package. It worked wonderfully for exporting data to excel.
install.packages("readxl")
library(readxl)
input <- read_excel("data/mydata.xlsx")
You can chose to export data into SAS, database or online using options provided here: https://www.blue-granite.com/blog/importing-and-exporting-getting-data-into-and-out-of-r
