0

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: imageLinkOfWhatHappens

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.

De Novo
  • 7,120
  • 1
  • 23
  • 39
Mayleen Cortez
  • 37
  • 1
  • 7
  • So what exactly do you want this to look like in excel? I'm not sure I understand your desired output. Do you want to reduce this to a 2-dimensional matrix somehow? Because that's pretty much all the programs like Excel can handle. Also it's easier to help if you share your data in a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – MrFlick Jun 20 '18 at 21:19
  • Just to be clear, you have 100 matrices that are each 7x100? In your example, `write.csv` calls `as.data.frame` on the array, which flattens each matrix and puts them side by side in a data frame – camille Jun 20 '18 at 21:42
  • @MrFlick Well for starters, I kind of want to format it so that instead of putting each matrix side by side, they’re placed on top of each other. In other words, if my array is M by N by K, excel gives me a 2D array thats M by N*K but I want it formatted as N*K by M instead (without changing the original array if possible.) Otherwise, if there was a way to insert an extra “blank” column in between each “dimension” so that I could more easily distinguish between the arrays, that would be nice. Does that make any sense? – Mayleen Cortez Jun 20 '18 at 22:00
  • @camille Oh okay i did not know that! Do you know if there is a way to modify the code so that it doesn’t output them side-by-side? – Mayleen Cortez Jun 20 '18 at 22:06

1 Answers1

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).

camille
  • 16,432
  • 18
  • 38
  • 60