dplyr - filter rows prior to a certain condition, and expanding non-contiguous time values

I have a series of position and personnel changes in a HR data set. The data set represents a history of personnel movements within a given set of positionIDs.

df <- structure(list(index = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 
29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39), positionID = c(1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 
3, 3, 4, 4, 4, 4, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6), personID = c(114, 
115, 113, 109, 108, 108, 100, 108, 100, 108, 101, 110, 110, 110, 
110, 103, 102, 112, 112, 112, 102, 102, 117, 102, 107, 107, 104, 
109, 118, 118, 105, 118, 118, 111, 106, 106, 120, 120, 120), 
    moveStartDate = c("1/07/2021", "4/07/2021", "28/06/2021", 
    "17/01/2022", "15/04/2022", "7/05/2022", "1/07/2022", "1/07/2022", 
    "26/07/2022", "26/07/2022", "31/12/2020", "1/10/2020", "1/01/2021", 
    "1/01/2021", "28/01/2021", "31/03/2021", "1/07/2021", "19/07/2021", 
    "30/04/2022", "3/06/2022", "1/02/2022", "1/07/2022", "29/08/2022", 
    "24/09/2022", "1/07/2021", "20/08/2021", "5/10/2020", "1/08/2022", 
    "8/08/2022", "7/09/2022", "2/10/2022", "14/10/2022", "29/10/2022", 
    "16/10/2020", "1/08/2020", "22/12/2020", "31/01/2022", "24/12/2022", 
    "3/02/2023"), moveEndDate = c("4/07/2021", "4/07/2021", "17/10/2021", 
    "14/04/2022", "6/05/2022", "30/06/2022", "25/07/2022", "25/07/2022", 
    "30/09/2022", "31/12/2022", "31/12/2020", "31/12/2020", "27/01/2021", 
    "27/01/2021", NA, "31/01/2022", "31/01/2022", "29/04/2022", 
    "3/06/2022", "3/06/2022", "30/06/2022", "23/09/2022", "25/09/2022", 
    "31/03/2023", "20/08/2021", "20/08/2021", "1/12/2021", "28/10/2022", 
    "6/09/2022", "30/09/2022", "2/10/2022", "28/10/2022", NA, 
    "16/10/2020", "21/12/2020", "21/12/2021", "23/12/2022", "2/02/2023", 
    NA), moveType = c("temporary appointment", "moved agency", 
    "temporary appointment", "transfer", "transfer", "transfer", 
    "redesignation", "transfer", "redesignation", "redesignation", 
    "moved agency", "relief", "relief", "transfer", "promotion", 
    "redesignation", "transfer", "transfer", "relief", "resignation", 
    "transfer", "transfer", "temporary appointment", "transfer", 
    "relief", "moved agency", "restructure", "relief", "relief", 
    "relief", "end of contract", "relief", "relief", "moved agency", 
    "relief", "promotion", "relief", "relief", "relief"), exitCode = c(NA, 
    "A", NA, NA, NA, NA, NA, NA, NA, NA, "A", NA, NA, NA, NA, 
    NA, NA, NA, NA, "B", NA, NA, NA, NA, NA, "A", NA, NA, NA, 
    NA, "E", NA, NA, "A", NA, NA, NA, NA, NA)), row.names = c(NA, 
-39L), class = "data.frame")

• Each position has a unique positionID Each staff member has a
  unique personID Each position can have different staff moved in and
  out over time. For example a person could resign or take extended
  leave and be replaced with a different personID.
• If the personID leaves the organisation they will have an exit code. Internal
  movements with exiting are NA.
• The movement dates are ordered
  sequentially, and if the person is still in the position, they do not
  have a move end date.

There are two problems I am trying to solve, as follows:

First problem:

• I would like to filter the set such that only positionIDs where there is an exit code and the subsequent moveTypes of each positionID are retained.
• I tried a group_by for positonID and moveEndDate to create a sequence of increments within each group. This created the increments, but I would not exclude the rows in each group prior to the non-NA exitCode. Within this group I would like to exclude any rows prior to the first exitCode.
• What I want to is create a new group where the moveEndDate starts at the point where there is an exitCode. For example positonID 1 group would include all rows from personID 115 until designated to personID 108 on 31/12/2022.

Second problem:

The time periods between a moveEndDate and a subsequent moveStart date are not contiguous. For example indexes 31 and 32. I would like to be able to insert a row that has a moveStartDate that is equal to the moveEndDate + 1day for immediate prior moveEndDate for that row, and a move endEndDate for the next moveStart date in the group (if there is one).
I’ll be honest. I don’t even know where to start with this one.

Any pointers are greatly appreciated.

With all credit to @jared_mamrot for the first part - see their comment above - you can filter() on cumsum() to very neatly achieve what you want:

x <-
  df %>%
  group_by(positionID) %>%
  filter(cumsum(!is.na(exitCode)) >= 1)

For the second part, I can suggest a solution based on my understanding of your description of the problem. That is, you want to insert the new row between each row pair where the number of days (the "gap") between the moveEndDate and subsequent moveStartDate is greater than zero. (So cases having a negative gap are ignored.) Note that this operates within a given positionID.

We can continue from where left off above, having created a new data.frame x and removed the unwanted rows. We again group by positionID and create a couple of new columns, using lead() to allow us to easily compare the current moveEndDate and subsequent moveStartDate. (Note we are using the lubridate package here.)

library(lubridate)

x <-
  x %>%
  group_by(positionID) %>%
  mutate(
    nextStartDate = lead(moveStartDate),
    gapToNextStartDate = (dmy(nextStartDate) - dmy(moveEndDate)) %>% as.numeric()
  )

... which gives us (first five rows shown):

  index positionID personID moveStartDate moveEndDate moveType              exitCode nextStartDate gapToNextStartDate
  <dbl>      <dbl>    <dbl> <chr>         <chr>       <chr>                 <chr>    <chr>                      <dbl>
1     2          1      115 4/07/2021     4/07/2021   moved agency          A        28/06/2021                    -6
2     3          1      113 28/06/2021    17/10/2021  temporary appointment NA       17/01/2022                    92
3     4          1      109 17/01/2022    14/04/2022  transfer              NA       15/04/2022                     1
4     5          1      108 15/04/2022    6/05/2022   transfer              NA       7/05/2022                      1
5     6          1      108 7/05/2022     30/06/2022  transfer              NA       1/07/2022                      1

We are interested in those rows having gapToNextStartDate (in days) being positive. It is after these rows that we want to insert our new rows.

This next section is inelegant but should do the trick. Not knowing what you wish to set certain columns in the new rows I've made them NA, with the exception of moveType which I've set to "*** inserted row ***" just to highlight these newly inserted rows.

# create a new data frame having the same columns but zero rows
out <- x[0, ]

for (i in seq_len(nrow(x))) {

  # add the current row from x to the output data.frame
  out <- bind_rows(out, x[i, ])  
   
  # check if we need to insert a new row after this one,
  # based on the condition of a positive number of days between
  # this row's moveEndDate and the next row's moveStartDate
  # (within a given positionID)
  if (!is.na(x[i, ]$gapToNextStartDate) & x[i, ]$gapToNextStartDate > 0) {
    out <-
      bind_rows(
        out,
        data.frame(
          index = NA,
          positionID = x[i, ]$positionID,
          personID = NA,
          moveStartDate = (dmy(x[i, ]$moveEndDate) + 1) %>% format("%d/%m/%Y"),
          moveEndDate = x[i, ]$nextStartDate,
          moveType = "*** inserted row ***",
          exitCode = NA,
          nextStartDate = NA,
          gapNextStartDate = NA
        )
      )
  }
}

Our original data frame x had 31 rows (after executing 'part one') - we have now added an additional 15 rows for cases meeting the condition as described above. The first ten rows of the output out are shown:

   index positionID personID moveStartDate moveEndDate moveType              exitCode nextStartDate gapToNextStar…¹ gapNe…²
   <dbl>      <dbl>    <dbl> <chr>         <chr>       <chr>                 <chr>    <chr>                   <dbl> <lgl>  
 1     2          1      115 4/07/2021     4/07/2021   moved agency          A        28/06/2021                 -6 NA     
 2     3          1      113 28/06/2021    17/10/2021  temporary appointment NA       17/01/2022                 92 NA     
 3    NA          1       NA 18/10/2021    17/01/2022  *** inserted row ***  NA       NA                         NA NA     
 4     4          1      109 17/01/2022    14/04/2022  transfer              NA       15/04/2022                  1 NA     
 5    NA          1       NA 15/04/2022    15/04/2022  *** inserted row ***  NA       NA                         NA NA     
 6     5          1      108 15/04/2022    6/05/2022   transfer              NA       7/05/2022                   1 NA     
 7    NA          1       NA 07/05/2022    7/05/2022   *** inserted row ***  NA       NA                         NA NA     
 8     6          1      108 7/05/2022     30/06/2022  transfer              NA       1/07/2022                   1 NA     
 9    NA          1       NA 01/07/2022    1/07/2022   *** inserted row ***  NA       NA                         NA NA     
10     7          1      100 1/07/2022     25/07/2022  redesignation         NA       1/07/2022                 -24 NA

The temporary columns nextStartDate and gapToNextStartDate have been retained for clarity but could be removed. Desired values for index, personID, etc. could be added if necessary.

Note: Most of the cases where gaps occur have a gap of a single day. I've assumed these fit your description of "non-contiguous", but if not they could be easily ignored by modifying > 0 to > 1.