Ovais
July 6, 2019, 12:36pm
1
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

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
```

system
Closed
July 27, 2019, 3:45pm
4
This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.