Sibling network using data.table

I would like to create a sibship network using data.table

.

My data looks like

indata <-
structure(list(id = c(1L, 2L, 3L, 4L, 12L, 13L, 14L, 15L), fid = c(NA, 
9L, 1L, 1L, 7L, 5L, 5L, 5L), mid = c(0L, NA, 2L, 2L, 6L, 6L, 
6L, 8L)), .Names = c("id", "fid", "mid"), class = "data.frame", row.names = 
c(NA, -8L))

      

        
        
        
      

    

which the

  id fid mid
1  1  NA   0
2  2   9  NA
3  3   1   2
4  4   1   2
5 12   7   6
6 13   5   6
7 14   5   6
8 15   5   8

      

        
        
        
      

    

The three columns represent the id, id of the father, and id of the mother, respectively. 0

or NA

means it is not available. So in the above data, person 3 and 4 are full brothers and sisters (they both have a father 1

and mother 2

), and 12 and 13 are half a brother (they have different fathers, but the same mother, 6

).

For each line in the dataframe, I need a list of siblings (let's just consider half of the siblings). My ideal end result would be

  id fid mid sibs
1  1  NA   0 NA
2  2   9  NA NA
3  3   1   2 4
4  4   1   2 3
5 12   7   6 13, 14
6 13   5   6 12, 14, 15
7 14   5   6 12, 13, 15
8 15   5   8 13, 14

      

        
        
        
      

    

where the last column sibs

is a list or a vector (and it doesn't have to be part of the dataset).

A rough version for getting the result using R base is given below

# get a list of offspring for each father id
foffspring <- by(indata, indata$fid, function(i) { i$id }, simplify=FALSE) 
# and mother id
moffspring <- by(indata, indata$mid, function(i) { i$id }, simplify=FALSE)

      

        
        
        
      

    

To get siblings to go through each id. Find their father and mother and combine the two matching entries from the previous lists.

sibs <- sapply( 1:nrow(indata), function(i) {
    res <- c()
    if( !is.na(indata$fid[i]) ) 
        res <- c(res, unlist(foffspring[paste0(indata$fid[i])]))
    if( !is.na(indata$mid[i]) ) 
        res <- c(res, unlist(moffspring[paste0(indata$mid[i])]))
    unique(res[res != indata$id[i]]) 
    }, simplify=TRUE )

      

        
        
        
      

    

This creates

> sibs
[[1]]
integer(0)

[[2]]
integer(0)

[[3]]
[1] 4

[[4]]
[1] 3

[[5]]
[1] 13 14

[[6]]
[1] 14 15 12

[[7]]
[1] 13 15 12

[[8]]
[1] 13 14

      

        
        
        
      

    

which was the desired outlet. Now the code above is not fast or pretty and I would really like to know if I can get a fantastic version data.table

. However, it seems to me that data.table

-fu is missing.

library(data.table)
DT <- data.table(indata)
# Create lists with the _indices_ of the offsprings
FT <- DT[ , list( yidx = list(.I) ) , by = fid ]
MT <- DT[ , list( yidx = list(.I) ) , by = mid ]

      

        
        
        
      

    

MT

as follows

   mid  yidx
1:  NA     2
2:   0     1
3:   2   3,4
4:   6 5,6,7
5:   8     8

      

        
        
        
      

    

Exactly as moffspring

above, except that it contains indices and not labels. This, however, is not a problem. Then I would like to concatenate the tables together

setkey(DT, fid)
setkey(FT, fid)
setkey(MT, mid)

# Inner join
P1 <- DT[FT]

# And inner join on mother
setkey(P1, mid)
P1[MT]

      

        
        
        
      

    

and now the end result looks like this:

   id fid mid  yidx i.yidx
1:  2   9  NA     2      2
2:  1  NA   0     1      1
3:  3   1   2   3,4    3,4
4:  4   1   2   3,4    3,4
5: 13   5   6 6,7,8  5,6,7
6: 14   5   6 6,7,8  5,6,7
7: 12   7   6     5  5,6,7
8: 15   5   8 6,7,8      8

      

        
        
        
      

    

It's almost. Now if I accept string concatenation yidx

and i.yidx

then I end up with a list of half sibs (including myself) and intersecting the strings will result in a full sibling. Note that the indices here are relative to the index in DT

, not the final data.table

, but can also be corrected.

However ... I have a feeling that something like this can be done much more efficiently in a few lines of code data.table

and a "gentle wave of the hand". Can anyone point me in the right direction?

[Sorry for the super long entry]

Update based on answers below. Just for fun, I ran three different sentences through microbenchmark

to see if there would be any time differences between the three approaches. f1()

is @Frank's suggestion, f2()

is the solution given by @mtoto, and f3

is @amatsuo_net's approach. Tried on vectors of length 1000, but the output

Unit: milliseconds
 expr       min        lq      mean    median        uq      max neval cld
 f1() 4020.8112 4387.7950 4614.7896 4498.8043 4770.1184 6837.672   100   c
 f2()  656.9575  685.7706  727.5191  710.3003  735.2832 1080.423   100 a  
 f3() 1637.8927 1706.7528 1789.1794 1739.4428 1814.7776 2403.474   100  b 

      

        
        
        
      

    

Quite a significant difference in approaches. I need to run it across a dataset with 7 million IDs, which certainly has a noticeable impact. Thanks everyone!