Rank function in dplyr

user_12981102 0
Viewed 76

I have a sample df as shown below:

d<-structure(list(ReviewType= c("Review","Review","Review","Correction","Correction","Review","Review","Review","Review","Review","Correction","Correction","Deficiency","Correction","Correction", 
                                "Deficiency", "Deficiency", "Deficiency", "Correction","Correction","Deficiency","Correction"),
                  Submissiondate= c("2020-08-29 04:32:00","2020-08-28 04:31:00","2020-08-26 04:31:00","2020-08-25 04:31:00","2020-08-24 04:31:00","2020-08-23 04:31:00","2020-08-22 04:31:00","2020-08-21 04:31:00","2020-08-20 04:31:00","2020-08-19 04:31:00",
                                    "2020-09-27 04:31:00","2020-09-27 03:52:59","2020-09-28 17:30:00","2020-09-29 14:01:00",
                                    "2020-09-05 03:00:00","2020-09-05 03:51:00", "2020-09-03 23:59:49",
                                    "2020-09-02 00:03:54","2020-09-01 00:04:48","2020-10-01 04:31:00","2020-10-11 04:31:00","2020-10-21 04:31:00"),
                  CaseNo= c("124","123","125","121","121","125","123","123","123","123","123","123","123","125","123","123","123","124","123","127","127","127")), class = "data.frame", row.names = c(NA, -22L))

I know i can use a rank function to get the latest detail for each case like the one shown below

d<-d %>% group_by(CaseNo) %>% arrange(desc(Submissiondate)) %>% dplyr::mutate(rank = row_number()) %>% arrange(`CaseNo`, rank)%>%filter(rank==1)

However, is it possible to use this rank function to get the latest detail by a specific date? So for example in my df, if I want to find out what was the latest review type for each case by 2020-08-29? How should i go on about doing it?

dplyr r
3 Answers

You could use last and filter:

d %>%  arrange(Submissiondate) %>%
       group_by(CaseNo) %>%
       summarize(last(ReviewType),last(Submissiondate)) %>%
       ungroup()

# A tibble: 5 x 3
  CaseNo `last(ReviewType)` `last(Submissiondate)`
  <chr>  <chr>              <chr>                 
1 121    Correction         2020-08-24 04:31:00   
2 123    Review             2020-08-19 04:31:00   
3 124    Review             2020-08-29 04:32:00   
4 125    Review             2020-08-23 04:31:00   
5 127    Correction         2020-10-01 04:31:00  

d %>%  filter(Submissiondate<'2020-08-29') %>% 
       arrange(Submissiondate) %>% 
       group_by(CaseNo) %>%
       summarize(last(ReviewType),last(Submissiondate)) %>%
       ungroup()

# A tibble: 3 x 3
  CaseNo `last(ReviewType)` `last(Submissiondate)`
  <chr>  <chr>              <chr>                 
1 121    Correction         2020-08-24 04:31:00   
2 123    Review             2020-08-19 04:31:00   
3 125    Review             2020-08-23 04:31:00
user_135133280

You might want to consider to use comparison to maximum, leads to less computation and less typing:

d %>% 
  group_by(CaseNo) %>%
  filter(Submissiondate==max(Submissiondate))

or for your question:

d %>% 
  filter(as.Date(Submissiondate)<"2020-08-30") %>%
  group_by(CaseNo) %>%
  filter(Submissiondate==max(Submissiondate))

If you're interested in short code and speed, here's also a data.table version:

D <- data.table(d)

D[,.SD[Submissiondate==max(Submissiondate)], keyby=CaseNo]

D[Submissiondate<"2020-08-30", .SD[Submissiondate==max(Submissiondate)], keyby=CaseNo]
user_128947600

This could be achieved by first filtering your data for the desired date:

library(dplyr)

d %>% 
  filter(as.Date(Submissiondate, "%Y-%m-%d") == as.Date("2020-08-29")) %>% 
  group_by(CaseNo) %>% 
  arrange(desc(Submissiondate)) %>% 
  dplyr::mutate(rank = row_number()) %>% 
  arrange(`CaseNo`, rank) %>% 
  filter(rank==1)
#> # A tibble: 1 x 4
#> # Groups:   CaseNo [1]
#>   ReviewType Submissiondate      CaseNo  rank
#>   <chr>      <chr>               <chr>  <int>
#> 1 Review     2020-08-29 04:32:00 124        1
user_129938610
Related