R: Using values from data frame A from a date prior to populate a row in data frame B

Question

This may be very complicated and I suspect requires advanced knowledge. I have now two different types of data.frames I need to combine:

The data:

Dataframe A:

lists all transfusion dates by patient ID. Every transfusion is represented by a separate row, patients can have multiple transfusions. Different patients can have transfusions on the same date.

Patient ID Transfusion.Date
1          01/01/2000
1          01/30/2000
2          04/01/2003
3          04/01/2003

Dataframes of Type B contain test results at other dates, also by patient ID:

Patient ID  Test.Date   Test.Value
1           11/30/1999   negative
1           01/15/2000   700 copies/uL
1           01/27/2000   900 copies/uL
2           03/30/2003   negative

What I would like to have is Dataframe A with the same number of rows (1 for each transfusion), and with the most recent Test.Value as a separate column. Each transfusion date should have the test result from the test performed most closely (prior) to the transfusion.

desired output:

-->

Patient ID Transfusion.Date Pre.Transfusion.Test
1          01/01/2000       negative
1          01/30/2000       900 copies/ul
2          04/01/2003       negative
3          04/01/2003       NA

I think the general strategy would be to subset the data.frames by patient IDs. Then take all transfusion dates for patient 1, check which result is closest to all available test_dates for each element and then return the value closest.

How can I explain R to do that?

Edit 1: Here is the R-code for these examples

df_A <- data.frame(MRN = c(1,1,2,3), 
                   Transfusion.Date = as.Date(c('01/01/2000', '01/30/2000', 
                   '04/01/2003','04/01/2003'),'%m/%d/%Y')) 

df_B <- data.frame(MRN = c(1,1,1,2), 
                   Test.Date = as.Date(c('11/30/1999', '01/15/2000', '01/27/2000', 
                   '03/30/2003'),'%m/%d/%Y'), Test.Result = c('negative', 
                   '700 copies/ul','900 copies/ul','negative'))

Edit 2:

To clarify, the resulting data should be: Patient A received transfusions on Day X and Day Y. (for df_A). Prior to the transfusion on day X, his most recent test result was X (closest test date to first transfusion, in df_B). Prior to the transfusion on day Y, his most recent test result was Y (prior to the second transfusion, also in df_B. df_B also contains a bunch of other test dates, which are not needed for the final output.

Answer 1

Here's using data.table's rolling joins:

require(data.table)
setkey(setDT(df_A), MRN, Transfusion.Date)
setkey(setDT(df_B), MRN, Test.Date)

df_B[df_A, roll=TRUE]
#    MRN  Test.Date   Test.Result
# 1:   1 2000-01-01      negative
# 2:   1 2000-01-30 900 copies/ul
# 3:   2 2003-04-01      negative
# 4:   3 2003-04-01            NA

• setDT converts data.frame to data.table by reference (without any additional copying). That'll result in df_A and df_B now being data.tables.

• setkey sorts the data.table by the columns we provided, and marks those columns as key columns, which allows us to use binary search based joins.

• We perform a join of the form x[i] on the key columns, where for each row of i, the matching rows of x (if any, else NA) along with i's rows are returned. This is what we call an equi-join. By adding roll = TRUE, in the event of a mismatch, the last observation is carried forward (LOCF). This is what we call a rolling join. The sorting in increasing order (due to setkey()) ensures that the last observation is the most recent date.

HTH

Answer 2

 dfLast <- df_B[ df_B$Test.Date %in% 
  as.Date( tapply(df_B$Test.Date, df_B$MRN, tail,1),"1970-01-01"), ]
 merge(df_A, dfLast, by=c(1:2,1:2) ,all.y=TRUE)
  MRN Transfusion.Date   Test.Result
1   1       2000-01-27 900 copies/ul
2   2       2003-03-30      negative

Edited. Had some logical errors and some sytactic errors. tapply returned the integer values of the Dates and as you pointed out I was using the wrong column name in the data reduction step.

Answer 3

OK thanks for everyone's help. It took me a lot of toil, blood, sweat, and tears, but this is the solution I came up with:

1. Merge both data frames:

df_AB <- merge(df_A, df_B, all.x = T)

df_AB:

  MRN Transfusion.Date  Test.Date   Test.Result
1   1       2000-01-01 1999-11-30      negative
2   1       2000-01-01 2000-01-15 700 copies/ul
3   1       2000-01-01 2000-01-27 900 copies/ul
4   1       2000-01-30 1999-11-30      negative
5   1       2000-01-30 2000-01-15 700 copies/ul
6   1       2000-01-30 2000-01-27 900 copies/ul
7   2       2003-04-01 2003-03-30      negative
8   3       2003-04-01       <NA>          <NA>

Using dplyr

df_tests <- df_AB %>% 
  group_by(MRN, Transfusion.Date) %>%
  mutate(Time.Difference = Transfusion.Date - Test.Date) %>%
  filter(Time.Difference > 0) %>%
  arrange(Time.Difference) %>%
  summarize(Test.Date = Test.Date[1], Test.Result = Test.Result[1])

df_tests:

  MRN Transfusion.Date  Test.Date Test.Result
1   1       2000-01-01 1999-11-30    negative
2   1       2000-01-30 1999-11-30    negative
3   2       2003-04-01 2003-03-30    negative

using merge again for MRN3:

df_desired <- merge(df_A, df_tests, all.x = T)

  MRN Transfusion.Date  Test.Date   Test.Result
1   1       2000-01-01 1999-11-30      negative
2   1       2000-01-30 2000-01-27 900 copies/ul
3   2       2003-04-01 2003-03-30      negative
4   3       2003-04-01       <NA>          <NA>