Why is my group_by() and slice_max() returning the desired number of records? R

user_12512712 0
Viewed 235

So I have this dataset I am working in. It has records that are recording on a log and there some duplicates on ClientIDs. The reason there are duplicates is because a user can update the data with new information. I've included the dataset below

  Timestamp           ClientID Application_Date Expiration_Date
  <chr>               <chr>    <chr>            <chr>          
1 12/9/2021 13:26:10  380925   12/8/2021        NA             
2 12/9/2021 14:37:15  380925   12/9/2021        NA             
3 12/14/2021 12:34:30 375889   12/14/2021       12/24/2021     
4 12/14/2021 12:37:08 375889   12/14/2021       NA             
5 12/14/2021 12:45:40 319956   12/14/2021       12/16/2021

So I have 3 distinct IDs here, 380925, 375889, and 319956. Using the timestamp column, I can see what data is the newest and most up to date. And I can confirm this in R by doing the following

> data %>% 
+   group_by(ClientID) %>% count()
# A tibble: 3 x 2
# Groups:   ClientID [3]
  ClientID     n
  <chr>    <int>
1 319956       1
2 375889       2
3 380925       2

Now if I would like to filter down to each ClientID's most recent recorded info, I was using this code.

data_output <- data %>% 
  group_by(ClientID) %>%
  slice_max(as.POSIXct(Timestamp, format = "%d/%m/%Y %H:%M:%S")) %>% 
  ungroup() %>% 
  arrange(Timestamp)

The problem is my output is only 1 row when I was expecting 3 rows.

# A tibble: 1 x 4
  Timestamp          ClientID Application_Date Expiration_Date
  <chr>              <chr>    <chr>            <chr>          
1 12/9/2021 14:37:15 380925   12/9/2021        NA

Why is this happening?

Data Below

data <- structure(list(Timestamp = c("12/9/2021 13:26:10", "12/9/2021 14:37:15", 
                             "12/14/2021 12:34:30", "12/14/2021 12:37:08", "12/14/2021 12:45:40"
), ClientID = c("380925", "380925", "375889", "375889", "319956"
), Application_Date = c("12/8/2021", "12/9/2021", "12/14/2021", 
                        "12/14/2021", "12/14/2021"), Expiration_Date = c(NA, NA, "12/24/2021", 
                                                                         NA, "12/16/2021")), row.names = c(NA, -5L), class = c("tbl_df", 
                                                                                                                               "tbl", "data.frame"))
dplyr r
1 Answers

The issue is with wrong format. It returns NA for those groups and is removed.

library(dplyr)
library(lubridate)
data %>% 
  group_by(ClientID) %>%
  slice_max(n = 1, order_by = mdy_hms(Timestamp)) %>%
  ungroup

-output

# A tibble: 3 × 4
  Timestamp           ClientID Application_Date Expiration_Date
  <chr>               <chr>    <chr>            <chr>          
1 12/14/2021 12:45:40 319956   12/14/2021       12/16/2021     
2 12/14/2021 12:37:08 375889   12/14/2021       <NA>           
3 12/9/2021 14:37:15  380925   12/9/2021        <NA>

If we do the same format to create a column

data %>%
   mutate(Timestampnew = as.POSIXct(Timestamp, format = "%d/%m/%Y %H:%M:%S"))
# A tibble: 5 × 5
  Timestamp           ClientID Application_Date Expiration_Date Timestampnew       
  <chr>               <chr>    <chr>            <chr>           <dttm>             
1 12/9/2021 13:26:10  380925   12/8/2021        <NA>            2021-09-12 13:26:10
2 12/9/2021 14:37:15  380925   12/9/2021        <NA>            2021-09-12 14:37:15
3 12/14/2021 12:34:30 375889   12/14/2021       12/24/2021      NA                 
4 12/14/2021 12:37:08 375889   12/14/2021       <NA>            NA                 
5 12/14/2021 12:45:40 319956   12/14/2021       12/16/2021      NA

Except for the first two, all others are NA because %d/%m/%Y is clearly an issue when the value is 14 for rows 3 to 5 - i.e. it should be %m/%d/%Y %H:%M:%S.

data %>% 
  group_by(ClientID) %>%
  slice_max(as.POSIXct(Timestamp, format = "%m/%d/%Y %H:%M:%S")) %>% 
  ungroup() %>% 
  arrange(Timestamp)
# A tibble: 3 × 4
  Timestamp           ClientID Application_Date Expiration_Date
  <chr>               <chr>    <chr>            <chr>          
1 12/14/2021 12:37:08 375889   12/14/2021       <NA>           
2 12/14/2021 12:45:40 319956   12/14/2021       12/16/2021     
3 12/9/2021 14:37:15  380925   12/9/2021        <NA>

In addition the arrange 'Timestamp' should be on POSIXct converted column? i.e.

data %>% 
   mutate(Timestamp = as.POSIXct(Timestamp, format = "%m/%d/%Y %H:%M:%S")) %>%
   group_by(ClientID) %>%
   slice_max(Timestamp) %>%
   ungroup %>%
   arrange(Timestamp)

-output

# A tibble: 3 × 4
  Timestamp           ClientID Application_Date Expiration_Date
  <dttm>              <chr>    <chr>            <chr>          
1 2021-12-09 14:37:15 380925   12/9/2021        <NA>           
2 2021-12-14 12:37:08 375889   12/14/2021       <NA>           
3 2021-12-14 12:45:40 319956   12/14/2021       12/16/2021

It is dropping those rows with NA because it calls smaller_ranks which uses sum(min_rank(x) <= y, na.rm = TRUE). If all the values are NA in a group, min_rank returns NA, the <=, returns NA and with na.rm = TRUE in sum returns 0, thus no rows are selected

user_37322710
Related