Export the intercepts and coefficients

General
1

Hello everybody,
I would like to ask for help. I have data like below. Dependent variable y is from p1, p2, p3, ...to p6, respectively, and independent variables are rmrf, SMB, HML, CMA,RMW. I would like to export the intercept (and coefficients of SMB, HML, RMW, CMA) in each regression into a table as follows;

image

I really appreciate any help. Thank you in advance.
Best regards,

reg<-data.frame(

rmrf = c(-11.9751824959988,-0.553741946142413,

14.6126567367923,1.12889808570392,-9.34891206376925,

-5.320234911983,-9.57704544310442,-22.0535273838718,-22.6914989446706,

0.361906662152138),

HML = c(-1.7384574213012,5.01940431986608,

11.4651524135959,24.4701133213624,0.68881245382078,

0.0116985174421913,-0.824084453396164,-0.720682027559548,-7.27696805130737,

-7.81179710210865),

SMB = c(-0.63078758830159,-3.9748240574933,

12.911658966056,34.1035766667936,1.7041630573028,1.561601024138,

-0.47341490179587,-0.567482279532688,-3.90152988878082,

-0.913375571941315),

RMW = c(-4.24694692269615,4.87157624037724,

-1.95986556216953,-3.12638066004878,2.90220139771362,

0.406959705609802,-3.52870258161911,-2.77429647022646,-6.40814545228584,

1.96559945284645),

CMA = c(1.77709999211917,-3.4786626692302,

1.70566209656657,4.32470950501213,1.09850715029654,

0.506921101987611,0.0275467943022996,-4.47348049222296,-4.80611431653632,

-4.03832502469625),

p1 = c(-5.14076223471579,-10.2096038237067,

35.7842970804946,102.27871225594,-6.80406522606962,

-0.82542749426641,-15.6152675129538,-20.772587943133,-30.0769543728323,

-2.99750628478244),

p2 = c(-11.1067719335485,-4.03104402558242,

25.6303428753607,42.1476389419119,-9.34947738023655,

-3.03467000293105,-11.1402543171837,-20.6956677626546,-21.8080366211187,

-1.63883977887855),

p3 = c(-10.2668746380167,-3.83542356643224,

35.8396253680283,58.9264623054155,-3.97937659438407,

-4.34372218829174,-12.5095336101785,-25.5513993159565,-27.2263148346345,

-6.27107640607577),

p4 = c(-3.24756191041237,-6.819137147006,

4.10961879948766,11.2289426543154,-8.33391193045244,

-8.42896045318665,-10.6185567010309,-21.4532871972318,-11.1859721862313,

5.11573623808598),

p5 = c(13.7224090547089,6.50642620820488,

10.4957763446122,-5.85894267022262,-7.2219098195008,

-3.60543524237641,2.04006651429443,-21.9967420966506,-22.5825037789464,

0.239368222709137),

p6 = c(-4.54608803556514,9.62266853601001,

10.2612365050672,4.92442431666426,-6.51725118515846,

-1.63737946911049,-7.41882901147813,-18.5066921903445,-21.7429431572509,

-5.30597952428943),

date = as.factor(c("2007-07-31",

"2007-08-31","2007-09-28","2007-10-31","2007-11-30",

"2007-12-28","2008-01-31","2008-02-29","2008-03-31",

"2008-04-29"))

)


p1<-lm(reg$p1 ~ reg$rmrf+reg$SMB+reg$HML+reg$RMW+reg$CMA, data = reg)

summary(p1)

p2<-lm(reg$p2 ~ reg$rmrf+reg$SMB+reg$HML+reg$RMW+reg$CMA, data = reg)

summary(p2)

p3<-lm(reg$p3 ~ reg$rmrf+reg$SMB+reg$HML+reg$RMW+reg$CMA, data = reg)

summary(p3)

p4<-lm(reg$p4 ~ reg$rmrf+reg$SMB+reg$HML+reg$RMW+reg$CMA, data = reg)

summary(p4)

p5<-lm(reg$p5 ~ reg$rmrf+reg$SMB+reg$HML+reg$RMW+reg$CMA, data = reg)

summary(p5)

p6<-lm(reg$p6 ~ reg$rmrf+reg$SMB+reg$HML+reg$RMW+reg$CMA, data = reg)

summary(p6)
2

I can get close to the layout you want with functions from the broom and tidyr packages. If p1 through p6 are the results of the lm() function:

p1DF <- broom::tidy(p1)
p2DF <- broom::tidy(p2)
p3DF <- broom::tidy(p3)
p4DF <- broom::tidy(p4)
p5DF <- broom::tidy(p5)
p6DF <- broom::tidy(p6)
AllInfo <- bind_rows(list(p1 = p1DF, 
                          p2 = p2DF, 
                          p3 = p3DF, 
                          p4 = p4DF, 
                          p5 = p5DF,
                          p6 = p6DF), .id = "Variable")

AllInfo <- select(AllInfo, Variable, term, estimate) %>% 
  mutate(term = ifelse(term == "(Intercept)", "Intercept", term))
AllInfo <- tidyr::pivot_wider(AllInfo, names_from = Variable, values_from = estimate)
AllInfo

# A tibble: 6 x 7
  term           p1     p2      p3      p4     p5     p6
  <chr>       <dbl>  <dbl>   <dbl>   <dbl>  <dbl>  <dbl>
1 Intercept -4.01   -1.42  -0.120  -2.82    7.94   1.39 
2 reg$rmrf   0.427   0.838  0.894   0.570   0.917  0.635
3 reg$SMB    3.10    1.19   1.33    0.938  -1.17  -0.635
4 reg$HML   -0.0616  0.104  0.431  -0.863   0.381  0.877
5 reg$RMW    0.595  -0.132 -0.0154  0.0746 -0.658  0.329
6 reg$CMA    0.172  -0.371  0.378   0.0692  2.44   0.608

I do not know of a way to get to the exact layout you want without manually extracting parts of the above data frame and placing them in a data frame or character matrix. Do you need exactly that layout?

1 Like
3

Thank you so much for your reply. In fact, I need exactly that layout. This is a table I would like to get.

image
image955×812 294 KB

I have many regressions, in this table, there are 25 regressions from p1 to p25. This table will be replicated many times with many ways of sorting portfolios.

4

I think the result of this code matches what you showed in your first post. As you can see, the code is not flexible.

p1DF <- broom::tidy(p1)
p2DF <- broom::tidy(p2)
p3DF <- broom::tidy(p3)
p4DF <- broom::tidy(p4)
p5DF <- broom::tidy(p5)
p6DF <- broom::tidy(p6)
AllInfo <- bind_rows(list(p1 = p1DF, 
                          p2 = p2DF, 
                          p3 = p3DF, 
                          p4 = p4DF, 
                          p5 = p5DF,
                          p6 = p6DF), .id = "Variable")

AllInfo <- select(AllInfo, Variable, term, estimate) %>% 
  mutate(term = ifelse(term == "(Intercept)", "Intercept", term),
         term = stringr::str_remove(term, "reg\\$"),
         term = ifelse(term != "Intercept", paste("Coefficients of", term), term),
         term = factor(term, 
                       levels = c("Intercept", "Coefficients of SMB", 
                                  "Coefficients of HML", 
                                  "Coefficients of RMW", 
                                  "Coefficients of CMA", 
                                  "Coefficients of rmrf"))) %>% 
  arrange(term)
DATA <- c()
for (TERM in unique(AllInfo$term)) {
  DATA <- c(DATA, TERM, "", "", AllInfo[AllInfo$term == TERM, ][["estimate"]])
}
MAT <- matrix(DATA, ncol = 3, byrow = TRUE)
head(MAT) #show first 6 rows

     [,1]                  [,2]                [,3]                
[1,] "Intercept"           ""                  ""                  
[2,] "-4.00916763285607"   "-1.41806105118572" "-0.119699492103685"
[3,] "-2.81788367484286"   "7.94427010089105"  "1.39488122579301"  
[4,] "Coefficients of SMB" ""                  ""                  
[5,] "3.09907511930664"    "1.18658954188907"  "1.3308839858677"   
[6,] "0.938181518109181"   "-1.16657008367081" "-0.635420813048536"
1 Like
5

Dear @FJCC,

Thank you so much. I really appreciate your help. If you don't mind, may I ask you one more question? As you know, I have to export the t-value of the intercept and t-value of all variables in the same layout. But this code is beyond of my knowledge to replicate this issue with t-value.

6

To display the t statistic you have to change this line

DATA <- c(DATA, TERM, "", "", AllInfo[AllInfo$term == TERM, ][["estimate"]])

to be

DATA <- c(DATA, TERM, "", "", AllInfo[AllInfo$term == TERM, ][["statistic"]])

You will probably also want to change every instance of Coefficients to t statistic in this earlier section.

mutate(term = ifelse(term == "(Intercept)", "Intercept", term),
         term = stringr::str_remove(term, "reg\\$"),
         term = ifelse(term != "Intercept", paste("Coefficients of", term), term),
         term = factor(term, 
                       levels = c("Intercept", "Coefficients of SMB", 
                                  "Coefficients of HML", 
                                  "Coefficients of RMW", 
                                  "Coefficients of CMA", 
                                  "Coefficients of rmrf")))
1 Like
7

Thank you @FJCC, thank you so much for taking the time on my issue.
I wish you all the best.
Best regards,

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.