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

Question

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:

Sheet y contains the following data:

Desired output:

Can you please edit your code into your post? It is much easier for us to help you if we can copy + paste your code into R, as opposed to manually typing out a screenshot (it's faster and less likely to lead to errors). In addition, some data would be useful so that we have a reproducible example. — MBorg

1.618 1.618 · Accepted Answer · 2018-03-12T06:55:46

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))))

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

1 Answers