按二元ID将数值填充到矩阵的空单元格

Question

我正在处理一些双边贸易数据，每一行都包含出口商和进口商的ID以及它们的贸易额。然后，我希望将每行的交易金额映射到矩阵对象中的相应单元格上，该矩阵对象的I为"exporter“和"importer”，分别列为" row“和"column”dimnames。

我想知道做这件事更简单的方法是什么？下面是我当前的工作代码。

​

# import data
mat <- readRDS(url("https://www.dropbox.com/s/aj1607s975c5gf6/mat.rds?dl=1"))

head(mat, 10)

# import ID
id <- readRDS(url("https://www.dropbox.com/s/6weala2j0idb16i/id.rds?dl=1"))

# create matrix (there are a total of 161 possible IDs though not all of them appear on the data)

matrix <- matrix(rep( 0, len=161*161), nrow = 161)

dimnames(matrix) <- list(unique(id), unique(id))


# how can I fill the trade value (in mat[, 3]) into the corresponding cell on the matrix by match mat[, 1] and mat[, 3] on the dimnames(matrix)?

​

Answer 1

尝试使用来自tidyr的complete和pivot_wider。

library(tidyr)

mat %>%
  complete(pid = unique(id), rid = unique(id)) %>%
  pivot_wider(names_from = pid, values_from = TradeValue)