How to make the last entry in a column within the same ID to be the first entry in R?-CodePudding

I have a dataset with over 15000 rows, which looks similar to this:

  ID valid_from  valid_until     action  action_text
1  1 01/01/2010  31/01/2016      1       Text1
2  1 01/02/2016  01/01/2021      2       Text2
3  2 01/10/2010  30/09/2019      4       Text4
4  3 01/05/2015  31/12/2015      3       Text3
5  3 01/01/2016  30/09/2020      3       Text3

I would like to combine the rows so that the last entry in column valid_until within the same ID becomes the first entry. Basically the first entry in column "valid_until" should be replaced with the last entry within the ID and rows which are not the first entries within an ID should be deleted.

To be more clear, I would like my result to look like this:

   ID  valid_from    valid_until     action  action_text
    1  1 01/01/2010  01/01/2021      1       Text1
    2  2 01/10/2010  30/09/2019      4       Text4
    3  3 01/05/2015  30/09/2020      3       Text3

Does anyone have an idea, how I could do this in R?

Thank you very much in advance!

CodePudding user response：

library(dplyr)

df %>% group_by(ID) %>%
       mutate(valid_from=min(valid_from),
              valid_until=max(valid_until),
              action=first(action),
              action_text=first(action_text)) %>%
       distinct(across(everything()))

CodePudding user response：

We may convert the date columns to Date class and just change the 'valid_until' column before doing the distinct

library(dplyr)
library(lubridate)
df1 %>%
    mutate(across(starts_with('valid'), dmy)) %>% 
    group_by(ID) %>% 
    mutate(valid_until = max(valid_until)) %>% 
    distinct(ID, .keep_all = TRUE) %>% 
    ungroup

-output

# A tibble: 3 × 5
     ID valid_from valid_until action action_text
  <int> <date>     <date>       <int> <chr>      
1     1 2010-01-01 2021-01-01       1 Text1      
2     2 2010-10-01 2019-09-30       4 Text4      
3     3 2015-05-01 2020-09-30       3 Text3

data

df1 <- structure(list(ID = c(1L, 1L, 2L, 3L, 3L), valid_from = c("01/01/2010", 
"01/02/2016", "01/10/2010", "01/05/2015", "01/01/2016"), valid_until = c("31/01/2016", 
"01/01/2021", "30/09/2019", "31/12/2015", "30/09/2020"), action = c(1L, 
2L, 4L, 3L, 3L), action_text = c("Text1", "Text2", "Text4", "Text3", 
"Text3")), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5"))