Make a matrix of origins and destinations

Question

I need to fill a table with origins and destinations so that each cell contains the total number of people leaving from one location to another.

The database has an id for each observation, as well as the source and destination. Each observation has a "weight" that corresponds to a number of people for that observation. Follow an example:

# Banco de dados de exemplo:
localidades <- data.frame(id = c(1, 2, 3, 4, 5, 6),
                          Peso_id = c(5, 5, 10, 10, 10, 7),
                          Origem = factor(c("A", "A", "B", "B", "B", "C"),
                                      levels = c("A", "B", "C", "D")),
                          Destino = factor(c("C", "D", "A", "C", "A", "D"),
                                       levels = c("A", "B", "C", "D"))
                         )

localidades
#>   id Peso_id Origem Destino
#> 1  1       5      A       C
#> 2  2       5      A       D
#> 3  3      10      B       A
#> 4  4      10      B       C
#> 5  5      10      B       A
#> 6  6       7      C       D

Matrix of origins and destinations?

If I use the function table() as I do below, only the number of observations will be counted (id) instead of the number of persons (ex: sum Peso_id) for each pair of localities:

minha_tabela <- table(localidades$Origem , localidades$Destino)

minha_tabela
#>    
#>     A B C D
#>   A 0 0 1 1
#>   B 2 0 1 0
#>   C 0 0 0 1
#>   D 0 0 0 0

However I need to be considered in the account the Peso_id at the time of assembling the table. At the end I want to get this information:

minha_tabela_OD
#>    
#>     A   B  C  D
#>   A 0   0  5  5
#>   B 20  0  10 0
#>   C 0   0  0  7
#>   D 0   0  0  0

How do you do?

Answer 1

You can use the function xtabs():

minha_tabela_OD <- xtabs(localidades$Peso_id ~ localidades$Origem + localidades$Destino)

minha_tabela_OD
#>               localidades$Destino
#> localidades$Origem  A  B  C  D
#>                  A  0  0  5  5
#>                  B 20  0 10  0
#>                  C  0  0  0  7
#>                  D  0  0  0  0