How to plot a scatter plot with multiple X, Y's (vectors) from an excel (in different sheets) in R

Question

I have a large dataset in an excel file, where excel file contains multiple worksheets. in each worksheet, I have xi and Yi vectors (column vectors, where i = 1,2,3....n) of size m*n where m can be any number of rows for an xi and Yi.

the sample dataset that I have in an excel file is shown below:

Excel file name: mydata; Sheet Name: data1

T324X_w T324Y_w T332X_w T332Y_w T36X_w  T36Y_w  T41X_w  T41Y_w  
7.67562 57.6316 7.41441 57.713  7.3051  58.349  7.6438  57.218      
12.0346 60.1571 10.3314 53.887  11.250  53.327  12.164  51.912      
17.2237 45.5066 13.9237 78.237  16.102  46.838  30.048  45.656  
21.8646 55.3352 35.3032 19.981  20.565  41.655  22.441  39.498      
15.9111 34.9817 36.5099 41.203  5.404   18.42   27.487  33.855  
31.6832 40.842  24.185  47.317  8.361   31.123  23.046  48.36   
15.7659 25.880  19.629  43.792  4.659   26.810  37.157  24.490  
10.5192 21.909  21.400  45.107  9.081   22.799  42.152  28.351                      
15.724  35.202  14.579  27.205  44.006  18.97   47.415  16.778                  
11.352  14.6283 27.956  24.449  49.572  55.464  53.3125 13.0234                 
25.740  39.320  21.759  21.609  55.448  11.915  58.992  29.9650                 
                25.752  18.874  60.713  9.641   66.19   6.859                       
                29.63   84.745  67.34   6.596   73.18   3.618                   
                53.28   14.26                   81.1    0.6252                      


Excel file name: mydata; Sheet Name: data2

T325X_w T325Y_w T333X_w T333Y_w 
7.67562 57.6316 7.41441 7.713   
12.346  12.1571 10.3314 3.89    
27.37   15.5066 13.237  29.27   
21.46   10.3352 17.3032 24.1        
26.111  14.9817 20.099  21.2    
31.832  19.842  24.185  37.37       
35.659  25.880  27.29   23.2    
20.192  21.909  11.00   30.7        
45.24   18.202  34.579  27.205  
31.52   14.6283 27.56   24.9    
36.740  12.320  41.59   21.09   
                45.752  18.4                            
                49.63   56.45                   
                53.28   14.26                                           


 Excel file name: mydata; Sheet Name: data3

T325X_w T324Y_w T332X_w T332Y_w T36X_w  T36Y_w  T41X_w  T41Y_w  
7.2 16  7.1441  47.13   7.051   48.349  7.6438  37.218  22.145
12.9346 2.1571  10.14   53.887  11.250  53.327  12.164  31.12   
17.2237 5.0     13.237  49.237  16.102  46.38   17.048  45.656  
21.46   0.52    37.3032 24.1    20.565  41.655  42.441  39.498  
26.11   4.17    40.9    21.03   15.404  3.42    87.487  33.855      
31.2    9.42    44.185  27.317  10.361  1.123   73.046  28.36       
35.59   5.0     27.629  33.792  24.659  6.810   37.157  24.490      
40.2    21.09   31.400  30.107  9.081   2.799   42.152  20.351                  
45.24   18.02   24.579  37.205  44.006  8.97    47.415  16.778                  
51.2    14.3    37.759  44.449  29.572  15.464  63.3    13.0                    
55.740  12.320  41.759  41.09   25.448  1.15    38.992  9.0                     
                45.759  68.874  20.713  9.641   36.19   6.5                     
                39.63   66.45   67.34   6.596   43.18   3.1                 
                53.28   14.26                   81.1    0.252                       

i would like to plot all the Xi's Vs Yi's in all the sheets on a single scatter plot

Note: the scatter plots based on sheet: data1, data2, data3 should be of three different colors.

the expected scatterplot plotted made in excel is presented

the data presented here and the data corresponding to the plot can vary

the code I am trying is as follows

library(readxl)    
read_excel_allsheets <- function(filename, tibble = FALSE) {
  sheets <- readxl::excel_sheets(filename)
  x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
  if(!tibble) x <- lapply(x, as.data.frame)
  names(x) <- sheets
  x
}
mysheets <- read_excel_allsheets("mydata.xlsx")

plot(mydata$data1$T324X_w, mydata$data1$T324Y_w, type = "l", col = "red", xlab = "X_axis", ylab = "Y_Axis", main = "Plots")
lines(mydata$data1$T332X_w, mydata$data1$T332Y_w, type = "l", col = "red")
lines(mydata$data2$T333X_w, mydata$data2$T333Y_w, type = "l", col = "blue")
legend("topright", c("T324d1", "T332d1", "T333d2"), 
    text.col=c("red", "yellow", "green"),pch = c(1,2), lty = c(1,2))

Note: legend names should be for example "T324" is the name of the vector and "d1" is the sheet name as T324d1 i.e. specifying a name of the vector and sheet

plotting individual by xi vs yi vectors is a tedious and lengthy process

i am looking for a code for plotting all the columns vectors of Xi's and Yi's from all the sheets in a single plot

thanks in advance

Answer 1

i tried to solve using for loop and it worked for me...... but only problem is the legend

##################### code to read all worksheets from an excel file ###############
library(readxl)    

read_excel_allsheets <- function(filename, tibble = FALSE) {
sheets <- readxl::excel_sheets(filename)
x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
if(!tibble) x <- lapply(x, as.data.frame)
names(x) <- sheets
x
}
########################################################
mysheets <- read_excel_allsheets("mydata.xlsx")  ## reading excel file
mysheets <- mysheets[-1] # removing the unwanted dataframe

for (i in 1: length(mysheets)) {
     a <- data.frame(mysheets[i])

     colors <- c("red", "blue", "green", "yellow", "black")

     for (j in seq(from = 1, to = length(a), by = 2)){

         b <- plot(a[, j], a[, j+1], type = "l", xlab = "X_Axis", ylab = "Y_Axis", xlim = c(0,100), ylim = c(-10,70), col = colors[i], main = "Trajectories")

         par(new = T)

      }
}

it worked for me...... however, I didn't get the legend on the plot as expected. Is there any other way to plot the scatter plots..... where we can get a zoom in/out and other features on the plot and including legend??