I have a table of the different diagnoses a patient has received, with the date the diagnosis was first recorded. It so happens that in medicine, diagnoses are recorded as codes from a huge list called ICD10, and sometimes several different codes are related to a single disease, for example a patient will get two separate rows with code A for a cataract and code B for impaired vision. A domain expert I am working with wants to see the table displayed by combinations, and has supplied me with a list of pairwise valid combinations.

I am interested in the code having good performance, since this whole thing is wrapped in a function that gets called repeatedly for many thousands of patients. I also want to solve it with data.table, since I am using it heavily in other parts of my code, and I prefer to keep everything consistent. Also, I think that the lookup table with code pairs should be "clean" in the sense of never having a rule twice, even if the order is inverted (such as containing both the A, B and the B, A combinations), but I would prefer a solution that is robust and works even if such repeated rules occur.

MWE test case

To give you an MWE where the codes are replaced with capital letters, I am starting with a table like this:

    code       earliest.year 
    A          2001     
    B          2002     
    C          2003     
    D          2004     
    E          2005     
    F          2006     
    G          2007     
    H          2008      

and a table to look up valid combinations:

  first second
     A      B
     A      C
     C      H
     D      G
     E      I

and want the output to look like this:

   code.combination earliest.year
         A, B, C, H          2001
               D, G          2004
                  E          2005
                  F          2006

My solution

I first tried doing it with vectorization, but got a list-formatted output from apply and couldn't convert the results back to a usable data.table. So I repeated it with a for loop and it worked.


codes <- data.table(code=LETTERS[1:8], earliest.year=c(2001:2008), group=0)
setkey(codes, code)
valid.combinations <- data.frame(first=c("A", "A", "C", "D", "E"), second=c("B", "C", "H", "G", "I"))

# solution A - vectorized
codes.a <- copy(codes)
codes.a <- sapply(codes.a$code, function(current.code) {
  if(codes.a[current.code, group==0]) codes.a <- codes.a[current.code, group:=max(codes.a$group)+1]
  codes.a <- codes.a[code %in% valid.combinations[valid.combinations$first==current.code, "second"], group:=codes.a[current.code, group]]
}) %>% t %>% data.table # First, the need for transposing it feels wrong. Second, this gives me a data.table whose columns are all lists, and I get all kinds of errors when trying to work with it. 

# Solution B - loop
codes.b <- copy(codes)
for(current.code in codes.b$code) {
  if(codes.b[current.code, group==0]) codes.b[current.code, group:=max(codes.b$group)+1]
  codes.b <- codes.b[code %in% valid.combinations[valid.combinations$first==current.code, "second"], group:=codes.b[current.code, group]]

codes.combined <- codes.b[,.(code.combination=paste(code, collapse=", "), earliest.year=min(earliest.year)),by=group]


  1. How do I get the vectorized solution to work properly (codes.a should be the same as codes.b)?
  2. Generally, vectorization is supposed to have better performance in R. Can I also expect it to give me better performance in this case, or does the casting into lists and re-casting into data.table negate the performance advantages?
  3. Is there a better solution to the problem as a whole, and if yes, what is it?

(I am interested to hear answers to 1 and 2 even if the answer to 3 is "yes")



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.