Make a stack of adjacency matrices from a dataframe in R

Question

I have a dataframe like this one:

df <- as.data.frame(matrix(runif(n=45, min=1, max=10), nrow=5))

colnames(df) <- c("CT1|CT1", "CT1|CT2", "CT1|CT3",
  "CT2|CT1", "CT2|CT2", "CT2|CT3",
  "CT3|CT1", "CT3|CT2", "CT3|CT3")
rownames(df) <- c("A", "B", "C", "D", "E")

Column names consist of a source and target names divided by "|"

   CT1|CT1  CT1|CT2  CT1|CT3  CT2|CT1  CT2|CT2  CT2|CT3  CT3|CT1  CT3|CT2  CT3|CT3
A 1.556076 7.928573 7.997007 3.404986 4.063141 8.446360 4.701470 5.767476 7.234584
B 2.853771 5.479293 9.412347 4.475027 5.338721 7.016201 8.388517 8.104206 5.298577
C 2.589011 7.458567 2.909283 1.120513 6.396092 8.148159 6.823542 1.209981 8.750885
D 7.183206 9.927155 6.865064 4.441492 5.441872 1.971493 8.046395 5.295071 4.942874
E 4.456933 4.420317 2.129996 8.827218 2.675958 7.513399 5.977327 7.590824 3.203175

I need to create a list of adjacency matrices for each row of df. For example, for row A the adjacency matrix should look like this:

         CT1      CT2      CT3
CT1 1.556076 7.928573 7.997007
CT2 3.404986 4.063141 8.446360
CT3 4.701470 5.767476 7.234584

Answer 1

Base R:

cbind(read.table(text = names(df), sep="|")[col(df),], 
        row = rownames(df), val = unlist(df))|>
  xtabs(val~., data = _)

---

This prints

, , row = A

     V2
V1         CT1      CT2      CT3
  CT1 1.556076 7.928573 7.997007
  CT2 3.404986 4.063141 8.446360
  CT3 4.701470 5.767476 7.234584

, , row = B

     V2
V1         CT1      CT2      CT3
  CT1 2.853771 5.479293 9.412347
  CT2 4.475027 5.338721 7.016201
  CT3 8.388517 8.104206 5.298577

If you need it as a list include asplit

cbind(read.table(text = names(df), sep="|")[col(df),], 
         row = rownames(df), val = unlist(df))|>
   xtabs(val~., data = _)|> 
   asplit(3)
$A
     V2
V1         CT1      CT2      CT3
  CT1 1.556076 7.928573 7.997007
  CT2 3.404986 4.063141 8.446360
  CT3 4.701470 5.767476 7.234584

$B
     V2
V1         CT1      CT2      CT3
  CT1 2.853771 5.479293 9.412347
  CT2 4.475027 5.338721 7.016201
  CT3 8.388517 8.104206 5.298577

---

tidyverse:

df %>%
   rownames_to_column() %>%
   pivot_longer(-rowname, names_to = c("V1", "name"), names_sep = "[|]")%>%
   pivot_wider()

# A tibble: 15 × 5
   rowname V1      CT1   CT2   CT3
   <chr>   <chr> <dbl> <dbl> <dbl>
 1 A       CT1    1.56  7.93  8.00
 2 A       CT2    3.40  4.06  8.45
 3 A       CT3    4.70  5.77  7.23
 4 B       CT1    2.85  5.48  9.41
 5 B       CT2    4.48  5.34  7.02
 6 B       CT3    8.39  8.10  5.30
 7 C       CT1    2.59  7.46  2.91
 8 C       CT2    1.12  6.40  8.15
 9 C       CT3    6.82  1.21  8.75
10 D       CT1    7.18  9.93  6.87
11 D       CT2    4.44  5.44  1.97
12 D       CT3    8.05  5.30  4.94
13 E       CT1    4.46  4.42  2.13
14 E       CT2    8.83  2.68  7.51
15 E       CT3    5.98  7.59  3.20

You can then split this into groups

df %>%
  rownames_to_column() %>%
  pivot_longer(-rowname, names_to = c("V1", "name"), names_sep = "[|]")%>%
  pivot_wider() %>%
  group_split(rowname, .keep = FALSE) %>%
  set_names(rownames(df)) %>%
  map(~column_to_rownames(.x, 'V1'))