Create a matrix symmetric from long table in r

user_13022457 0
Viewed 124

This is my data

df <- data.frame (Var1  <- c("a", "b", "c","d","e","b","c","d","e","c","d","e","d","e","e"),
                  Var2 <- c("a","a","a","a","a","b","b","b","b","c","c","c","d","d","e")
                  pre <- c(1,2,3,4,5,1,6,7,8,1,9,10,1,11,1) )

I would like to build a symmetric matrix with Var1 and Var2 function as rownames and colnames, and the matrix values are the Corresponding number in "pre" in r, like this:

  a b c d e 
a 1 2 3 4 5
b 2 1 6 7 8 
c 3 6 1 9 10
d 4 7 9 1 11
e 5 8 10 11 1

This seems to be an easy problem, but I have googled a lot of posts, but it has not been solved, so I come here to ask, thank you!

Mengying

r
4 Answers

You can get the data in wide format first.

library(dplyr)
library(tidyr)

mat <- df %>%
  pivot_wider(names_from = Var2, values_from = pre, values_fill = 0) %>%
  column_to_rownames('Var1') %>%
  as.matrix()

mat
#  a b  c  d e
#a 1 0  0  0 0
#b 2 1  0  0 0
#c 3 6  1  0 0
#d 4 7  9  1 0
#e 5 8 10 11 1

Since you have a symmetric matrix you can copy the lower triangular matrix to upper triangle.

mat[upper.tri(mat)] <- t(mat)[upper.tri(mat)]
mat

#  a b  c  d  e
#a 1 2  3  4  5
#b 2 1  6  7  8
#c 3 6  1  9 10
#d 4 7  9  1 11
#e 5 8 10 11  1

data

df <- data.frame (Var1  = c("a", "b", "c","d","e","b","c","d","e","c","d","e","d","e","e"),
                  Var2 = c("a","a","a","a","a","b","b","b","b","c","c","c","d","d","e"),
                  pre = c(1,2,3,4,5,1,6,7,8,1,9,10,1,11,1) )
user_39629140

Here is an option with igraph package

g <- graph_from_data_frame(df,directed = FALSE)
E(g)$pre <- df$pre
get.adjacency(g,attr = "pre")

which gives

  a b  c  d  e
a 1 2  3  4  5
b 2 1  6  7  8
c 3 6  1  9 10
d 4 7  9  1 11
e 5 8 10 11  1
user_121587570

Base R solution (using data provided by Ronak):

# Crosstab: 
mdat <- as.data.frame.matrix(xtabs(pre ~ Var1 + Var2, df))

# Reflect on the diag (thanks @Ronak Shah):
mdat[upper.tri(mdat)] <- t(mdat)[upper.tri(mdat)]

As @ThomasIsCoding points as well we can use this one-liner:

xtabs(pre ~ ., unique(rbind(df, cbind(setNames(rev(df[-3]), names(df)[-3]), df[3] ))))

As @thelatemail points out we can also:

xtabs(pre ~ ., unique(data.frame(Map(c, df, df[c(2,1,3)]))))
user_92031580

Here's a base R version:

df <- data.frame (Var1  = c("a", "b", "c","d","e","b","c","d","e","c","d","e","d","e","e"),
                  Var2 = c("a","a","a","a","a","b","b","b","b","c","c","c","d","d","e"),
                  pre = c(1,2,3,4,5,1,6,7,8,1,9,10,1,11,1))

# Generate new matrix/data frame
mat2 <- matrix(0, length(unique(df$Var1)), length(unique(df$Var2)))

# Name the columns and rows so we can access values
rownames(mat2) <- unique(df$Var1)
colnames(mat2) <- unique(df$Var2)

# Save values into appropriate places into data frame
mat2[as.matrix(df[, 1:2])] <- as.matrix(df[, 3])

# Using upper triangle trick from @Ronak Shah's answer
mat2[upper.tri(mat)] <- t(mat2)[upper.tri(mat2)]

# See results
mat2
#   a b  c  d  e
# a 1 2  3  4  5
# b 2 1  6  7  8
# c 3 6  1  9 10
# d 4 7  9  1 11
# e 5 8 10 11  1
user_68731330
Related