CODEWITHSUNDEEP
r
Subhashree
Subhashree

Reputation: 108

Delete the first sheet of multiple excel sheet and consolidate remaining with R

I have multiple excel files having different number of sheets in each file. The 1st sheet of each files is named summary. I want to get rid of the summary sheet of each file and consolidate the other sheets in a data frame.

I have files names 1.xlsx, 2.xlsx .... 343.xlsx. Each excel file contains multiple sheets. For example, 1.xlsx contains sheets names summary, Delhi, Noida 2.xlsx contains sheets names summary, Mumbai, Pune, Goa, Hyderabad and so on.

I need to delete summary sheet of all 343 files and merge all other sheets of each file in a single data frame

The number of rows and columns are not same

Example: Sheet x contains the following data:

Content of sheetx

Sheet y contains the following data:

Content of sheetx

Desired output:

OUTPUT

Upvotes: 1

Views: 602

Answers (1)

Prem
Prem

Reputation: 11985

Considering all tabs except Summary in each excel file have same column names - 

library(readxl)   

f_in <- list.files("directory_path_of_your_excel_files/")

read_all_tabs <- function(f_name) {
  tabs <- excel_sheets(f_name)
  tabs <- tabs[-1]
  tab_data <- lapply(tabs, function(x) read_excel(f_name, sheet = x))
  names(tab_data) <- tabs
  tab_data
}

df <- Reduce(rbind, lapply(f_in, function(x) Reduce(rbind, read_all_tabs(x))))


Edit: Since all sheets have different number of rows/ columns and your requirement is to column bind these data I think cbind.na would be handy.

library(qpcR)
df <- Reduce(qpcR:::cbind.na, lapply(f_in, function(x) Reduce(qpcR:::cbind.na, read_all_tabs(x))))

Upvotes: 1

Related Questions