Plot multiple Excel sheets into one graph without repeating read.xlsx

3
votes

I have a excel file with three sheets: sheet 1, sheet 2, sheet 3. Each sheet has column x and y. I want to put the three plots x-y into one plot like below

enter image description here

What I did was read each sheets separately, and 'plot + lines'.

Is there any loop method to do this? Because I'll have more than three sheets.

2
rexcelplotxlsx

2 Answers

2
votes

I think a good approach here would be to read each sheet into a list of data frames, stack them into a single data frame that includes an identifier for the source sheet, and then plot using ggplot2.

Here's an example with a simple Excel file called test.xlsx that I created. It has three sheets, each with four rows of data, as illustrated below. The code assumes that the Excel file is in the current working directory. If not, provide the appropriate path to the file when you read the data. I've used the readxl package to read the data. This method generalizes to an Excel workbook with any number of sheets with the same column names (though you could do additional processing to deal with different column names in different sheets).

library(readxl)
library(dplyr)
library(ggplot2)

# Get sheet names
sht = excel_sheets("test.xlsx")

sht

[1] "Sheet 3" "Sheet 2" "Sheet1"

# Read each sheet into a list
df = lapply(setNames(sht, sht), function(s) read_excel("test.xlsx", sheet=s))

df 

$`Sheet 3`
      x     y
1     1    10
2     2    11
3     3    12
4     4    13

$`Sheet 2`
      x     y
1     1     5
2     2     6
3     3     7
4     4     8

$Sheet1
      x     y
1     1     1
2     2     2
3     3     3
4     4     4

# Convert to a single data frame with a column for the source sheet
df = bind_rows(df, .id="Sheet")

# Plot
ggplot(df, aes(x,y,colour=Sheet)) +
  geom_line() +
  scale_y_continuous(limits=c(0,max(df$y))) +
  theme_classic()

enter image description here

2
votes

Another way could be to use library(xlsx), it has function called getSheets which can tell you the name and number of sheets you are having in a workbook. I am reading the names of the sheets and then using them to create a list of data for all the sheets. I combine these data into a long format to be used with ggplot later.

library(xlsx)
setwd("/Users/pradeepkumar/Desktop/Misc") ###set your working directory where your data resides
sheetname <- getSheets(loadWorkbook("Workbook1.xlsx"))
s1 <- lapply(names(sheetname),function(x)read.xlsx("Workbook1.xlsx",sheetName = x))
names(s1) <- names(sheetname)
final_data <- data.frame(do.call("rbind",s1 ))
sheets <- rownames(final_data)
sheets <- gsub("\\.\\d{1,}","",sheets)
final_data$sheets <- sheets
rownames(final_data) <- NULL
library(ggplot2)
ggplot(data=final_data,aes(x=x,y=y,color=sheets)) + geom_line()

For example I have workbook (Workbook1.xlsx) with three sheets( 1, 2 and 3)

I have three different dataset in each of them with x and y as variables. Using the above code I can get a plot as below.

enter image description here