exporting a multidimensional array from R to a spreadsheet

Question

So I am trying to export a multidimensional array from R into excel, google sheets, or some similar spreadsheet program. I would also be okay with exporting into a text file as long as it is in the format I need. I have done some web-surfing but haven't progressed too much.

Note: I have some programming experience (about 4 semesters) but am relatively new to R. I am working in group with 3 others who have almost no prior programming experience.

Our array is 7x100x100 (though I may have to make it bigger later on). I have been able to export it onto an excel spreadsheet, but the way it is formatted makes my job a little bit harder. So I have one hundred 7x100 matrices and excel just puts them all side by side (horizontally). I want to export it in a way that makes it easier to distinguish between the matrices.

As an example... if I have a multidimensional array of dimensions 2x3x4, and I print the results within RStudio (in the console), this is what displays:

, , 1

      [,1] [,2] [,3]
[1,]    0    0    0
[2,]    0    0    0

, , 2

      [,1] [,2] [,3]
[1,]    0    0    0
[2,]    0    0    0

, , 3

      [,1] [,2] [,3]
[1,]    0    0    0
[2,]    0    0    0

, , 4

      [,1] [,2] [,3]
[1,]    0    0    0
[2,]    0    0    0

I like how I can distinguish between each 2x3 matrix since they are stacked one after the other (but vertically, not horizontally)

However, on excel, this is what I get:

This is the code I used for the above example.

results <- array(0, dim = c(2,3,4))
write.csv(results,"test3.csv")

I want to modify it somehow so that the matrices are distinguishable from one another or so that they are stacked on top of each other vertically instead of horizontally.

Note: Our actual data is more than just a bunch of zeroes... this is just an example to make it simpler. Ultimately, we will be generating 7x100 matrices to put into this array in a for loop, and we need to save each generated matrix in a way we can analyze the data easier later.

Thanks for any help you can give!!! I tried to be specific but let me know if any other information would be useful.

Answer 1

Here are two options, one with base R apply to make matrices, and a dplyr solution. First off, I recreated the array with values 1:24 to make it easier to check that everything's in place, rather than if everything is 0.

library(tidyverse)

arr <- array(1:24, dim = c(2,3,4))

You can work across columns to create one large matrix, where the smaller tables are essentially stacked vertically. You could then make that a data frame or write it to a file.

apply(arr, MARGIN = 2, function(a) as.matrix(a))
#>      [,1] [,2] [,3]
#> [1,]    1    3    5
#> [2,]    2    4    6
#> [3,]    7    9   11
#> [4,]    8   10   12
#> [5,]   13   15   17
#> [6,]   14   16   18
#> [7,]   19   21   23
#> [8,]   20   22   24

In this second way, I got the first line (the lapply call) from a similar SO post; this returns a list of four matrices. After that, you can use purrr::imap_dfr to map over the matrices and create data frames, yielding one data frame. One added bonus here is that you can mutate a column to label e.g. what table each observation comes from.

# https://stackoverflow.com/a/40207726/5325862
n3 <- dim(arr)[3]
lapply(1:n3, function (i) arr[,,i]) %>%
  imap_dfr(function(mtx, i) {
    as.data.frame(mtx) %>%
      mutate(table = paste("table", i, sep = "_"))
  })
#>   V1 V2 V3   table
#> 1  1  3  5 table_1
#> 2  2  4  6 table_1
#> 3  7  9 11 table_2
#> 4  8 10 12 table_2
#> 5 13 15 17 table_3
#> 6 14 16 18 table_3
#> 7 19 21 23 table_4
#> 8 20 22 24 table_4

Created on 2018-06-20 by the reprex package (v0.2.0).