Finding end-date of last sickness incident in R

Question

I have a file which records when someone was sick, and when they recovered. Some records have several incidents of sickness, and I am trying to add a new column to each row which is "date last sickness ended" (or "date record began" if NA). There are 10,000s of records, and there can be 10+ incidents of sickness. But in the simplest example, I am trying to go from this:

| ID      | RecordBegins | SickStartDate | SickEndDate |

| person1 | 1990-01-01 | 2017-03-04 | 2017-07-01 |

| person1 | 1990-01-01 | 2018-11-01 | 2019-02-04 |

to this:


| ID      | RecordBegins | SickStartDate | SickEndDate | EndLastSick

| person1 | 1990-01-01 | 2017-03-04 | 2017-07-01 | 1990-01-01

| person1 | 1990-01-01 | 2018-11-01 | 2019-02-04 | 2017-07-01

I'm new to R, and self-taught. I've been trying to stay in the tidyverse world, but am open to other approaches.

I can code for what I want when there's only one unique person in the dataset using the code below. I've tried extending it using group_by() when I expand the dataset to more than one person, but I can't think of a way to make it work.

sick <-tribble(
  ~ID, ~RecordBegins, ~SickStartDate, ~SickEndDate,
  "person1", as.Date("1990-01-01"), as.Date("2017-03-04"), as.Date("2017-07-01"),
  "person1", as.Date("1990-01-01"), as.Date("2018-11-01"), as.Date("2019-02-04"),
)

var1 <- sick$SickEndDate %>% sort(decreasing = TRUE) # place date of last sick at position [1]
var1[1] = sick$`RecordBegins`[1] # replace date of last sick with record start date
var1 <- sort(var1) # re-sort so in date order
sick <- mutate(sick, startExposure = var1)

However that feels pretty clunky, and I can't think of a way to get it to work for when scaling up to more than one person, for example:

sick <-tribble(
  ~ID, ~RecordBegins, ~SickStartDate, ~SickEndDate,
  "person1", as.Date("1990-01-01"), as.Date("2017-03-04"), as.Date("2017-07-01"),
  "person1", as.Date("1990-01-01"), as.Date("2018-11-01"), as.Date("2019-02-04"),
  "person2", as.Date("1995-01-01"), as.Date("2014-10-07"), as.Date("2017-01-04"),
  "person2", as.Date("1995-01-01"), as.Date("2017-11-01"), as.Date("2017-11-23"),
)

user_7978445 · Answer

You can group by ID, and make use of lag, if I'm interpreting your question correctly!

library(dplyr)

sick %>%
    arrange(ID, SickStartDate) %>%
    group_by(ID) %>%
    mutate(EndLastSick = case_when(
        # if this is the first record for this person, use RecordBegins
        is.na(lag(SickEndDate)) ~ RecordBegins,
        # otherwise, get the most recent SicKEndDate
        TRUE ~ lag(SickEndDate)
    ))

## A tibble: 4 x 5
## Groups:   ID [2]
#  ID      RecordBegins SickStartDate SickEndDate EndLastSick
#                               
#1 person1 1990-01-01   2017-03-04    2017-07-01  1990-01-01 
#2 person1 1990-01-01   2018-11-01    2019-02-04  2017-07-01 
#3 person2 1995-01-01   2014-10-07    2017-01-04  1995-01-01 
#4 person2 1995-01-01   2017-11-01    2017-11-23  2017-01-04

Data:

sick <-tribble(
  ~ID, ~RecordBegins, ~SickStartDate, ~SickEndDate,
  "person1", as.Date("1990-01-01"), as.Date("2017-03-04"), as.Date("2017-07-01"),
  "person1", as.Date("1990-01-01"), as.Date("2018-11-01"), as.Date("2019-02-04"),
  "person2", as.Date("1995-01-01"), as.Date("2014-10-07"), as.Date("2017-01-04"),
  "person2", as.Date("1995-01-01"), as.Date("2017-11-01"), as.Date("2017-11-23"),
)

user_3962914 · Answer

This is similar to @heds1's answer but using only lag function.

library(dplyr)

sick %>%
  arrange(ID, SickStartDate, SickEndDate) %>%
  group_by(ID) %>%
  mutate(EndLastSick = lag(SickEndDate, default = first(RecordBegins))) %>%
  ungroup

#   ID    RecordBegins SickStartDate SickEndDate EndLastSick
#                               
#1 person1 1990-01-01   2017-03-04    2017-07-01  1990-01-01 
#2 person1 1990-01-01   2018-11-01    2019-02-04  2017-07-01 
#3 person2 1995-01-01   2014-10-07    2017-01-04  1995-01-01 
#4 person2 1995-01-01   2017-11-01    2017-11-23  2017-01-04

Finding end-date of last sickness incident in R

2 Answers