CODEWITHSUNDEEP
r
Maral Dorri
Maral Dorri

Reputation: 478

How to read multiple excel data files in a loop and process data from each file?

I have 11 excel data files that I would like to open, process, and finally, plot against theoretical data. I can do this for excel file separately but I would like to make the code shorter by doing it in a loop. My data file names are from S00.xlsx to S10.xlsx (11 files). I use the following to read each file:

library('readxl')
SS00 <- read_excel("S00.xlsx", sheet = "Bridge Object Girder Forces", skip = 3, col_names = FALSE)

I need to process each data file as below:

S00_shear <- data.frame(S00[,5],SS00[,8],S00[,12])
names(S00_shear) <- c("Girder", "Load", "Shear")

# Split data for single lane loaded and multi lane loaded
S00_Shear_multi.lane <- S00_shear[which(S00_shear$Load == "LL-1"),]
S00_Shear_single.lane <- S00_shear[which(S00_shear$Load == "LL-Single"),]

# Split data for the exterior girders and interior girders
S00_Shear.ext_multi.lane <- subset(S00_Shear_multi.lane, Girder %in% c("Left Exterior Girder",
                                                                     "Right Exterior Girder"))
S00_Shear.int_multi.lane <- subset(S00_Shear_multi.lane, Girder %in% c("Interior Girder 1",
                                                                     "Interior Girder 2",
                                                                     "Interior Girder 3"))

S00_Shear.ext_single.lane <- subset(S00_Shear_single.lane, Girder %in% c("Left Exterior Girder",
                                                                       "Right Exterior Girder"))
S00_Shear.int_single.lane <- subset(S00_Shear_single.lane, Girder %in% c("Interior Girder 1",
                                                                       "Interior Girder 2",
                                                                       "Interior Girder 3"))

# Find the maximum shear values
S00_Max.Shear.ext_multi.lane <- max(S00_Shear.ext_multi.lane$Shear)
S00_Max.Shear.int_multi.lane <- max(S00_Shear.int_multi.lane$Shear)
S00_Max.Shear.ext_single.lane <- max(S00_Shear.ext_single.lane$Shear)
S00_Max.Shear.int_single.lane <- max(S00_Shear.int_single.lane$Shear)

Once I have the maximum values for all 11 data files then I need a final table 

# Read theoretical values and find ratios
Beamline <- read.csv(file = "Beamline.csv", header = TRUE, sep = ",", row.names =1)
Shear <- data.frame(EXT.Single = c(S00_Max.Shear.ext_single.lane,S01_Max.Shear.ext_single.lane,
                               S02_Max.Shear.ext_single.lane,S03_Max.Shear.ext_single.lane,
                               S04_Max.Shear.ext_single.lane,S05_Max.Shear.ext_single.lane,
                               S06_Max.Shear.ext_single.lane,S07_Max.Shear.ext_single.lane,
                               S08_Max.Shear.ext_single.lane,S09_Max.Shear.ext_single.lane,
                               S10_Max.Shear.ext_single.lane)/Beamline[10,2], EXT.Multi = 
                               c(S00_Max.Shear.ext_multi.lane,S01_Max.Shear.ext_multi.lane,
                               S02_Max.Shear.ext_multi.lane,S03_Max.Shear.ext_multi.lane,
                               S04_Max.Shear.ext_multi.lane,S05_Max.Shear.ext_multi.lane,
                               S06_Max.Shear.ext_multi.lane,S07_Max.Shear.ext_multi.lane,
                               S08_Max.Shear.ext_multi.lane,S09_Max.Shear.ext_multi.lane,
                               S10_Max.Shear.ext_multi.lane)/Beamline[10,2], INT.Single = 
                               c(S00_Max.Shear.int_single.lane,S01_Max.Shear.int_single.lane,
                               S02_Max.Shear.int_single.lane,S03_Max.Shear.int_single.lane,
                               S04_Max.Shear.int_single.lane,S05_Max.Shear.int_single.lane,
                               S06_Max.Shear.int_single.lane,S07_Max.Shear.int_single.lane,
                               S08_Max.Shear.int_single.lane,S09_Max.Shear.int_single.lane,
                               S10_Max.Shear.int_single.lane)/Beamline[10,2], INT.Multi = 
                               c(S00_Max.Shear.int_multi.lane,S01_Max.Shear.int_multi.lane,
                               S02_Max.Shear.int_multi.lane,S03_Max.Shear.int_multi.lane,
                               S04_Max.Shear.int_multi.lane,S05_Max.Shear.int_multi.lane,
                               S06_Max.Shear.int_multi.lane,S07_Max.Shear.int_multi.lane,
                               S08_Max.Shear.int_multi.lane,S09_Max.Shear.int_multi.lane,
                               S10_Max.Shear.int_multi.lane)/Beamline[10,2])

Right now I just copy-paste the code 10 more times and change the S00 to S01, S02...S10. Can I do this with one loop?

Upvotes: 1

Views: 397

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388807

We could write all the code that needs to be applied in a function and return a named vector of maximum values.

apply_fun <- function(filename) {

   SS00 <- readxl::read_excel(filename, sheet = "Bridge Object Girder Forces", skip = 3, col_names = FALSE)
   S00_shear <- data.frame(S00[,5],SS00[,8],S00[,12])
   names(S00_shear) <- c("Girder", "Load", "Shear")

   # Split data for single lane loaded and multi lane loaded
   S00_Shear_multi.lane <- S00_shear[which(S00_shear$Load == "LL-1"),]
   S00_Shear_single.lane <- S00_shear[which(S00_shear$Load == "LL-Single"),]

   # Split data for the exterior girders and interior girders
   S00_Shear.ext_multi.lane <- subset(S00_Shear_multi.lane, Girder %in% c("Left Exterior Girder",
                                                                     "Right Exterior Girder"))
   S00_Shear.int_multi.lane <- subset(S00_Shear_multi.lane, Girder %in% c("Interior Girder 1",
                                                                     "Interior Girder 2",
                                                                     "Interior Girder 3"))

   S00_Shear.ext_single.lane <- subset(S00_Shear_single.lane, Girder %in% c("Left Exterior Girder",
                                                                       "Right Exterior Girder"))
   S00_Shear.int_single.lane <- subset(S00_Shear_single.lane, Girder %in% c("Interior Girder 1",
                                                                       "Interior Girder 2",
                                                                       "Interior Girder 3"))

  # Find the maximum shear values
 c(EXT.Multi = max(S00_Shear.ext_multi.lane$Shear), 
   INT.Multi   = max(S00_Shear.int_multi.lane$Shear), 
   EXT.Single  = max(S00_Shear.ext_single.lane$Shear), 
   INT.Single  = max(S00_Shear.int_single.lane$Shear))
}

Apply this function to all the excel files and divide the values with Beamline[10,2]. 

filenames <- list.files(pattern = 'S\\d+\\.xlsx', full.names = TRUE)
Beamline <- read.csv(file = "Beamline.csv", header = TRUE, sep = ",",row.names =1)

out <- data.frame(t(sapply(filenames, apply_fun)))/Beamline[10,2]

Upvotes: 2

Related Questions