r - 如何用数据框值填充空矩阵

Question

我拼命地试图用数据框中的值填充矩阵。
它是交易数据，所以数据框看起来像这样:

country1 country2 value
1 Afghanistan  Albania    30
2 Afghanistan  Albania    81
3 Afghanistan    China     5
4     Albania  Germany     6
5       China  Germany     8
6       China   Turkey   900
7     Germany   Turkey    12
8     Germany      USA     3
9     Germany   Zambia   700

使用 unique 和 sort 命令，我创建了 df 中出现的所有国家/地区的列表(并将其转换为矩阵):

     countries_sorted
[1,] "Afghanistan"   
[2,] "Albania"       
[3,] "China"         
[4,] "Germany"       
[5,] "Turkey"        
[6,] "USA"           
[7,] "Zambia"    

使用这个“列表”，我创建了一个空的交易矩阵(7x7):

             Afghanistan Albania China Germany Turkey USA Zambia
Afghanistan          NA      NA    NA      NA     NA  NA     NA
Albania              NA      NA    NA      NA     NA  NA     NA
China                NA      NA    NA      NA     NA  NA     NA
Germany              NA      NA    NA      NA     NA  NA     NA
Turkey               NA      NA    NA      NA     NA  NA     NA
USA                  NA      NA    NA      NA     NA  NA     NA
Zambia               NA      NA    NA      NA     NA  NA     NA

我现在无可救药地无法用 df 的值列中的数字/总和填充这个矩阵。
我试过这样的事情:

a<-cast(df, country1~country2 , sum)

这在一定程度上起作用，但矩阵不保留其原始的 7x7 格式，这就是我需要一个对角线全为 0 的矩阵。

> a
     country1 Albania China Germany Turkey USA Zambia
1 Afghanistan     111     5       0      0   0      0
2     Albania       0     0       6      0   0      0
3       China       0     0       8    900   0      0
4     Germany       0     0       0     12   3    700

请问大家有解决办法吗？？？

Answer 1

从这两个数据集开始:

#your data.frame
df <- read.table(header=T, file='clipboard', stringsAsFactors = F)
#the list of unique countries
countries <- unique(c(df$country1,df$country2))

你可以这样做:

#create all the country combinations
newdf <- expand.grid(countries, countries)
#change names
colnames(newdf) <- c('country1', 'country2')
#add a value of 0 for the new combinations (won't affect outcome)
newdf$value <- 0
#row bind with original dataset
df2 <- rbind(df, newdf)


#and create the table using xtabs:
#the aggregate function will create the sum of the value for each combination
> xtabs(value ~ country1 + country2, aggregate(value~country1+country2,df2,sum))
             country2
country1      Afghanistan Albania China Germany Turkey USA Zambia
  Afghanistan           0     111     5       0      0   0      0
  Albania               0       0     0       6      0   0      0
  China                 0       0     0       8    900   0      0
  Germany               0       0     0       0     12   3    700
  Turkey                0       0     0       0      0   0      0
  USA                   0       0     0       0      0   0      0
  Zambia                0       0     0       0      0   0      0