Export R Results Tables to Excel – Please don’t kick me out of your club
[This article was first published on Econometrics by Simulation, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
This post is written as a result of finding the following exchange on one of the R mailing lists:Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Is-there-a-way-to-export-regression-output-to-an-excel-spreadsheet
Question: Is there a way to export regression output to an excel spreadsheet?
Translation: I would like to be able to do a very simple thing that almost any statistical programming language can easily do, please suggest a basic command to do that.
Response1: ?lm ?coef ?write.csv …Translation: Read the manual and try this bit of incomplete code.
Questioner: I am very very new with R… Is there some simple code I could just paste?
Translation: Really? Isn’t there anything you could suggest?
Response2: This is the help-you-learn-R mailing list, not the do-my-work-for-me mailing list…
Translation: Go F%$# yourself, freeloader. We only answer interesting questions.
Me:
I just wanted to say that it is just this type of response that gives R-users a bad reputation. I am an active R user and very happy to contribute to R in whatever way is possible but when I see posts like this, it makes me want to switch to a language in which the users are NICE people. Okay, I know, I know. A few bad apples should not spoil the basket, but sometimes things just taste rotten.
First thing, the reason I even stumbled across this post was because I had the same very similar question. Looking at the hits on the bottom of the page I can see that there are over 400 people who have viewed this discussion I am guessing most of them because they were looking for a specific solution rather than being interested in seeing how quickly experienced R users could could offend new users (for which there are numerous other examples).
In all likelihood, a lot of other new R users have come across this same post and been equally confounded but this rude and ridiculous response.
The original user who asked this question asked a very simple question for which any statistical language should have a very simple canned response. Something along the lines:
lmOut(mylm, file=”results”, filetype=”csv”)
Yet the response that was instead produced was one which was overly complex, patronizing, and ultimately needlessly insulting.
I have written a little program to help format regression summary statistics into spreadsheet formats easily read by excel. Sorry if this is redundant. I am sure hundreds of people have programmed similar solutions. But I think it might be useful to many users who are not very familiar with how R constructs results.
lmOut <- function(res, file="test.csv", ndigit=3, writecsv=T) { # If summary has not been run on the model then run summary
co <- res$coefficients
f <- res$fstatistic
# This sets the number of rows before we start recording the coefficients
nstats <- 4
# G matrix stores data for output
# Save rownames and colnames
# Save Coefficients
# Save F-stat
G[1,2] <- paste0("F(",f[2],",",f[3],")")
G[2,2] <- formatter(f[1])
# Save F-p value
G[1,3] <- "Prob > P"
# Save R2
G[1,4] <- "R-Squared"
G[2,4] <- formatter(res$r.squared)
# Save Adj-R2
G[1,5] <- "Adj-R2"
G[2,5] <- formatter(res$adj.r.squared)
}
lmOut(res)
# First let's generate some fake binary response data (from yesterday's post).
Nobs <- 10^4
Y <- X%*%B
# Great, we have generated our data.
