Export R Results Tables to Excel – Please don’t kick me out of your club

August 19, 2013
By

(This article was first published on Econometrics by Simulation, and kindly contributed to R-bloggers)

This post is written as a result of finding the following exchange on one of the R mailing lists:

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
  if (length(grep("summary", class(res)))==0) res <- summary(res)
  co <- res$coefficients
  nvar <- nrow(co)
  ncoll <- ncol(co)
  f <- res$fstatistic
  formatter <- function(x) format(round(x,ndigit),nsmall=ndigit)
  # This sets the number of rows before we start recording the coefficients
  nstats <- 4
  # G matrix stores data for output
  G <- matrix("", nrow=nvar+nstats, ncol=ncol+1)
  G[1,1] <- toString(res$call)
  # Save rownames and colnames
  G[(nstats+1):(nvar+nstats),1] <- rownames(co)
  G[nstats, 2:(ncoll+1)] <- colnames(co)
  # Save Coefficients
  G[(nstats+1):(nvar+nstats), 2:(ncol+1)] <- formatter(co)
  # 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"
  G[2,3] <- formatter(1-pf(f[1],f[2],f[3]))
  # 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)
  print(G)
  if (writecsv) write.csv(G, file=file, row.names=F)
}
lmOut(res)
# First let's generate some fake binary response data (from yesterday's post).
  Nobs <- 10^4
  X <- cbind(cons=1, X1=rnorm(Nobs),X2=rnorm(Nobs),X3=rnorm(Nobs),u=rnorm(Nobs))
  B <- c(B0=-.2, B1=-.1,B2=0,B3=-.2,u=5)
  Y <- X%*%B
  SData <- as.data.frame(cbind(Y, X))
# Great, we have generated our data.  
myres <- lm(Y ~ X1 + X2 + X3, data=SData)
lmOut(myres, file="my-results.csv")
Created by Pretty R at inside-R.org

Output looks something like this:


Looking over the original exchange it does not look like this code would even work for a logit for which it was originally needed.  However, I will post it on github and perhaps others will find the concept useful enough to make revisions (unlikely).  I might take another go at making it more general in the future though in all likelihood some user will send me an angry message saying "this has already been done by ...".

https://gist.github.com/EconometricsBySimulation/6274532

To leave a comment for the author, please follow the link and comment on his blog: Econometrics by Simulation.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.