Using Stepwise Model Selection with Multiple Excel Sheets in R: A Comprehensive Guide

Nesting and Looping the Step Function in R for Multi-Excel Sheet Output

In recent years, data analysis has become increasingly complex, often involving multiple variables, datasets, and models. R is a popular programming language for data analysis, known for its ease of use and versatility. In this article, we will explore how to nest and loop the step function in R using tidyverse packages, which allows us to efficiently analyze and output results from multiple Excel sheets.

Background

The lm() (Linear Model) function is a fundamental tool in R for modeling relationships between variables. However, as datasets become increasingly complex, manual model selection can be time-consuming and prone to errors. The step() function offers an automated approach to model selection by iteratively adding and removing predictors.

We will create separate models using the lm() and step() functions for each country (AUD, EUR, SGD). The output of these models will then be written to separate Excel sheets.

R Code with Stepwise Model Selection

To begin, we need to load necessary libraries and prepare our data.

library(tidyverse)
library(broom)

# Load the data
dfraw <- read_csv("data.csv")

Next, we group by country using group_by() and then apply nest() to create a nested structure of data.

res <- 
  dfraw |&gt; 
  # If you want to run one model for each country you only need to
  # `group_by()` `Country`.
  group_by(Country) |&gt;
  nest() |&gt;
  mutate(both_model = map(data, ~ lm(VarY ~ ., data = .x)),
         step_model = map2(data, both_model, ~step(.y, direction = "both", trace = 0)))

Evaluating Model Results

summary() can be used to inspect model coefficients and standard errors, but for programmatically evaluating model results, we use broom::glance() and broom::tidy().

eval_res <- 
  res |&gt; 
  mutate(quality = map(step_model, glance),
         estimates = map(step_model, tidy)) 

eval_res |&gt; 
  select(Country, quality) |&gt; 
  unnest(quality)

Writing Models to Excel Sheets

We will use the openpyxl library to write our models to separate Excel sheets.

library(openpyxl)

# Load necessary libraries and create a workbook
wb <- loadWorkbook("output.xlsx")

# Loop through each country's model and write it to an excel sheet
for (i in unique(eval_res$Country)) {
  sheet_name <- paste0(i, "_model")
  
  # Get the current worksheet
  ws <- getWorksheet(wb, sheet_name)
  
  # Write coefficients
  ws writesheet("Coefficients", estimates[[i]][, c("term", "estimate", "std.error", "statistic", "p.value")])
}

# Save workbook to a file
wb $ saveAs("output.xlsx")

Conclusion

In this article, we explored how to nest and loop the step function in R for multi-Excel sheet output. We created separate models using lm() and step() functions for each country (AUD, EUR, SGD). The output of these models was then written to separate Excel sheets. Additionally, we evaluated model results using broom::glance() and broom::tidy(). This approach allows us to efficiently analyze complex datasets in R while also producing clear and concise reports.

Additional Resources

For further reading on the topic of stepwise model selection or a list of recommended resources for learning R, please refer to:

By following these steps, you will be able to analyze complex datasets using the lm() and step() functions in R while producing clear and concise reports.

Last modified on 2025-04-20