In R, How can I create a new date variable adopting the nearest date value right after an index date variable?

user_18741614 0
Viewed 60

My dataframe in R studio is as follows:

StudyID FITDate.1 ScopeDate.1 ScopeDate.2 ScopeDate.3 ScopeDate.4
1 2014-05-15 2010-06-02 2014-05-28 2014-08-01 2015-10-27
2 2017-11-29 2018-02-27     
3 2015-10-04 2016-06-24 2017-01-18

I have a variable "FITDate.1" indicates the date for FIT test, and several variables "ScopeDate.x" indicates the dates for multiple scope tests.

In my research, a person can have only one date for FIT test, but can have multiple dates for scope. Clinically, if a person has a FIT test, then he will be referred to undertake scope test. However, this person may receive scope tests for other reasons.

So if the date of a scope test is right after the date of a FIT test, then we will define them highly related.

I want to create a variable "FITrelatedscopedate" to include the dates of FIT related scopes. For example, in the row of StudyID==1, the date of "FITDate.1"is 2014-05-15, which is right between ScopeDate.1 (2010-06-02) and ScopeDate.2 (2014-05-28). So the date value 2014-05-28 of ScopeDate.2 is what i need, and I will use 2014-05-28 as the FIT related scope date and write it in the new variable "FITrelatedscopedate".

I think I have to use loop syntax, but i had no experience to realize it. Do you have any experience to solve similar problem? Do you know any codes to realize it? Thanks, any help are appreciated.

r date loops
1 Answers

Here is one approach with tidyverse assuming you start with two long data.frames, one for FIT testing, and the other for endoscopy.

df_fit <- data.frame(
  StudyID = 1:3,
  FITDate = as.Date(c("2014-05-15", "2017-11-29", "2015-10-04"))
)
df_fit

  StudyID    FITDate
1       1 2014-05-15
2       2 2017-11-29
3       3 2015-10-04

df_scope <- data.frame(
  StudyID = c(1,1,1,1,2,3,3),
  ScopeDate = as.Date(c("2010-06-02", "2014-05-28", "2014-08-01", "2015-10-27", "2018-02-27", 
                        "2016-06-24", "2017-01-18"))
)
df_scope

  StudyID  ScopeDate
1       1 2010-06-02
2       1 2014-05-28
3       1 2014-08-01
4       1 2015-10-27
5       2 2018-02-27
6       3 2016-06-24
7       3 2017-01-18

First, you can do a left_join by the StudyID to add the scope dates to the FIT data. Then, you can filter to only keep scope dates after FIT testing. For each StudyID, use slice to retain only the first row (this assumes dates are in chronological order...if not, add arrange(ScopeDate) first in the pipe - let me know if you need help with this).

Then, you can right_join back to df_fit so that those FIT testing dates without endoscopy will have NA for the ScopeDate. The final statement with mutate will calculate the time duration between endoscopy and FIT testing.

library(tidyverse)

left_join(
  df_fit,
  df_scope,
  by = "StudyID"
) %>%
  filter(ScopeDate > FITDate) %>%
  group_by(StudyID) %>%
  slice(1) %>%
  right_join(df_fit) %>%
  mutate(Duration = ScopeDate - FITDate)

Output

  StudyID FITDate    ScopeDate  Duration
    <dbl> <date>     <date>     <drtn>  
1       1 2014-05-15 2014-05-28  13 days
2       2 2017-11-29 2018-02-27  90 days
3       3 2015-10-04 2016-06-24 264 days

Let me know if this works for you. A data.table approach can be considered if you need something faster and have a very large dataset.

If you need the Duration as a numeric column, you can use as.numeric(ScopeDate - FITDate).

user_34606700
Related