Convert dataframe to Multi-dimensional array - R

user_14315772 0
Viewed 652

I have two data frames like the ones below..

df1 <- data.frame(row.names = c(1,2,3,4,5,6), Week = c(1,1,2,2,52,52), NAME = c("Florida", "Georgia","Florida", "Georgia","Florida", "Georgia"), X2001 = c(25,16,83,45,122,132), X2002 = c(3, 78, 22, 5, 166, 103))
df2 <- data.frame(row.names = c(1,2,3,4,5,6), Week = c(1,1,2,2,52,52), NAME = c("Florida", "Georgia","Florida", "Georgia","Florida", "Georgia"), X2001 = c(18,64,38,15,186,101), X2002 = c(15, 89, 16, 1, 137, 100))

I would now like to combine these dataframes into a single 4d array [dataframe (1:2), state (1:50), week (1:52), year (1:20)] where the values in the year columns can be accessed by indexing this array.

Similar to this example.. R - convert data.frame to multi-dimensional matrix

I have started to do this with the following code..

#Set up array to organize state BA data
State.Names <- unique(df1[,"NAME"])
modis.ba <- array(0,c(2,length(State.Names),52,length(2001:2020)))
modis.years <- 2001:2020
modis.weeks <- 1:52

for(landowner in 1:2){
  print(landowner)
  #Read in MODIS
  if(landowner == 1){
    ba.modis <- df2
  }
  if(landowner == 2){
    ba.modis <- df1
  }
  
  #Populate the state BA array
  for(state in State.Names){ 
  
  To be continued........

I am having trouble figuring out how to complete this for loop to obtain the array that I want. Any easy solutions for this?

dataframe multidimensional-array r
3 Answers

Is something like this what you want?:

library(dplyr)
library(tidyr)
df <- df1 %>% 
    full_join(df2, by = c("Week", "NAME"), suffix = c("1","2")) %>% 
    pivot_longer(cols = -c(Week,NAME), names_to = c(".value","df"), names_pattern = "(.*)(\\d)$") %>% 
    pivot_longer(cols = starts_with("X"), names_to = "year", names_prefix = "X") %>% 
    rename(dataframe = df, week = Week, state = NAME) %>% 
    select(dataframe, state, week, year, value)

darr <- xtabs(value~.,df)

> darr

, , week = 1, year = 2001

         state
dataframe Florida Georgia
        1      25      16
        2      18      64

, , week = 2, year = 2001

         state
dataframe Florida Georgia
        1      83      45
        2      38      15

, , week = 52, year = 2001

         state
dataframe Florida Georgia
        1     122     132
        2     186     101

, , week = 1, year = 2002

         state
dataframe Florida Georgia
        1       3      78
        2      15      89

, , week = 2, year = 2002

         state
dataframe Florida Georgia
        1      22       5
        2      16       1

, , week = 52, year = 2002

         state
dataframe Florida Georgia
        1     166     103
        2     137     100
> darr[,state = "Florida", , year = "2001" ]
         week
dataframe   1   2  52
        1  25  83 122
        2  18  38 186
user_9979790

You can do this in a single base R call:

arr <- array(unlist(c(df1[,c("X2001", "X2002")], df2[,c("X2001", "X2002")])), 
             dim = c(2, 3, 2, 2), 
             dimnames = list(State = c("Florida", "Georgia"), 
                             Week  = c(1, 2, 52), 
                             year  = 2001:2002, 
                             df    = 1:2))

So that:

arr
#> , , year = 2001, df = 1
#> 
#>          Week
#> State      1  2  52
#>   Florida 25 83 122
#>   Georgia 16 45 132
#> 
#> , , year = 2002, df = 1
#> 
#>          Week
#> State      1  2  52
#>   Florida  3 22 166
#>   Georgia 78  5 103
#> 
#> , , year = 2001, df = 2
#> 
#>          Week
#> State      1  2  52
#>   Florida 18 38 186
#>   Georgia 64 15 101
#> 
#> , , year = 2002, df = 2
#> 
#>          Week
#> State      1  2  52
#>   Florida 15 16 137
#>   Georgia 89  1 100

and

arr[State = "Florida", , year = "2001", ]
#>     df
#> Week   1   2
#>   1   25  18
#>   2   83  38
#>   52 122 186

and

arr[State = "Florida", Week = "52", year = "2001", df = "2"]
#> [1] 186
user_125003150

Maybe you can try xtabs like below

xtabs(
  cbind(X2001, X2002) ~ .,
  merge(cbind(df = 1, df1), cbind(df = 2, df2), all = TRUE)
)

such that

, , NAME = Florida,  = X2001

   Week
df    1   2  52
  1  25  83 122
  2  18  38 186

, , NAME = Georgia,  = X2001

   Week
df    1   2  52
  1  16  45 132
  2  64  15 101

, , NAME = Florida,  = X2002

   Week
df    1   2  52
  1   3  22 166
  2  15  16 137

, , NAME = Georgia,  = X2002

   Week
df    1   2  52
  1  78   5 103
  2  89   1 100
user_121587570
Related