Populate matrix from a data frame via a loop

Question

Based on a dataset (see "Input"), I would like to create the following matrix:

> 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

Input:

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

I tried the following but was unable to proceed further:

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] = ????
  }
}

Is there an efficient way do solve such a problem?

Any help is really appreciated!

score 1 · Accepted Answer · answered Jul 16 '20 at 11:20

1

You can try:

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

answered Jul 16 '20 at 11:20

Duck

39,058
13
42
84

@MartinGal Yeah, do you know why that happened? I thought I did something wrong. – Duck Jul 16 '20 at 13:38
@Duck I don't know. Perhaps someone thought "well... that's not a matrix, so I downvote!!!!111" or that person didn't like a `tidyr` solution. In my opinion that solution answers OPs question. – Martin Gal Jul 16 '20 at 13:46
@MartinGal Yeah very weird behavior! – Duck Jul 16 '20 at 13:47

Darren Tsai · Answer 2 · 2020-07-18T08:52:15.400

If you want a table object, you can use 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

Make sure that ratio is numeric before using the code above.

To convert the table to a data.frame, you can use 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

Populate matrix from a data frame via a loop

2 Answers2