I want to create a new identifier column in a dataset I have.
ex <- structure(list(id = c("8210109300002", "8210109300002", "8210109300002",
"8210109300002", "8210109300002", "8210109300002", "8210109300002",
"8210109300002", "8210109300002"), serv_from_dt = structure(c(18262,
18263, 18267, 18267, 18268, 18269, 18269, 18275, 18276), class = "Date"),
serv_to_dt = structure(c(18262, 18263, 18267, 18267, 18268,
18269, 18269, 18275, 18276), class = "Date"), date_plus1 = structure(c(18263,
18264, 18268, 18268, 18269, 18270, 18270, 18276, 18277), class = "Date")),
row.names = c(NA, -9L), class = c("data.table", "data.frame"))
This identifier would be based on the serv_to_date, serv_from_date, and date_plus1 columns. The data is ordered by serv_from_date; if the next row has a ser_to_date that is equal to the previous row's serv_from_date OR a serv_to_date that is equal to the previous row's serv_from_date+1 (which is the date_plus1 column) then label those rows with 1 identifier.
The final output I'd like is:
want <- structure(list(id = c("8210109300002", "8210109300002", "8210109300002",
"8210109300002", "8210109300002", "8210109300002", "8210109300002",
"8210109300002", "8210109300002"), serv_from_dt = structure(c(18262,
18263, 18267, 18267, 18268, 18269, 18269, 18275, 18276), class = "Date"),
serv_to_dt = structure(c(18262, 18263, 18267, 18267, 18268,
18269, 18269, 18275, 18276), class = "Date"), date_plus1 = structure(c(18263,
18264, 18268, 18268, 18269, 18270, 18270, 18276, 18277), class = "Date"),
identifier = c("1", "1", "2",
"2", "2", "2", "2",
"3", "3")), row.names = c(NA, -9L), class = c("data.table", "data.frame"))
My first step was to create a column that identifies lag dates with the previous row's dates:
ex %>%
mutate(NewCol = ifelse((lag(serv_from_dt) == date_plus1 | lag(serv_from_dt) == serv_to_dt), "yes", "no"))
however, this code does not correctly say "yes" to serv_from_date's that match previous row's date_plus1.
Thanks in advance for any help you can provide!
Aucun commentaire:
Enregistrer un commentaire