Requirements
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.
library(magrittr)
library(data.table)
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]]
return(codes.a[current.code,])
}) %>% 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]
Questions
- How do I get the vectorized solution to work properly (
codes.a
should be the same ascodes.b
)? - 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?
- 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")