通过循环从数据帧中填充矩阵

Question

基于数据集(请参阅“输入”)，我想创建以下矩阵：

> A
   BM CC  E  F  I  S CnC D
BR  1  2  3  4  5  6 NA NA
MX  7  8 11 12 13 14  9 10

输入：

cntry <- c("BR", "BR", "BR", "BR", "BR", "BR", "MX", "MX", "MX","MX", "MX", "MX", "MX", "MX")
sector <- c("BM", "CC", "E", "F", "I", "S","BM", "CC", "CnC", "D", "E", "F", "I", "S")
ratio <- seq(1, 14)
data <- data.frame(cbind(cntry, sector, ratio)

> data
   cntry sector ratio
1     BR     BM     1
2     BR     CC     2
3     BR      E     3
4     BR      F     4
5     BR      I     5
6     BR      S     6
7     MX     BM     7
8     MX     CC     8
9     MX    CnC     9
10    MX      D    10
11    MX      E    11
12    MX      F    12
13    MX      I    13
14    MX      S    14

我尝试了以下几点，但无法继续下去：

cntry_n <- nrow(cntry) # number of countries (here: 2), duplicates were removed
sector_n <- nrow(sector) # number of sectors(here: 8), duplicates were removed

A <- matrix(0, cntry_n, sector_n) # Zero was used as a default
rownames(A) <- cntry 
colnames(A) <- sector

> A
   BM CC  E  F  I  S CnC D
BR  0  0  0  0  0  0   0 0
MX  0  0  0  0  0  0   0 0

for(m in 1:cntry_n){
  for(n in 1:sector_n){
    A[m, n] = ????
  }
}

是否有一个有效的方法来解决这样的问题？

任何帮助都是非常感谢的！

Answer 1

你可以试试：

library(tidyr)
data2 <- pivot_wider(data,names_from = sector,values_from = ratio)

# A tibble: 2 x 9
  cntry BM    CC    E     F     I     S     CnC   D    
  <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 BR    1     2     3     4     5     6     <NA>  <NA> 
2 MX    7     8     11    12    13    14    9     10  

Answer 2

如果您想要一个table对象，可以使用xtabs()。

tab <- xtabs(ratio ~ cntry + sector, data)

#      sector
# cntry BM CC CnC  D  E  F  I  S
#    BR  1  2   0  0  3  4  5  6
#    MX  7  8   9 10 11 12 13 14

在使用上面的代码之前，请确保ratio是数字的。

要将表转换为data.frame，可以使用as.data.frame.matrix()。

as.data.frame.matrix(tab)

#    BM CC CnC  D  E  F  I  S
# BR  1  2   0  0  3  4  5  6
# MX  7  8   9 10 11 12 13 14