pivot_wider in R to get a distance matrix from three columns

Question

I have a dataframe with 3 columns as the following:

set.seed(123)

demo_data <- data.frame(
  
  query = rep(sprintf("ID%s", c(1:4)),4),
  target = rep(sprintf("ID%s", c(1:4)), each = 4),
  dist = round(runif(min = 40, max = 100, n = 16))
  
)

I'm planning to get a matrix where the values listed in query and target columns are the new columns and the ANI values are to each cell in the dataframe, something like this:

	ID1	ID2	ID3	ID4
ID1	55	93	79	57
ID2	43	82	83	49
ID3	60	78	73	98
ID4	97	100	76	94

PD: these are synthetic values, in my original data I want the diagonal to be 100 for each cell (the same against the same).

Answer 1

You can simply run xtabs

> xtabs(dist ~ ., demo_data)
     target
query ID1 ID2 ID3 ID4
  ID1  57  96  73  81
  ID2  87  43  67  74
  ID3  65  72  97  46
  ID4  93  94  67  94

Answer 2

Here is how we can do it with pivot_wider and column_to_rownames:

library(dplyr)
library(tidyr)
library(tibble)

demo_data %>%
  pivot_wider(
    names_from = target, 
    values_from = dist,
    id_cols = query
  ) %>%
  column_to_rownames(var = "query") 

  ID1 ID2 ID3 ID4
ID1  57  96  73  81
ID2  87  43  67  74
ID3  65  72  97  46
ID4  93  94  67  94