Regression Looping stuck with the first excel sheet

[Ovais]

Hi,

I need help to extract similar data from various excel sheets into R through a loop for a regression analysis of grade 8, 9 and 10 students' height against their mothers' and fathers' height .

The excel file name is "height.xlsx". The three sheet names are "Grade 8", "Grade 9" and "Grade 10". The column names are "Student_Height" for dependent variable data, and "Mother_Height" and "Father_Height" for independent data.

I am running the following code:

A = getSheetNames("height.xlsx")

for (i in c(1:3))

{

my_sheet = read_excel("height.xlsx", sheet = A[i])

X = data.frame(Mother_Height,Father_Height)

Y = Student_Height

reg_fit = ln(Y ~ X)

}

Now the problem is that while running through the loop, X and Y are stuck with the first sheet and are not going to the second and third sheets.

Can someone please help me fix this. Thanks

[andresrcs]

Also you can avoid the loop and read all sheets into a single dataframe, then you can nest and model by "Grade", see this example with made up data

library(readxl)
library(tidyverse)

file_path <- "height.xlsx"

# Read all sheets into a single dataframe
heights <- file_path %>% 
    excel_sheets() %>% 
    set_names() %>% 
    map_dfr(.f = ~read_excel(path = file_path, sheet = .x), .id = "Grade")

heights
#> # A tibble: 27 x 4
#>    Grade   Student_Height Mother_Height Father_Height
#>    <chr>            <dbl>         <dbl>         <dbl>
#>  1 Grade 8              1             1             1
#>  2 Grade 8              2             2             2
#>  3 Grade 8              3             3             3
#>  4 Grade 8              4             4             4
#>  5 Grade 8              5             5             5
#>  6 Grade 8              6             6             6
#>  7 Grade 8              7             7             7
#>  8 Grade 8              8             8             8
#>  9 Grade 8              9             9             9
#> 10 Grade 9              3             3             3
#> # … with 17 more rows

# Nest and model by Grade
heights_models <- heights %>% 
    group_nest(Grade) %>% 
    mutate(model = map(data, ~lm(Student_Height ~ Mother_Height + Father_Height, data = .)))

heights_models 
#> # A tibble: 3 x 3
#>   Grade    data             model 
#>   <chr>    <list>           <list>
#> 1 Grade 10 <tibble [9 × 3]> <lm>  
#> 2 Grade 8  <tibble [9 × 3]> <lm>  
#> 3 Grade 9  <tibble [9 × 3]> <lm>

# Extract individual models
heights_models$model[2]
#> [[1]]
#> 
#> Call:
#> lm(formula = Student_Height ~ Mother_Height + Father_Height, 
#>     data = .)
#> 
#> Coefficients:
#>   (Intercept)  Mother_Height  Father_Height  
#>     1.184e-15      1.000e+00             NA