4

I want to export results from multiple regressions into an excel file in a very specific format

MWS

data("mtcars")
str(mtcars)
m1<-lm(hp ~ disp, data = mtcars)
m2<-lm(hp ~ disp + wt, data = mtcars)

I find this format the most suitable:

library(texreg)
screenreg(list(m1, m2))

===================================
             Model 1     Model 2   
-----------------------------------
(Intercept)   45.73 **    68.84 *  
             (16.13)     (31.80)   
disp           0.44 ***    0.54 ***
              (0.06)      (0.14)   
wt                       -14.45    
                         (17.10)   
-----------------------------------
R^2            0.63        0.63    
Adj. R^2       0.61        0.61    
Num. obs.     32          32       
RMSE          42.65       42.85    
===================================
*** p < 0.001, ** p < 0.01, * p < 0.05

I want to convert the above into a dataframe or anything similar in order to export it to excel, preserving its format.

Other ideas that could generate a similar table and export it to excel are welcome.

The most import thing for me is to export the coefficients having the * on top of each, so I can understand which one is significant in every new regression.

Can you please help me with that ?

msh855
  • 1,493
  • 1
  • 15
  • 36

1 Answers1

3

You can use stargazer library, I think its very close to what you require,

for example:

library(stargazer)

l1 <- lm(mpg ~ hp + drat, data=mtcars)
l2 <- lm(mpg ~ disp + hp, data=mtcars)

stargazer(l1, l2, title= "Regression comparision", type="html")

Here type = "text" generates a table like below, However you can change it to html and generate html equivalent, if you copy and paste the html to plain text file and save it as htm/html and then open in browser, you will get nicely formatted table.

How to export to excel: Run the command with type="html", copy the html to excel sheet , that is it. Excel will able to convert entire html to nice looking chart like the screenshot.

enter image description here

PKumar
  • 10,971
  • 6
  • 37
  • 52
  • Thanks. That worked. Is there a way to convert this into a dataframe? and remove unnecessary rows? For example, I want for later to be flexible so I can merge these results with data from some dataframes. My ideal scope is to have the (-1) (-2) as column vectors, and the dependent variables are row names, keeping the coefficients with the asterisks on top as elements of the dataframe. If you can also help with that will be splendid !! – msh855 May 24 '18 at 15:53
  • @msh855 , No unfortunately there is no way to do that with stargazer for now, but there is an alternate use package `broom`, its a package which converts "lm" class into a much more readable data frame. Try it. https://cran.r-project.org/web/packages/broom/vignettes/broom.html and http://varianceexplained.org/r/broom-intro/ . With the restrictions that you need ** its difficult. You can check my earlier answer https://stackoverflow.com/questions/50341020/how-to-flip-a-regression-table-in-stargazer/50342254#50342254 , where I have converted stargazer to data frame but it lacks that * – PKumar May 24 '18 at 15:56
  • Noted. Thanks. Looks promising, but `stargazer()' is more tidy for my purpose cause of the asterisks. Everybody can understand that some coefficients are significant. :) – msh855 May 24 '18 at 16:00