XLregresSummary: Regression Summary Tables exported to a spreadsheet

In table1xls: Exports Reproducible Summary Tables to Multi-Tab Spreadsheet Files (.xls or .xlsx)

Description

Takes regression effect estimates and the corresponding standard errors, transforms to "human scale" if requested, calculates confidence-intervals and p-values, and exports a standard formatted summary table to a spreadsheet.

Usage

XLregresSummary(wb, sheet, betas, SE = NULL, varnames = NULL, colid = 1:2,
  transfun = identity, title = NULL, effname = "Difference",
  alpha = 0.05, df = NA, roundig = 2, pround = 3, row1 = 1,
  col1 = 1, purge = FALSE)

Arguments

wb	a workbook-class object
sheet	numeric or character: a worksheet name (character) or position (numeric) within wb.
betas	numeric: a vector of point estimates, or a matrix containing estimates and standard errors in columns
SE	numeric: a vector of standard-error estimates for the effects. If NULL (default), user needs to specify them via the betas matrix.
varnames	character: a vector of effect names (column 1 of output table). If NULL (default), user needs to specify them via the betas matrix.
colid	integer: vector of indices for the columns containing the point estimates and SEs, respectively. Used only if betas is a matrix.
transfun	transformation function for betas,SE, to produce columns 2-3 of the output. Defaults to identity. use exp for odds ratio or relative risk.
title	character: an optional overall title to the table. Default (NULL) is no title.
effname	character: a string explaining what the effect stands for, e.g. "difference" (the default), "Odds Ratio", etc.
alpha	numeric, Type I error for CIs. Default 0.05 for 95% CIs.
df	numeric, residual degrees of freedom. If a finite value is provided, t-distribution p-value and CIs will be calculated; otherwise Normality is assumed. Default NA. ##' @param title character: an optional overall title to the table. Default (NULL) is no title.
roundig	numeric: how many digits (after the decimal point) to round the effect estimate to?
pround	numeric: how many digits (after the decimal point) to round the p-value to? P-values rounded down to zero will show up as "<" the smallest nonzero value, e.g. with the default pround=3 p-values smaller than 0.0005 will show up as "<0.001".
row1, col1	numeric: the first row and column occupied by the table (title included if relevant).
purge	logical: should sheet be created anew, by first removing the previous copy if it exists? (default FALSE)

Details

This function produces a standard scientific-article regression summary table, given the raw regression output. The resulting table has 4 columns: effect name, its (optionally transformed) magnitude, a probabilistically symmetric confidence interval (likewise transformed), and p-value. The formatted table is exported to sheet, and the file is immediately saved.

The input can be provided as separate vectors of point estimates (betas) and standard errors (SE), or as a single matrix for betas. In the latter case, as a default the effect names will be rownames(betas), unless a vector with more descriptive names is provided via varnames.

See the XLtwoWay help page, for behavior regarding new-sheet creation, overwriting, etc.

Value

The function returns invisibly, after writing the data into sheet.

Note

The default CI's are 95% and Normal. P-values are also derived from the Normal. If you run any regression whose intervals are calculated differently (e.g., linear regression with not-huge sample size), make sure to change both confac and pfun accordingly, as is shown in the example.

Author(s)

Assaf P. Oron <assaf.oron.at.seattlechildrens.org>

Examples

book4<-XLwriteOpen("attenu.xls") 

quakenames=c("Magnitude (Richter), per unit","Distance (log km), per x10")

# Ground acceleration as a function of magnitude and distance, all log scale.
quakemod1=summary(lm(log10(accel)~mag+log10(dist),data=attenu))


## Model-scale summaries; we don't care for the intercept.
# First (wrongly) using Normal distribution for inference/CIs

XLregresSummary(book4,"ModelScale",varnames=quakenames,
                betas=quakemod1$coef[-1,1],SE=quakemod1$coef[-1,2],
                ,title="Log-Ground Acceleration Effects, Normal CIs")

# Now using t-distribution as befits linear regression

XLregresSummary(book4,"ModelScale",varnames=quakenames,
                betas=quakemod1$coef[-1,1],SE=quakemod1$coef[-1,2],
                ,title="Log-Ground Acceleration Effects",df=quakemod1$df[2],col1=6)

## Same thing, but using matrix input; no need to provide SE and names. 
## It is arguably still nicer to provide your own names - but could be a reproducibility risk. 
## Also, increasing the p-value resolution by changing 'pround'.

XLregresSummary(book4,"ModelScale",betas=quakemod1$coef[-1,],
                pround=6,title="Log-Ground Acceleration Effects",
                ,df=quakemod1$df[2],row1=8)

## Effects are arguably more meaningful as percent change. 
## So... still same model, but different summaries. 
## Also, note the combination of matrix input with names over-written via 'varnames':

XLregresSummary(book4,"PercentChange",varnames=quakenames,
                betas=quakemod1$coef[-1,],
                roundig=1,pround=6,title="Relative Ground Acceleration Effects",
                transfun=function(x) 100*(10^x-1),
                effname="Percent Change",df=quakemod1$df[2])

cat("Look for",paste(getwd(),"attenu.xls",sep='/'),"to see the results!\n")

### lm() does not take account of station or event level grouping.
### So we use a mixed model, losing 16 data points w/no station data:
### Run this on your own... and ask the authors of "lme4" about p-values at your own risk :)

# library(lme4)
# quakemod2=lmer(log10(accel)~mag+log10(dist)+(1|event)+(1|station),data=attenu)
# 
# XLregresSummary(book4,"MixedModel",varnames=quakenames,betas=fixef(quakemod2)[-1],
# SE=sqrt(diag(vcov(quakemod2)))[-1],
# roundig=1,pround=6,
# title="Relative Ground Acceleration Effects",
# transfun=function(x) 100*(10^x-1),effname="Percent Change",df=160)

table1xls documentation built on May 2, 2019, 5:54 a.m.