multiple excel sheets in R

Question

I have a excel file with 131 sheets, that all contain data in the same format. Each sheet contains data of one station, and is named with that number and name. Here is the example for one sheet for station no. 13:

(13) stationName

So far I read the sheets one by one, for example

data13 <- read_excel("mydata.xlsx", n_max = 65, skip = 1, sheet = 7)

In every sheet there's data on 52 variables, that I collect by choosing the required columns.

ID111 <- data13[c(1:9)] 
ID112 <- data13[c(1,10:17)]
ID113 <- data13[c(1,18:25)]
ID114 <- data13[c(1,26:33)]
ID115 <- data13[c(1,34:41)]
ID123 <- data13[c(1,42:49)]
ID122 <- data13[c(1,50:57)]
etc.

these are then put into a list

datasets <- list(ID111, ID112, ID113, ID114, ID115, ID123, ID122) 
names(datasets) <- c(111, 112, 113, 114, 115, 123, 122)

and I reshape the data using

station13 <- map_dfr(datasets, ~ .x %>%
                       setNames( c("Year", "MS", "UL", "JS", "BF", "FF", "RF", "CL", "FL")) %>%
                       pivot_longer(cols = "MS":"FL",
                                    names_to = "Phase", 
                                    values_to = "DOY"
                       ), .id = 'Species') %>%
  relocate(Species, .after = 'Year')
station13$StationID <- "13"

at the very end I will combine all dataframes to have all data from all excel sheets in one file using rbind().

So far I did copy&paste it for every station and change the station number manually. Is there a way to make this more efficient?

jpsmith · Accepted Answer · 2023-01-26 22:58:33Z

4

Instead of reading in each of the 131 sheets manually, you can read all the sheets directly into a list using lapply (then do whatever operations you need on the list)

library(readxl)
# File path
dataxlsx <- "/FilePath/data_file.xlsx"

# Read in all sheets in excel file to a list
xl_list <- lapply(excel_sheets(dataxlsx), read_excel, path = dataxlsx, n_max = 65, skip = 1)

You can rename with

names(xl_list) <- excel_sheets(dataxlsx)

edited Jan 26, 2023 at 22:58

answered Jan 26, 2023 at 22:48

jpsmith

18.5k6 gold badges25 silver badges48 bronze badges

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

2 Comments

Johanna Over a year ago

Thank you, the reading works with this! But how can I apply my operations to the whole list? I can subset the station name with the list name, but what do assign it to (instead of station13 in the above example)?

jpsmith Over a year ago

Happy to help! but each section of what you need should be a posted question - stack overflow discourages questions with multiple asks (in fact, they are often closed for the following reason: “This question currently includes multiple questions in one. It should focus on one problem only.”). If you break this up, you’ll get much faster, better help - good luck!

Collectives™ on Stack Overflow

multiple excel sheets in R

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related