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

Question

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?

Is the sheet name same for each excel file i.e sheet = "Bridge Object Girder Forces" ? Also skip =3 part? Is that common to all? — Ronak Shah
– Ronak Shah, Commented Mar 28, 2020 at 2:16
Does this answer your question? How can I read multiple csvs and retain the number in the file name for each? — Nova
– Nova, Commented Mar 28, 2020 at 2:18
Yes they are all the same type of data files generated from a finite element software. I only need the same sheet and always skip the first 3 rows @RonakShah — Maral Dorri
– Maral Dorri, Commented Mar 28, 2020 at 2:24

Ronak Shah · Accepted Answer · 2020-03-28 03:40:16Z

2

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]

answered Mar 28, 2020 at 3:40

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maral Dorri Over a year ago

Thank you so much, this is exactly what I wanted to do. Can you explain what the pattern ('S\\d+\\.xlsx') indicated? For example, I am reading files that are named "84-9-S00.xlsx". The naming convention has 6 options for the first term (84, 92, 100, 108, 116, 124), 3 options for the second term (9, 12, 15), and 11 options for the third term (S00 to S10)

Ronak Shah Over a year ago

@MaralDorri You don't have to be very specific about the regex. The main thing is how many excel files you have in the directory and out of that how many you want to read. For example, if you want to read all the excel files you could just do filenames <- list.files(pattern = '\\.xlsx', full.names = TRUE) without worrying about their exact names at all.

Maral Dorri Over a year ago

Thank you so much again!

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related