Create new index based on conditional increment

user_9203380 0
Viewed 97

I have a dataframe that looks like this (but with 1000s of rows):

Person_ID Visit_ID Time_Diff
1 1 NA
2 2 NA
3 3 NA
3 4 1444
4 5 NA
4 6 0
4 7 0
4 8 180
5 9 NA
6 10 NA
7 11 NA
7 12 19
8 13 NA
8 14 25
9 15 NA

What you see from this is that:

  1. The same person_ID can be in multiple rows
  2. The Visit_ID always increments by 1
  3. The Time Diff is sometimes NA, sometimes negative, sometimes positive

What I want to do is to:

  1. Create a new Visit_ID (Let's call it New_Visit_ID)
  2. Start that ID from 1 on the first row and then increment for each row the Person_ID changes OR the Time_Diff is >24 (i.e. not NA or <=24)

What this means is that the same Person_ID with a time diff of <=24 should have the same New_Visit_ID, i.e. some kind of conditional increment.

Hope this is clear!

The desired output should be:

Person_ID Visit_ID Time_Diff New_Visit_ID
1 1 NA 1
2 2 NA 2
3 3 NA 3
3 4 1444 4
4 5 NA 5
4 6 0 5
4 7 0 5
4 8 180 6
5 9 NA 7
6 10 NA 8
7 11 NA 9
7 12 19 9
8 13 NA 10
8 14 25 11
9 15 NA 12
dplyr r datatable
1 Answers

We create two columns 'ind1', 'ind2' based on the two conditions 1) checking 'Time_Diff' greater than 24. 2) check whether the adjacent elements of 'Person_ID' are same, then add them together, get the cumulative sum (cumsum) to create the 'New_Visit_ID'

library(dplyr)
df1 %>%
   mutate(ind1 = (Time_Diff > 24 & !is.na(Time_Diff)),
   ind2 = Person_ID != lag(Person_ID, default = first(Person_ID)),
   New_Visit_ID= cumsum(ind1 + ind2) + 1, ind1 = NULL, ind2 = NULL)

-output

#    Person_ID Visit_ID Time_Diff New_Visit_ID
#1          1        1        NA            1
#2          2        2        NA            2
#3          3        3        NA            3
#4          3        4      1444            4
#5          4        5        NA            5
#6          4        6         0            5
#7          4        7         0            5
#8          4        8       180            6
#9          5        9        NA            7
#10         6       10        NA            8
#11         7       11        NA            9
#12         7       12        19            9
#13         8       13        NA           10
#14         8       14        25           11
#15         9       15        NA           12

data

df1 <- structure(list(Person_ID = c(1L, 2L, 3L, 3L, 4L, 4L, 4L, 4L, 
5L, 6L, 7L, 7L, 8L, 8L, 9L), Visit_ID = 1:15, Time_Diff = c(NA, 
NA, NA, 1444L, NA, 0L, 0L, 180L, NA, NA, NA, 19L, NA, 25L, NA
)), class = "data.frame", row.names = c(NA, -15L))
user_37322710
Related