r long to wide and covariance matrix

Question

This is my dataset,

df1 <- "ID     t      res
        1      1     -1.5
        1      2     -1.5
        1      3      0.5
        1      4      0.5
        2      1     -0.5
        2      2     -0.5
        2      3     -2.0
        2      4     -1.5
        2      5      1.5"
df1 <- read.table(text = df1, header = TRUE)

What I like to do is,

1. Covert this to a matrix.

2. Transform from long to wide

     ID   1     2     3     4    5
     1   -1.5 -1.5   0.5  0.5   NA
     2   -0.5 -0.5  -2.0 -1.5   1.5
   

3. Finally Create a 5 x 5 covariance matrix like this.

    1      2       3       4      5
    0.5    0.5    -1.25   -1      0
    0.5    0.5    -1.25   -1      0
   -1.25  -1.25    3.125   2.5    0
   -1     -1       2.5     2      0
    0      0       0       0      0
   

I am able to do this with a dataframe , using functions pivot_wide or reshapeand cov which is manual and tedious. However i am not sure how to perform these steps when the data object is a matrix. Any suggestion is very helpful. Thanks.

Answer 1

Try this.

cov(reshape(as.data.frame(M), idvar='ID', timevar='t', direction='wide')[, -1])
#       res.1 res.2  res.3 res.4 res.5
# res.1  0.50  0.50 -1.250  -1.0    NA
# res.2  0.50  0.50 -1.250  -1.0    NA
# res.3 -1.25 -1.25  3.125   2.5    NA
# res.4 -1.00 -1.00  2.500   2.0    NA
# res.5    NA    NA     NA    NA    NA

---

Data:

M <- structure(c(1, 1, 1, 1, 2, 2, 2, 2, 2, 1, 2, 3, 4, 1, 2, 3, 4, 
5, -1.5, -1.5, 0.5, 0.5, -0.5, -0.5, -2, -1.5, 1.5), dim = c(9L, 
3L), dimnames = list(NULL, c("ID", "t", "res")))

## or
M <- as.matrix(df1)  ## from OP

Answer 2

And here‘s the tidyverse equivalent.

df1 %>%
  as.data.frame() %>%
  pivot_wider(names_from = 't',
              values_from = 'res') %>%
  select(-ID) %>%
  cov()

Answer 3

A base R solution with the new pipe operator introduced in R 4.1.0 is

mat1 <- as.matrix(df1)

covmat <- mat1 |>
  as.data.frame() |>
  reshape(direction = "wide",
          idvar = "ID",
          timevar = "t") |>
  {\(x) `[`(x, -1)}() |>
  cov()

covmat[is.na(covmat)] <- 0
covmat
#>       res.1 res.2  res.3 res.4 res.5
#> res.1  0.50  0.50 -1.250  -1.0     0
#> res.2  0.50  0.50 -1.250  -1.0     0
#> res.3 -1.25 -1.25  3.125   2.5     0
#> res.4 -1.00 -1.00  2.500   2.0     0
#> res.5  0.00  0.00  0.000   0.0     0

^{Created on 2023-02-26 with reprex v2.0.2}