Expand data.table with combinations of two columns if condition is specified in another column

Question

I have data.tableone that gives me connections between locations ( originand destination) for different bus routes ( route_id).

library(data.table)
library(magrittr)

# data for reproducible example
  dt <- data.table( origin = c('A','B','C', 'F', 'G', 'H'), 
                    destination = c('B','C','D', 'G', 'H', 'I'),
                    freq = c(2,2,2,10,10,10),
                    route_id = c(1,1,1,2,2,2), stringsAsFactors=FALSE )
# > dt
#    origin destination freq route_id
# 1:      A           B    2        1
# 2:      B           C    2        1
# 3:      C           D    2        1
# 4:      F           G   10        2
# 5:      G           H   10        2
# 6:      H           I   10        2

For the purposes of what I would like to do, if there is route_idone that gives the connection A-Band the connection B-C, then I want to add a connection to the data A-Cfor the same route_id, etc.

The problems . So far I have created simple code that does this work, but:

• it uses for loop, which takes a lot of time (my real data contain hundreds of thousands of observations).
• . . , B-C, C-B.

 # loop
   # a) get a data subset corresponding to each route_id
   # b) get all combinations of origin-destination pairs 
   # c) row bind the new pairs to original data
   for (i in unique(dt$route_id)) {
               temp <- dt[ route_id== i,]
               subset_of_pairs <- expand.grid(temp$origin, temp$destination) %>% setDT()
               setnames(subset_of_pairs, c("origin", "destination"))
               dt <- rbind(dt, subset_of_pairs, fill=T)
               }

# assign route_id and freq to new pairs
  dt[, route_id := route_id[1L], by=origin]
  dt[, freq := freq[1L], by=route_id]

# Keepe only different pairs that are unique
  dt[, origin := as.character(origin) ][, destination := as.character(destination) ]
  dt <- dt[ origin != destination, ][order(route_id, origin, destination)]
  dt <- unique(dt)

    origin destination freq route_id
 1:      A           B    2        1
 2:      A           C    2        1
 3:      A           D    2        1
 4:      B           C    2        1
 5:      B           D    2        1
 6:      C           D    2        1
 7:      F           G   10        2
 8:      F           H   10        2
 9:      F           I   10        2
10:      G           H   10        2
11:      G           I   10        2
12:      H           I   10        2

Answer 1

:

res = dt[, {
  stops = c(origin, last(destination))
  pairs = combn(.N + 1L, 2L)
  .(o = stops[pairs[1,]], d = stops[pairs[2,]])
}, by=route_id]

    route_id o d
 1:        1 A B
 2:        1 A C
 3:        1 A D
 4:        1 B C
 5:        1 B D
 6:        1 C D
 7:        2 F G
 8:        2 F H
 9:        2 F I
10:        2 G H
11:        2 G I
12:        2 H I

, c(origin, last(destination)) - . dt , .

vars dt, , res[dt, on=.(route_id), freq := i.freq].

, , . OP , 341 , 1e6/341*choose(341,2)= 170 . , .

---

, data.table , :

DT[, { 
  ...
}, by=g]

:

• , ..., .
• .
• .N, .SD, .GRP .BY .() list().

pairs , 1.. #stops (=.N + 1 .N - , _id). , ; . ... ; list() .().

---

, combn . # , , :

Ns = dt[,.N, by=route_id][, unique(N)]
cb = lapply(setNames(,Ns), combn, 2)

pairs = cb[[as.character(.N)]] . , pairs, memoization, .