I have an R data frame that has an ID column with multiple records for an ID. When the flag is set to 1 for an ID, I want to create a column new timeline that starts from 1 and increases sequentially in increments of 6 (1,6,12...). How can I achieve this in R using dplyr ?
Below is a sample data frame
| ID | Timepoint | Flag |
|---|---|---|
| A | 0 | 0 |
| A | 6 | 0 |
| A | 12 | 0 |
| A | 18 | 1 |
| A | 24 | 0 |
| A | 30 | 0 |
| A | 36 | 0 |
Expected Dataframe
| ID | Timepoint | Flag | New_Timepoint |
|---|---|---|---|
| A | 0 | 0 | |
| A | 6 | 0 | |
| A | 12 | 0 | |
| A | 18 | 1 | 1 |
| A | 24 | 0 | 6 |
| A | 30 | 0 | 12 |
| A | 36 | 0 | 18 |
An option is to group by 'ID', create the lag of the 'Timepoint' with n specified as the position of 'Flag' where the value is 1 (-1)
library(dplyr)
df1 %>%
group_by(ID) %>%
mutate(New_Timepoint = dplyr::lag(replace(Timepoint, !Timepoint, 1),
n = which(Flag == 1)-1)) %>%
ungroup
-output
# A tibble: 7 x 4
# ID Timepoint Flag New_Timepoint
# <chr> <int> <int> <dbl>
#1 A 0 0 NA
#2 A 6 0 NA
#3 A 12 0 NA
#4 A 18 1 1
#5 A 24 0 6
#6 A 30 0 12
#7 A 36 0 18
Or use a double cumsum to create the index
df1 %>%
group_by(ID) %>%
mutate(New_Timepoint = Timepoint[na_if(cumsum(cumsum(Flag)), 0)]) %>%
ungroup
df1 <- structure(list(ID = c("A", "A", "A", "A", "A", "A", "A"),
Timepoint = c(0L,
6L, 12L, 18L, 24L, 30L, 36L),
Flag = c(0L, 0L, 0L, 1L, 0L, 0L,
0L)), class = "data.frame", row.names = c(NA, -7L))
Another dplyr option
df %>%
group_by(ID) %>%
mutate(New_Timepoint = pmax(1, Timepoint - c(NA, Timepoint[Flag == 1])[cumsum(Flag) + 1])) %>%
ungroup()
gives
ID Timepoint Flag New_Timepoint
<chr> <int> <int> <dbl>
1 A 0 0 NA
2 A 6 0 NA
3 A 12 0 NA
4 A 18 1 1
5 A 24 0 6
6 A 30 0 12
7 A 36 0 18
