How to create an Origin-Destination matrix in R for multiple observations tied to unique ID?-CodePudding

Here I have a sample dataset and my goal is to generate matrices (explained below)

ID <- c(1,1,1,
        2,2,
        3,3,3,3,3,3,
        4,4,4,
        5,5,5,5,5,5)
OZ <- c("OZ1", "OZ1", "OZ3", 
        "OZ3", "OZ3", 
        "OZ1", "OZ3", "OZ1", "OZ3", "OZ1", "OZ3", 
        "OZ1", "OZ2", "OZ3",
        "OZ2", "OZ3", "OZ2", "OZ1", "OZ3", "OZ2")
DZ <- c("DZ3", "DZ3", "DZ1", 
        "DZ1", "DZ2",
        "DZ3", "DZ1", "DZ3", "DZ1", "DZ3", "DZ1",
        "DZ3", "DZ3", "DZ1",
        "DZ3", "DZ2", "DZ3", "DZ3", "DZ1", "DZ3")
        
OD <- data. Frame(ID, OZ, DZ)

What I am looking to do is to generate two sets of matrices, one for each ID and the other for all ID.

For the first case, I am looking for something like this:

#For ID = 1
       OZ1  OZ2  OZ3
  DZ1   0    0    1
  DZ2   0    0    0
  DZ3   2    0    0

And I need to do this for about 500 unique IDs for about 4000 observations. My best educated guess is to use a loop function within which I write the function to generate this matrix.

The second part is to get another matrix that should look like below:

ID  OZ1DZ1  OZ1DZ2  OZ1DZ3  OZ2DZ1  OZ2DZ2  OZ2DZ3  OZ3DZ1  OZ3DZ2  OZ3DZ3
1     0       0        2      0        0       0       1       0      0
2     0       0        0      0        0       0       1       1      0
3     0       0        1      0        0       0       1       0      0
4     0       0        1      0        0       1       1       0      0
5     0       0        1      0        0       3       1       1      0

In my attempt to solve this, I looked at https://cran.r-project.org/web/packages/od/vignettes/od.html and tried OD_matrix <- od_to_odmatrix(OD) which did not give me any count (presumably because OZ and DZ are characters). I looked at various other questions on this forum, but none seem to work for my case.
Hence, as always, your time and help are really appreciated!

CodePudding user response：

first part:

 table(rev(OD))
, , ID = 1

     OZ
DZ    OZ1 OZ2 OZ3
  DZ1   0   0   1
  DZ2   0   0   0
  DZ3   2   0   0

, , ID = 2

     OZ
DZ    OZ1 OZ2 OZ3
  DZ1   0   0   1
  DZ2   0   0   1
  DZ3   0   0   0

, , ID = 3

     OZ
DZ    OZ1 OZ2 OZ3
  DZ1   0   0   3
  DZ2   0   0   0
  DZ3   3   0   0

, , ID = 4

     OZ
DZ    OZ1 OZ2 OZ3
  DZ1   0   0   1
  DZ2   0   0   0
  DZ3   1   1   0

, , ID = 5

     OZ
DZ    OZ1 OZ2 OZ3
  DZ1   0   0   1
  DZ2   0   0   1
  DZ3   1   3   0

If you need the results in a list format, you should do asplit(table(rev(OD)),3)

The second:

 with(OD, table(ID, interaction(OZ, DZ, sep = "", lex.order = TRUE)))
   
ID  OZ1DZ1 OZ1DZ2 OZ1DZ3 OZ2DZ1 OZ2DZ2 OZ2DZ3 OZ3DZ1 OZ3DZ2 OZ3DZ3
  1      0      0      2      0      0      0      1      0      0
  2      0      0      0      0      0      0      1      1      0
  3      0      0      3      0      0      0      3      0      0
  4      0      0      1      0      0      1      1      0      0
  5      0      0      1      0      0      3      1      1      0