CODEWITHSUNDEEP
rexcel
user6077819
user6077819

Reputation: 31

Combine Multiple Excel Worksheets into Single Dataframe with Sheet Name as First Column, R

I have an Excel workbook that contains 19 sheets, names Sheet1 through to Sheet19. I would like to import all 19 sheets into one R data.frame and use the sheet name as the first column. So I would end up with something like:

Excel rendition of the structure I would like for the dataframe

Thank you.

enter image description here

Upvotes: 2

Views: 6226

Answers (2)

PKumar
PKumar

Reputation: 11128

You may use readxl like below: I have an excel sheet "Book1.xlsx" with similar formats across all the three sheets within that workbook. I have columns a,b and c in the three sheets. You don't have to specify the names, "sheet" object will carry your names of sheets. Also, please note that using lapply will iterate over your sheets and later we can append it to get the final dataframe

My Assumption: Your all sheets structure are similar in nature.

library(readxl)
library(tidyverse)
setwd("C:\\Users\\pradeep4.kumar\\Desktop") ###set your working directory where your data resides
sheet = excel_sheets("Book1.xlsx")
df = lapply(setNames(sheet, sheet), function(x) read_excel("Book1.xlsx", sheet=x))
df = bind_rows(df, .id="Sheet")

Sample of the Output:

> data.frame(df)
    Sheet   a  b   c
1  Sheet3  17 59  82
2  Sheet3  17 26  84
3  Sheet3  92 33  17
4  Sheet3  12 60  16
33 Sheet2  50 27  42
34 Sheet2  47 75  49
35 Sheet2  94 44  20
36 Sheet2  79 23  55
37 Sheet2  35 35   5
38 Sheet2  30 74  29
39 Sheet1   2 95  81
40 Sheet1  89 65  30

Upvotes: 3

quant
quant

Reputation: 4482

It would be easier if you used data.table instead of data.frame. So assuming that your xlsx file is called Book1.xlsx this should work

df <- data.table()
for (i in 1:19)
{
  sheet <- paste0("Sheet",i)
  temp.df <- as.data.table(read.xlsx(file = "Book1.xlsx",sheetIndex = sheet))
  temp.df[,sheetname:=i]
enter code here

  df <- rbind(df,temp.df)
}

Keep in mind, that the column names in every sheet need to be the same.

Upvotes: 3

Related Questions