Put multiple Excel sheets in one graph without repeating read.xlsx
I have an Excel file with three sheets: sheet 1, sheet 2, sheet 3. Each sheet has an x and y column. I want to put three xy plots in one plot like below
What I did was read each sheet separately as well as "plot + lines".
Is there any loop method for doing this? Because I will have more than three sheets.
source to share
I think a good approach here would be to read each sheet in a list of dataframes, group them into one dataframe that includes an ID for the original 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 shown below. The code assumes that the Excel file is in the current working directory. If not, provide the appropriate file path when reading data. I used a package readxl
to read data. This technique generalizes to an Excel workbook with any number of sheets with the same column names (although you could do additional processing to handle different column names on 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()
source to share
Another way might be to use library(xlsx)
, it has a function called getSheets
it that can tell you the name and the number of sheets you have in the book. I read the sheet names and then use them to create a list of data for all sheets. I will concatenate this data into a long format that will be used later with ggplot.
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 a workbook (Workbook1.xlsx) with three sheets (1, 2 and 3)
I have three different datasets in each one with x and y variables. Using the above code, I can get the graph as shown below.
source to share