Linear model results written to excel file

Question

I'm running a linear model in R and I want the entire output of my model to be written to the same excel file.

Right now, I can do this only for the coefficients, which is the first example. The second example is when I try to get the entire output to write to excel, which throws an error on the second to last line of code, see below:

# creating data set for lm
df<- cbind.data.frame(var1= rnorm(10,3,2), var2= rnorm(10,4,1))

# running sample model
lmodel<- lm(var1~var2, data = df)

# assigning model results to a variable
mod_res<- summary(lmodel)
mod_res

# assigning model coefficients to a variable
modCoeff<- coef(summary(lmodel))
modCoeff

# getting model coefficients to open in an excel spreadsheet... THIS WORKS!
lmodCoeffs<- openxlsx::createWorkbook()
openxlsx::addWorksheet(lmodCoeffs, "coeffs")
openxlsx::writeData(lmodCoeffs, "coeffs", modCoeff, rowNames= TRUE)
openxlsx::openXL(coeffs)

# look at ALL model fit stats in excel... THIS DOES NOT WORK!
modResSheet<- openxlsx::createWorkbook()
openxlsx::addWorksheet(modResSheet, "res")
openxlsx::writeData(modResSheet, "res", mod_Res, rowNames= TRUE) # error thrown here
openxlsx::openXL(modResSheet)

Getting the coefficients only is useful, however, seeing all model fit stats within one single excel file will make model evaluation more comprehensive.

IceCreamToucan · Accepted Answer · 2018-12-26T17:56:25.910

3

If what you want on the excel sheet is the output printed when you run print(mod_res), you can use capture.output. So the second to last line in your question should be

openxlsx::writeData(modResSheet, "res", capture.output(mod_res))

For a more tidy layout you can use tidy and glance from the broom package

library(broom) #part of tidyverse
openxlsx::writeData(modResSheet, "res", tidy(mod_res)) 
openxlsx::writeData(modResSheet, "res", glance(mod_res),
                    startRow = nrow(tidy(mod_res)) + 4)

edited Dec 26 '18 at 17:56

answered Dec 26 '18 at 17:25

IceCreamToucan

28,083
2
22
38

this is good but not quite: the format of the data from this is not clean and requires a lot of formatting post-excel writing, whereas the `lmodCoeffs` comes out in clean columns/rows... any idea how to get the format to be cleaner for the full model results? – dre Dec 26 '18 at 17:45
I added an option which separates the results into columns/rows. – IceCreamToucan Dec 26 '18 at 17:51

score 2 · Answer 2 · answered Dec 26 '18 at 17:47

2

I hope you already received the answer to your original question. I just wanted to add an alternative for convenience sake and other readers that simply want to export their models for further inspection and model evaluation. The package apaTables has a function called apa.reg.table() that exports the model output table to .doc file in complete APA style formating (even with CIs and semi-partial correlation squared). I find it the most convenient way to do what you are asking (assuming I understood what you are useing it for).

answered Dec 26 '18 at 17:47

Claudiu Papasteri

2,469
1
17
30

will it export to `xls`, `xlsx`, or `csv` format as well? or just `doc`? Thanks! – dre Dec 26 '18 at 17:49
.rtf or .doc only. It is not the actual solution for what you asked for and I don't supose that modifying the `apa.re.table()` function will be very straight forward in order to export to the wright cells in a excel spreadsheet. – Claudiu Papasteri Dec 26 '18 at 17:58

Linear model results written to excel file

2 Answers2