update matrix element by looping through a data.frame

Question

I have a data.frame which entails origin-destination flows:

#od flows data.frame with trips per year as flows
set.seed(123)
origin <- c(rep(1,3),rep(2,3),rep(3,3))
destination <- c(rep(1:3,3))
flow <- c(runif(9, min=0, max=1000))
od_flows <- data.frame(origin,destination,flow)

# od matrix with all possible origins and destinations
od_flows_all_combos <- matrix(0,10,10)

od_flows
od_flows_all_combos

> od_flows
  origin destination     flow
1      1           1 287.5775
2      1           2 788.3051
3      1           3 408.9769
4      2           1 883.0174
5      2           2 940.4673
6      2           3  45.5565
7      3           1 528.1055
8      3           2 892.4190
9      3           3 551.4350
> od_flows_all_combos
      [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10]
 [1,]    0    0    0    0    0    0    0    0    0     0
 [2,]    0    0    0    0    0    0    0    0    0     0
 [3,]    0    0    0    0    0    0    0    0    0     0
 [4,]    0    0    0    0    0    0    0    0    0     0
 [5,]    0    0    0    0    0    0    0    0    0     0
 [6,]    0    0    0    0    0    0    0    0    0     0
 [7,]    0    0    0    0    0    0    0    0    0     0
 [8,]    0    0    0    0    0    0    0    0    0     0
 [9,]    0    0    0    0    0    0    0    0    0     0
[10,]    0    0    0    0    0    0    0    0    0     0

I would like to update the od_flows_all_combos matrix with values of the od_flows data.frame such that origin values (in df) equal column numbers (in matrix) and destination values (in df) equal rows in the matrix. For example:

Update od_flows_all_combos[1,1] with 287.5775 and so on for all rows in df.

I would like to "loop" over the data.frame od_flows by rows and thereby use an apply-function. This is just an example. My actual od_flow data.frame has dim (1'200'000 x 3) and the matrix (2886x2886). So I need an efficient approach to this problem.

My first approach was this:

for(i in 1:nrow(od_flows)){
  od_flows_all_combos[rownames(od_flows_all_combos)==od_flows[i,2],colnames(od_flows_all_combos)==od_flows[i,1]] <- od_flows[i,3]
  }

Calculation hasn't ended yet...

Could someone help me with a solution using an apply function?

Thank you!

Adelmo Filho Adelmo Filho · Accepted Answer · 2017-04-18T17:32:22

You can organize the od_flows dataframe as a matrix directly, assuming od_flows completly fills your_desired_matrix

require(dplyr)

set.seed(123)
origin <- c(rep(1,3),rep(2,3),rep(3,3))
destination <- c(rep(1:3,3))
flow <- c(runif(9, min=0, max=1000))
od_flows <- data.frame(origin,destination,flow)

od_flows_order = od_flows %>% arrange(origin, destination)

your_desired_matrix = matrix(od_flows_order$flow, ncol = 3, byrow = TRUE)

your_desired_matrix 

        [,1]     [,2]     [,3]
[1,] 287.5775 788.3051 408.9769
[2,] 883.0174 940.4673  45.5565
[3,] 528.1055 892.4190 551.4350

update matrix element by looping through a data.frame

1 Answers