0

I have a code where I am performing a zero inflated model on excel spreadsheet named by the zip code. I have 174 such sheets in my directory. I need to run the code on each spreadsheet. How can do all this at once?

This is my code:

library(pscl)
library(sandwich)
library(lmtest)
library(MASS)
library(readxl)
library(pscl)
formula <- Street ~ BackUp+Catch+Manhole+PRCP
inputData <- read_xlsx("10028.xlsx")
model.zi <- zeroinfl(formula = formula, 
                          dist    = "negbin",
                          data    = inputData)
summary(model.zi)
library(car)
Anova(model.zi,
      type="II",
      test="Chisq")
library(rcompanion)
nagelkerke(model.zi)
Anova(model.zi)$'Pr(>Chisq)'

Rsquared10028 <- nagelkerke(model.zi)$'Pseudo.R.squared'
PValue10028 <- Anova(model.zi)$'Pr(>Chisq)'

write.csv(Rsquared10028,"C:/Desktop/Rsquared10028.csv", row.names = FALSE)
write.csv(PValue10028,"C:/Desktop/PValue10028.csv", row.names = FALSE)

I'd appreciate any help. I am not sure how to add an excel file, so I added an image of the data just to show the format.

10028

BigBen
  • 46,229
  • 7
  • 24
  • 40
Candace Agonafir
  • 23
  • 5
  • Are the Excel files separate files or sheets within 1 file? If several documents, are they all in the same folder? Are they all structured identically? – Brigadeiro May 03 '21 at 19:44
  • 1
    Well, this may give you an idea: https://stackoverflow.com/q/40690592/4961700 just as a suggestion. – Solar Mike May 03 '21 at 19:46
  • 1
    An answer on reading from multiple excel spreadsheets - https://stackoverflow.com/a/64702033/10276092 ; and an answer for writing to multiple excel spreadsheets - https://stackoverflow.com/a/56546316/10276092 – M.Viking May 03 '21 at 19:56

1 Answers1

1

Assuming that your 174 sheets are separate files and are the only files in your working directory, you can do this:

setwd("/path/to/wd")
formula <- Street ~ BackUp+Catch+Manhole+PRCP
lapply(list.files(), function(x){
    inputData <- read_xlsx(x)
    model.zi <- zeroinfl(formula = formula, dist = "negbin", data = inputData)
    write.csv(nagelkerke(model.zi)$'Pseudo.R.squared', paste0("C:/Desktop/Rsquared", sub("\\.xlsx", "", x), ".csv"), row.names = FALSE)
    write.csv(Anova(model.zi)$'Pr(>Chisq)', paste0("C:/Desktop/PValue", sub("\\.xlsx", "", x), ".csv"), row.names = FALSE)
    return(paste0("Done ", x)) #optional
})
Snipeskies
  • 258
  • 1
  • 5
  • Actually I realized that it is reading different R^2 and P values than when I run the individual spreadsheets manually. I am not sure why. I will post another question with this revised inquiry. – Candace Agonafir May 04 '21 at 21:13