I currently face issues transforming the matrix to the format below. How do I achieve this in R in the easiest way? Ideally, I would like to use the second matrix as a data frame. Many thanks in advance!
Estonia Germany Poland
Estonia 0 2 3
Germany 2 0 4
Poland 3 4 0
Country1 Country2 Weight
Estonia Estonia 0
Estonia Germany 2
Estonia Poland 3
Germany Estonia 2
...
If the matrix is called mat you can use :
library(tibble)
library(tidyr)
mat %>%
as.data.frame() %>%
rownames_to_column('Country1') %>%
pivot_longer(cols = -Country1, names_to = 'Country2', values_to = 'Weight')
# Country1 Country2 Weight
# <chr> <chr> <int>
#1 Estonia Estonia 0
#2 Estonia Germany 2
#3 Estonia Poland 3
#4 Germany Estonia 2
#5 Germany Germany 0
#6 Germany Poland 4
#7 Poland Estonia 3
#8 Poland Germany 4
#9 Poland Poland 0
data
mat <- structure(c(0L, 2L, 3L, 2L, 0L, 4L, 3L, 4L, 0L), .Dim = c(3L,
3L), .Dimnames = list(c("Estonia", "Germany", "Poland"), c("Estonia",
"Germany", "Poland")))
Another, non-tidyverse, way:
df <- as.data.frame(as.table(mat))
names(df) <- c("Country1", "Country2", "Weight")
df
# Country1 Country2 Weight
# 1 Estonia Estonia 0
# 2 Germany Estonia 2
# 3 Poland Estonia 3
# 4 Estonia Germany 2
# 5 Germany Germany 0
# 6 Poland Germany 4
# 7 Estonia Poland 3
# 8 Germany Poland 4
# 9 Poland Poland 0
We can just use melt
library(reshape2)
melt(mat)
mat <- structure(c(0L, 2L, 3L, 2L, 0L, 4L, 3L, 4L, 0L), .Dim = c(3L,
3L), .Dimnames = list(c("Estonia", "Germany", "Poland"), c("Estonia",
"Germany", "Poland")))
Another base R option
> cbind(expand.grid(dimnames(mat)), Weight = c(mat))
Var1 Var2 Weight
1 Estonia Estonia 0
2 Germany Estonia 2
3 Poland Estonia 3
4 Estonia Germany 2
5 Germany Germany 0
6 Poland Germany 4
7 Estonia Poland 3
8 Germany Poland 4
9 Poland Poland 0
