0

I'm working on some bilateral trade data with each row consisting of the ID for exporter and importer, as well as their trade amount. I then want to map the trade amount of each row onto its corresponding cell in a matrix object which has the IDs of "exporter" and "importer" listed as "row" and "column" dimnames.

I am wondering what will be an easier way to do this? Below is my current working code.

# 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)?
Chris T.
  • 1,699
  • 7
  • 23
  • 45

1 Answers1

0

Try with complete and pivot_wider from tidyr.

library(tidyr)

mat %>%
  complete(pid = unique(id), rid = unique(id)) %>%
  pivot_wider(names_from = pid, values_from = TradeValue)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Thanks for the heads up, but this code creates a 15605 by 165 dataframe (or tbl), not really a 161 by 161 matrix filled with trade values. – Chris T. Jul 20 '20 at 11:35
  • @ChrisT. If I use the above code for the dataset you shared I get 161 X 162 dataframe. 1st column is `rid` in it. Also all `id`s are present in `mat` already , so `mat %>% pivot_wider(names_from = pid, values_from = TradeValue)` returns the same thing. Do you get something else for the data shared? – Ronak Shah Jul 20 '20 at 11:54