Create variable that takes value of another variable within a sequence

Question

I have a dataset like the example one below. I want to create a variable (i.e., Submission_date.last) which takes the last value of another variable (i.e., Submission_date) based on the sequence of another variable (i.e., repeat) whereby 0 indicates the start of a new sequence.

I have got the below code, but don't know where to go from here. Any help is appreciated, thanks!

df %>%
  group_by(ID) %>%
  arrange(Submission_date) %>%
  mutate(Submission_date.last = Submission_date[]) # I'm not sure what to put on this line to correctly create the variable?

ID    Submission_date    Repeat    Submission_date.last
1        25/07/19          0          31/07/19
1        30/07/19          1          31/07/19
1        31/07/19          2          31/07/19
1        11/08/20          0          14/08/20
1        14/08/20          1          14/08/20
2        30/07/19          0          30/07/19
2        31/08/20          0          31/08/20
3        13/09/20          0          15/09/20
3        15/09/20          1          15/09/20
3        18/07/21          0          22/07/21
3        21/07/21          1          22/07/21
3        22/07/21          2          22/07/21

Ronak Shah · Accepted Answer · 2021-08-15 11:34:08Z

1

Create a group whenever Repeat = 0 and for each group get the last Submission_date.

library(dplyr)

df %>%
  group_by(ID, grp = cumsum(Repeat == 0)) %>%
  mutate(Submission_date.last = last(Submission_date)) %>%
  ungroup %>%
  select(-grp)

#      ID Submission_date Repeat Submission_date.last
#   <int> <chr>            <int> <chr>               
# 1     1 25/07/19             0 31/07/19            
# 2     1 30/07/19             1 31/07/19            
# 3     1 31/07/19             2 31/07/19            
# 4     1 11/08/20             0 14/08/20            
# 5     1 14/08/20             1 14/08/20            
# 6     2 30/07/19             0 30/07/19            
# 7     2 31/08/20             0 31/08/20            
# 8     3 13/09/20             0 15/09/20            
# 9     3 15/09/20             1 15/09/20            
#10     3 18/07/21             0 22/07/21            
#11     3 21/07/21             1 22/07/21            
#12     3 22/07/21             2 22/07/21

data

It is easier to help if you provide data in reproducible format.

df <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 
3L, 3L), Submission_date = c("25/07/19", "30/07/19", "31/07/19", 
"11/08/20", "14/08/20", "30/07/19", "31/08/20", "13/09/20", "15/09/20", 
"18/07/21", "21/07/21", "22/07/21"), Repeat = c(0L, 1L, 2L, 0L, 
1L, 0L, 0L, 0L, 1L, 0L, 1L, 2L)), row.names = c(NA, -12L), class = "data.frame")

answered Aug 15, 2021 at 11:34

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AEP Over a year ago

Thanks so much! Will also include data in a reproducible format in future questions, thanks for pointing this out.

akrun · Accepted Answer · 2021-08-15 18:43:56Z

We can use

library(data.table)
setDT(df)[, Submission_date.last := last(Submission_date), 
     by = .(ID, cumsum(Repeat == 0))]

-ouptut

> df
    ID Submission_date Repeat Submission_date.last
 1:  1        25/07/19      0             31/07/19
 2:  1        30/07/19      1             31/07/19
 3:  1        31/07/19      2             31/07/19
 4:  1        11/08/20      0             14/08/20
 5:  1        14/08/20      1             14/08/20
 6:  2        30/07/19      0             30/07/19
 7:  2        31/08/20      0             31/08/20
 8:  3        13/09/20      0             15/09/20
 9:  3        15/09/20      1             15/09/20
10:  3        18/07/21      0             22/07/21
11:  3        21/07/21      1             22/07/21
12:  3        22/07/21      2             22/07/21

data

df <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 3L, 3L, 3L, 
3L, 3L), Submission_date = c("25/07/19", "30/07/19", "31/07/19", 
"11/08/20", "14/08/20", "30/07/19", "31/08/20", "13/09/20", "15/09/20", 
"18/07/21", "21/07/21", "22/07/21"), Repeat = c(0L, 1L, 2L, 0L, 
1L, 0L, 0L, 0L, 1L, 0L, 1L, 2L)), row.names = c(NA, -12L), class = "data.frame")

Collectives™ on Stack Overflow

Create variable that takes value of another variable within a sequence

2 Answers 2

1 Comment

data

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

data

Comments

Your Answer

Sign up or log in

Post as a guest

Related