1

I have the following code example:

library(readxl)
library(tidyverse)
N <- 5 #number of datasets to pull data from
ind <- c("2010", "2011", "2012", "2013", "2015")

A <- array(rep(1, 91*144*5), dim=c(91,144,5))

for (k in seq_along(ind)) {
   A <- read_excel(paste0("~R/data", ind[k], ".xlsx"), range="B3:EO94")
}

I know that this is not reproducible due to inability to upload data. The above code captures my thought process in what I am trying to do. I want to create a matrix A(i,j,k) where i and j are rows and columns, respectively, from the k excel files. So in my case, k is the years in ind. I have those 5 excel files and they have identical dimensions.

What I want out of this process is the ability to take a particular set of i,j and create a new vector that has a length=k, although this is not the question I am asking here. For context, i and j are longitude and latitude and k is year.

Brennan
  • 419
  • 5
  • 17
  • 1
    If you add a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610)its way easier for others to find a solution to your problem. By defintion,a MRE is **not** putting your whole code and database in the question but creating an example with as little code and data as needed to replicate the problem! The MRE will make it easier for others to find and test a answer to your question. – dario Mar 04 '20 at 21:48
  • This is honestly all I have at this point, I have no idea what to do next – Brennan Mar 04 '20 at 21:59
  • if all entires are of the same class you could make an array with 3 dimensions. – GordonShumway Mar 04 '20 at 22:07
  • @GordonShumway that is exactly what I am trying to do I just don't know how to – Brennan Mar 04 '20 at 22:23
  • 1
    @Brennan I'm not sure what's going on in your loop. You are assigning k a string each time but not using it in the loop (your file name is a static string). Also, you are just overwriting `A` each time, when you want A to be a 3D array receiving a different 2D slice in each cycle of the loop. So you should declare `A` as a 91 * 150(ish) * 5 array ahead of the loop, change the for to `for(k in seq_along(ind))`, change `A` to `A[,,k]` and `"~R/data[k]"` to `paste0("~R/data", ind[k])` – Allan Cameron Mar 04 '20 at 22:42
  • @AllanCameron Your comment definitely suffices as an answer thank you! If you post it as one I will gladly accept – Brennan Mar 04 '20 at 23:11
  • I get the following error: Error in A[, , k] <- read_excel(paste0("~/R/data", : incorrect number of subscripts – Brennan Mar 04 '20 at 23:14
  • @AllanCameron I updated the code in the question – Brennan Mar 04 '20 at 23:20
  • @Brennan can you try `A[,,k] <- as.matrix(read_excel(...`. If that doesn't work, try `A <- as.matrix(` and check `dim(A)` after your code has run – Allan Cameron Mar 04 '20 at 23:30
  • @AllanCameron when doing A <- as.matrix(rep(0,91*144*5), dim=c(91,144,5)) I get dim(A) [1] 65520. The as.matrix(read_excel(... gives me the same error message as before – Brennan Mar 04 '20 at 23:37
  • Sorry, I mean when you read the excel file directly into a variable, are the dims c(91,144)? – Allan Cameron Mar 04 '20 at 23:41
  • @AllanCameron Oh like any given one? I run the code: B <- read_excel(paste0("~R/data", ind[1], ".xlsx"), range = "B3:EO94") and then dim(B) gives me [1] 91 144 so yeah – Brennan Mar 04 '20 at 23:47
  • That's strange - you should be able to write B to A[,,1] in that case? Your other option is to make A an empty list and store each sheet to A[k] in the list. You can then do array(unlist(A), dim = c(91, 144, 5)) – Allan Cameron Mar 04 '20 at 23:53
  • @AllanCameron Your latter suggestion worked perfectly, weird that the other didn't. Thank you very much! if you want to succinctly write that out as an answer I will accept to clear it from unanswered questions – Brennan Mar 05 '20 at 00:00

1 Answers1

1

It is possible to write the sheets to a list, then create an array from the unlisted contents:

library(readxl)

ind <- c("2010", "2011", "2012", "2013", "2015")
A <- list()

for (k in seq_along(ind)) {
   A[k] <- read_excel(paste0("~R/data", ind[k], ".xlsx"), range="B3:EO94")
}

result <- array(unlist(A), dim=c(91,144,5))
Allan Cameron
  • 147,086
  • 7
  • 49
  • 87
  • Follow-up question: Will the A[k] import the excel files with `i` rows and `j` columns? It does not seem to properly match up with the excel file – Brennan Mar 05 '20 at 00:35
  • Disregard my comment, I will ask a new question as it is more involved – Brennan Mar 05 '20 at 01:13