Comparing dates in different columns to isolate certain within-group entries in R

Question

I have a data frame with an ID column that includes duplicates. There is a column called type that takes the values "S" or "N." There are two additional date columns - admission date and discharge date. My question is a bit similar to comparing two data frames and isolating rows based on certain date differences, but not quite. If needed, I could separate my data into two data frames, but I'm wondering if I can accomplish what I want without the extra steps.

Here is a small example of what the data for two patients looks like in R:

example <- data.frame(ID = c(22,22,22,52,52,52), 
admission_date = c("2013-10-03","2014-03-11","2014-03-16","2012-02-08","2014-06-10","2014-06-20"),
discharge_date = c("2013-10-11","2014-03-16","2014-03-28","2012-02-13","2014-06-12","2014-06-30"), 
type = c('S','S','N','S','S','N'))

What I want to do is compare within patients, entries that take the value "N" and entries that take the value "S" in the type variable. Based on the discharge date for entries with the value "S," I would like to find entries with the value "N" that have an admission date within 5 days of the former's discharge date (the discharge date with value "S" should be before the admission date with value "N").

So in the example data frame, the only two entries that should be retained are rows 2 and 3 and not rows 5 and 6 since the difference between admission date and discharge date is greater than 5.

Does anyone have any suggestions of how I can filter this data? Any help is greatly appreciated.

Answer 1

After attempting to filter within the same data frame, I decided to separate the data into two tables: one containing only data of type "S" and the other containing only data of type "N." Then, I did a full join while matching on the ID column. While this creates a greater number of rows than before, I was then able to compare the two date of interest. The resulting data frame contains only one row - the entry of a patient with an admission date with type "N" within 5 days of a discharge date with type "S."

The code in R is as follows:

library(dplyr)

example_df <- data.frame(ID = c(22,22,22,52,52,52), 
                         admission_date = c("2013-10-03","2014-03-11","2014-03-16","2012-02-08","2014-06-10","2014-06-20"),
                         discharge_date = c("2013-10-11","2014-03-16","2014-03-28","2012-02-13","2014-06-12","2014-06-30"), 
                         type = c('S','S','N','S','S','N'))
N_only <- example_df %>%
  filter(type == "N")
S_only <- example_df %>%
  filter(type == "S")

example_df_merged <- merge(N_only, S_only, by = "ID")
example_df_merged$admission_date.x <- as.Date(as.character(example_df_merged$admission_date.x), format="%Y-%m-%d")
example_df_merged$discharge_date.y <- as.Date(as.character(example_df_merged$discharge_date.y), format="%Y-%m-%d")
example_df_merged$dateDiff <- example_df_merged$discharge_date.y - example_df_merged$admission_date.x

example_df_final <- example_df_merged %>%
  filter(dateDiff <= 5 & dateDiff >= 0)

For clearer variable names, I would have changed the variables ending in ".x" and ".y," but that is not necessary.

Answer 2

This was an interesting challenge. One reason for this is because iterating over rows is less intuitive than iterating over columns (see this question for lots of suggestions: For each row in an R dataframe).

Now I know vectorized solutions are preferred over for loops, but one of the challenges with this problem was that instead of just performing functions on each row, we're comparing the iterated rows to other rows and deleting some rows as we go along. I expect there's a better solution out there and I hope someone posts a better solution to help me learn.

One minor note before I begin, "example" isn't a great name for an object because it's also a function in base R. Additionally, the solution is much easier if we're only dealing with alternating rows of "S" and "N" - that is if many S's precede an N then only the lowest S might be within 5 days of N. Nonetheless it was worth the effort to attack the more challenging case.

Ultimately I ended up solving this as a 2-stage problem, each solved with a for loop. First, I took out all the S rows which weren't within 5 days of the corresponding N rows. Then I took out those N rows which didn't have any appropriate S companions. All of this is implemented in base R.

So to begin:

    example_df <- data.frame(ID = c(22,22,22,52,52,52), 
                  admission_date = c("2013-10-03","2014-03-11","2014-03-16","2012-02-08","2014-06-10","2014-06-20"),
                  discharge_date = c("2013-10-11","2014-03-16","2014-03-28","2012-02-13","2014-06-12","2014-06-30"), 
                  type = c('S','S','N','S','S','N'))

    example_df$admission_date<-as.numeric(as.Date(example_df$admission_date))
    example_df$discharge_date<-as.numeric(as.Date(example_df$discharge_date))

The first thing I did was to take the date columns (which were characters) and convert them to numeric based on date. Originally I was doing mathematical operations with date objects, but this became complicated with the subsetting operations I ended up using.

Here's the first for loop:

   del_vec <- vector("integer")
   for( i in 1:nrow(example_df))  {
   if (example_df[i,"type"]== "S") {
   next
   }
   if (example_df[i,"type"] == "N") {
   add_on <- which
   (
   example_df["type"] == "S" & 
    example_df["ID"]==example_df[i,"ID"] &
     example_df["discharge_date"] < (example_df[i,"admission_date"] - 5)
     )
}

      del_vec<- append(del_vec,add_on)
 } 
   example_df_new <- example_df[-c(del_vec),]
   rownames(example_df_new) <- 1:nrow(example_df_new)
  example_df_new

What I did here is start by creating a vector which will contain the row numbers that we delete. To get rid of the inappropriate S rows we need to actually work on the N rows, so I have the loop skip the S rows. Then when the loop encounters an N row, we find the rows which meet the following conditions:

1. have type S
2. have the same ID as the N row in question
3. have a discharge date which is more than 5 days from the admission date for the N row in question

Using which()captures the row numbers that meet these criteria. Now I add these rows to the empty vector and remove them from the original df. I also rename the rows of the new df to get the following output for example_df_new

   ID    admission_date discharge_date type
 1 22          16140          16145    S
 2 22          16145          16157    N
 3 52          16241          16251    N

So we've preserved the 2 rows you wanted to keep, but now we have this bottom row that we want to get rid of. I do this in the second loop which iterates over the rows in the new reduced df:

    del_vec2 <- vector()
    for(i in 1:nrow(example_df_new)) {
    if (example_df_new[i,"type"]=="S") {
    next
     }
    if (example_df_new[i,"type"] == "N") {
    add_on_two <- which(example_df_new["type"] == "S" &    example_df_new["ID"] == example_df_new[i,"ID"])
    }
    if(length(add_on_two !=0)) {
    next
    } else {
    del_vec2 <- append(del_vec2,i)
     }
    }

    example_df_3<-example_df_new[-c(del_vec2),]
    example_df_3

Again, we tell the loop to skip the S rows — whichever ones made the first cut should stay in. Now when the loop encounters an N row we ask the loop to look for rows that meet the following criteria:

1. is type S
2. has the same ID as the N row in question

Again I use which() to save the positions of these rows. If these criteria are met then we skip ahead - we want to keep all the N's that have an appropriate S companion. If not then we add the row number of (i) - that is the row number for the N in question to our vector of rows that we want to delete.

We then delete those rows and end up with the desired output:

     ID     admission_date discharge_date type
   1 22          16140          16145       S
   2 22          16145          16157       N

At this point you can change the date columns back to a date format. Again, while this may be the first, I expect it's not the best solution. I hope to see an improved solution, but the problem is more tricky than it appears at first.