Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A simple command to grab coefficients, t-stats, p-values, f-stats, etc from a regression and export them as an easy to use spreadsheet.
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)
ncol <- 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")
@weiqinwur

This comment has been minimized.

Copy link

commented Dec 31, 2013

Dear Francis,

Thank you very much indeed for your code : )
When I run the code, it showed an error as below:
Error in ncol + 1 : non-numeric argument to binary operator
How to fix it?

Kind regards,
Wei

@ghost

This comment has been minimized.

Copy link

commented Mar 5, 2014

Thank you for the code! There is a small typo (ncoll instead of ncol) in line 7 that causes the "Error in ncol+1: non-numeric argument to binary operator". Here is the full code:

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)
ncol <- 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")
@EconometricsBySimulation

This comment has been minimized.

Copy link
Owner Author

commented Mar 25, 2014

# Excel summary exporter

Modified from Smart, Francis

Jean P. Gibert, 2014

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=(ncoll+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:(ncoll+1)] <- formatter(co)

Save F-stat

G[1,2] <- paste("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)
write.csv(G, file=file, row.names=F)
}

@EconometricsBySimulation

This comment has been minimized.

Copy link
Owner Author

commented Mar 25, 2014

Thanks for the corrections!

@tonytonycc

This comment has been minimized.

Copy link

commented Jun 19, 2014

Hi,

This function is great and I appreciate you putting it together. Question that I can't figure out unfortunately (I am a beginner.)

Trying to loop the output for multiple regressions that I'm doing across monthly samples.

This works fine: lmOut(m_2014-01-31,file="my-results.csv")

But the loop below (where monthschar[1]= 2014-01-31) does not work and returns:
Error in res$coefficients : $ operator is invalid for atomic vectors

for (i in 1:length(monthschar)) {
lmOut(paste("m_",monthschar[i],sep="",paste("",sep="")),
file=paste("output",monthschar[i],sep=""),paste(".csv",sep=""))
}

Any idea why this is happening? The silly quotation marks (`) that R put around the models I generated with another for loop are the reason I attached the ugly paste notation.

Thank you!

@YukunZhang

This comment has been minimized.

Copy link

commented Dec 18, 2014

Hi

Thanks for the function. I encounter this error:
Error in matrix("", nrow = (nvar + nstats), ncol = (ncoll + 1)) :
invalid 'nrow' value (too large or NA)

I am using a linear mixed effect model.
Thanks

@amacharia

This comment has been minimized.

Copy link

commented Feb 19, 2015

thanks for the code ...I am also a beginner and it is very useful... thanks for putting putting some sense into to responder2 on R bloogers... http://www.r-bloggers.com/export-r-results-tables-to-excel-please-dont-kick-me-out-of-your-club/

@avinavsinha

This comment has been minimized.

Copy link

commented Jan 6, 2016

Just curious, why haven't you made this into a package?

@MichaelChirico

This comment has been minimized.

Copy link

commented Apr 29, 2018

much more canonical than

if (length(grep("summary", class(res)))==0)

would be

if (inherits(res, 'summary.lm'))
@edb983

This comment has been minimized.

Copy link

commented Jun 11, 2018

Thank you this is very helpful. I did notice that line 7 and line 17 and 19 all need to be consistent "ncol" or "ncoll" .

@MichaelChirico: I change
"if (length(grep("summary", class(res)))==0) res <- summary(res) "
to
" if (inherits(res, 'summary.lm')) res <- summary(res)"

but then the function throws an error:

"Error in matrix("", nrow = nvar + nstats, ncol = ncol + 1) :
invalid 'nrow' value (too large or NA)"

The original version does produce the "my-results.csv" output file.

@chrissy-briggs

This comment has been minimized.

Copy link

commented Oct 9, 2018

Hello, many thanks for this :) I'm trying to run it but I'm getting the following error:

Error in grep("summary", class(res)) : object 'res' not found

Apologies if this is something basic, I am very, very new to R!
Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.