Creating an Excel Workbook with R Equivalent to Stata’s Codebookout Command

Stata is a popular statistical software package widely used in data analysis and research. One of its features, codebookout, allows users to create an Excel workbook that saves the name, label, and storage type of all variables in the existing dataset along with their corresponding values and value labels.

R, on the other hand, is another programming language and software environment for statistical computing and graphics. While R has many built-in features for data analysis and visualization, creating a function equivalent to Stata’s codebookout command can be challenging. In this article, we will explore how to achieve this using R.

Problem Statement

The user is looking for an R function that creates an Excel workbook with the same structure as Stata’s codebookout output. The desired output includes:

• Variable Name (the name of each column)
• Variable Label (the label or name associated with each column)
• Answer Label (unique values in each column, categorized or open-ended)
• Answer Code (numerical assignments to each category in the Answer Label, blank if the Answer Label is not categorical)
• Variable Type (data type of each column)

Solution

We will create a custom R function create_codebook that takes a data frame as input and produces the desired output.

## utility function: pad vector with blanks to specified length
pad <- function(x,n,p="") {
    return(c(x,rep(p,n-length(x))))
}

## process a single column
proc_col <- function(x,nm) {
    u <- unique(x)
    
    ## if (length(u)==length(x)) {
    if (!is.factor(x)) {
        n <- 1
        u <- "open ended"
        cc <- ""
    } else {
        cc <- as.numeric(u)
        n <- length(u)
    }
    
    dd <- data.frame(`Variable Name`=pad(nm,n),
                       `Variable Label`=pad(nm,n),
                       `Answer Label`=u,
                       `Answer Code`=cc,
                       `Variable Type`=pad(class(x),n),
                       stringsAsFactors=FALSE)
    return(dd)
}

## process all columns
proc_df <- function(x) {
    L <- Map(proc_col,x,names(x))
    dd <- do.call(rbind,L)
    rownames(dd) <- NULL
    
    # create additional column for Answer Label to display values
    u <- unique(dd$`Answer Label`)
    dd$`Display Values` <- ifelse(is.factor(dd$`Variable Type`), u, "Open ended")
    
    return(dd)
}

This function works by applying the proc_col function to each column in the input data frame. The proc_col function processes a single column and returns a data frame with the desired columns.

Example Usage

We will create an example using the built-in Titanic dataset in R.

# load necessary libraries
library(Hmisc)
library(XLSX)

# create the codebook
xx <- as.data.frame(table(Titanic$Class))
dd <- proc_df(xx)

# write to excel file
write_xlsx(dd, "codebook.xlsx")

This will generate an Excel workbook named codebook.xlsx with the desired output.

Notes and Limitations

• The code uses a custom function proc_col to process individual columns. This approach ensures that each column is evaluated correctly.
• The proc_df function creates additional column Display Values for the Answer Label, which displays values when the variable type is categorical.
• Note that this implementation does not handle complex cases like multiple levels in a categorical variable or missing values.

Conclusion

While Stata’s codebookout command provides a convenient way to create an Excel workbook with metadata about variables, R can also be used to achieve similar results using custom functions and the XLSX package. This article demonstrates how to create such a function, which takes a data frame as input and produces the desired output.

Future Improvements

• Refine the implementation to handle more complex cases.
• Consider adding additional features or metadata columns.
• Explore alternative packages or libraries for creating Excel workbooks in R.

Last modified on 2025-02-07