如何在R的年度截面回归结果表中添加系数与对应p值？

我正在R中执行1995至2018年的年度截面回归，数据来自Excel工作簿（每个工作表对应一个年份）。目前无法将回归系数及其对应的p值添加到生成的resultados_df中，以下是我的脚本、当前结果及期望结果：

原脚本 #

library(lmtest)
library(olsrr)
library(tseries)
library(readxl)
library(openxlsx) 
library(dplyr)

años <- 1995:2018 

resultados <- list()

for (año in años) {
  # 定义Excel文件路径（可根据需求修改）
  archivo_excel <- "C:/Users/Kevin/Desktop/Bases/base_Regresiones.xlsx"
  
  # 导入对应年份工作表的数据
  datos <- read_excel(archivo_excel, sheet = as.character(año))

  # 执行回归分析，假设因变量为y，其余为自变量
  modelo <- lm(y ~ ., data = datos)
  
  # 计算所需统计量
  r_squared <- summary(modelo)$r.squared
  f_statistic <- summary(modelo)$fstatistic[1]
  ssr <- deviance(modelo)
  pvalues <- summary(modelo)$coefficients[, "Pr(>|t|)"]
  jb_test <- jarque.bera.test(residuals(modelo))
  bg_test <- bgtest(modelo)
  
  # 将结果存入列表，包含R平方、F统计量等
  resultados[[as.character(año)]] <- list(
    Año = año,
    R_squared = r_squared,
    F_statistic = f_statistic,
    u_squad = ssr,
    Coeficientes = coef(modelo),
    Pvalues = pvalues,
    JBtest = jb_test$p.value,
    BGtest = bg_test$p.value,
    BPtest = bp_test$p.value
  )
}

# 将结果列表转换为数据框
resultados_df <- do.call(rbind, resultados)

当前结果 #

Año  R_squared F_statistic u_squad  JBtest   
1995 0.9480865 148.5375    10.46748 0.4176042
1996 0.9485575 149.972     10.31898 0.2332913
1997 0.9462563 142.0284    10.8743  0.3334412
1998 0.9464819 143.84      10.8509  0.5535448
1999 0.9475053 145.5997    10.57655 0.4754198
2000 0.9472141 144.7519    10.81674 0.3440291
2001 0.9504769 156.0998    10.27286 0.8708391
2002 0.9494986 151.6648    10.43004 0.8408953
2003 0.9502323 155.2925    10.36924 0.6420716
2004 0.9501257 154.9432    10.38435 0.8029328
2005 0.9498327 153.991     10.4385  0.2381718
2006 0.9518624 160.8269    10.04476 0.3467262
2007 0.9513862 159.1718    10.17184 0.3430534
2008 0.9468287 144.8315    10.96748 0.4548003
2009 0.9525406 163.2413    9.350981 0.5795036
2010 0.948046  148.4155    10.10924 0.6508358
2011 0.9510188 157.9168    9.487844 0.5200191
2012 0.948731  150.507     9.751045 0.5669693
2013 0.9453135 140.5932    10.37591 0.4488269
2014 0.9465016 143.8963    10.07656 0.4465016
2015 0.9469356 145.1395    10.06823 0.2957086
2016 0.9479821 148.223     9.874893 0.2328414
2017 0.9452753 140.4895    10.42455 0.1856073
2018 0.9451515 140.154     10.45936 0.2261362

期望结果 #

Año  R_squared F_statistic u_squad  JBtest   constant x1       x2...    x2018 pvalue_x1... pvalue_x2018
1995 0.9480865 148.5375    10.46748 0.4176042 0.344558 0.245688 0.242411        0.03
1996 0.9485575 149.972     10.31898 0.2332913 0.545682 0.833226 0.643456        0.02
1997 0.9462563 142.0284    10.8743  0.3334412 0.788953 0.412100 0.142731        0.11
1998 0.9464819 143.84      10.8509  0.5535448
1999 0.9475053 145.5997    10.57655 0.4754198
2000 0.9472141 144.7519    10.81674 0.3440291
2001 0.9504769 156.0998    10.27286 0.8708391
2002 0.9494986 151.6648    10.43004 0.8408953
2003 0.9502323 155.2925    10.36924 0.6420716
2004 0.9501257 154.9432    10.38435 0.8029328
2005 0.9498327 153.991     10.4385  0.2381718
2006 0.9518624 160.8269    10.04476 0.3467262
2007 0.9513862 159.1718    10.17184 0.3430534
2008 0.9468287 144.8315    10.96748 0.4548003
2009 0.9525406 163.2413    9.350981 0.5795036
2010 0.948046  148.4155    10.10924 0.6508358
2011 0.9510188 157.9168    9.487844 0.5200191
2012 0.948731  150.507     9.751045 0.5669693
2013 0.9453135 140.5932    10.37591 0.4488269
2014 0.9465016 143.8963    10.07656 0.4465016
2015 0.9469356 145.1395    10.06823 0.2957086
2016 0.9479821 148.223     9.874893 0.2328414
2017 0.9452753 140.4895    10.42455 0.1856073
2018 0.9451515 140.154     10.45936 0.2261362

解决方案 #

问题原因 #

原脚本中Coeficientes和Pvalues是向量类型，存入列表后do.call(rbind, resultados)无法将其展开为数据框的列，只会将它们作为单个元素处理，因此结果中看不到系数和p值。

修改后的脚本 #

library(lmtest)
library(olsrr)
library(tseries)
library(readxl)
library(openxlsx) 
library(dplyr)

años <- 1995:2018 

resultados <- list()

for (año in años) {
  archivo_excel <- "C:/Users/Kevin/Desktop/Bases/base_Regresiones.xlsx"
  datos <- read_excel(archivo_excel, sheet = as.character(año))
  modelo <- lm(y ~ ., data = datos)
  
  # 计算基础统计量
  r_squared <- summary(modelo)$r.squared
  f_statistic <- summary(modelo)$fstatistic[1]
  ssr <- deviance(modelo)
  pvalues <- summary(modelo)$coefficients[, "Pr(>|t|)"]
  jb_test <- jarque.bera.test(residuals(modelo))
  bg_test <- bgtest(modelo)
  bp_test <- bptest(modelo)  # 补全原脚本缺失的BP检验计算
  
  # 处理回归系数：转换为列表并重命名截距项
  coef_list <- as.list(coef(modelo))
  names(coef_list)[names(coef_list) == "(Intercept)"] <- "constant"
  
  # 处理p值：转换为列表并添加前缀
  pval_list <- as.list(pvalues)
  names(pval_list) <- paste0("pvalue_", names(pval_list))
  names(pval_list)[names(pval_list) == "pvalue_(Intercept)"] <- "pvalue_constant"
  
  # 合并所有结果为单个列表
  resultados[[as.character(año)]] <- c(
    list(
      Año = año,
      R_squared = r_squared,
      F_statistic = f_statistic,
      u_squad = ssr,
      JBtest = jb_test$p.value,
      BGtest = bg_test$p.value,
      BPtest = bp_test$p.value
    ),
    coef_list,
    pval_list
  )
}

# 转换为数据框
resultados_df <- do.call(rbind, resultados)

说明 #

1. 补全BP检验：原脚本中未定义bp_test，添加bp_test <- bptest(modelo)避免报错。
2. 拆分系数与p值：将系数向量和p值向量转换为命名列表，每个元素对应一个变量的系数或p值，这样do.call(rbind)会自动将这些元素展开为数据框的列。
3. 重命名截距项：将默认的(Intercept)重命名为constant，pvalue_(Intercept)重命名为pvalue_constant，更符合期望结果的命名习惯。

内容的提问来源于stack exchange，提问作者kev