is there a way to count unique dates in R dplyr-CodePudding

Say I have:

Date        ID  
2000-01-01  1    
2000-01-02  1    
2000-01-03  1   
2000-01-01  2    
2000-01-01  2    
2000-01-01  3    
2000-01-10  3  
2000-01-04  3

I want to count consecutive dates by ID. how can I get something like:

Date        ID  count
2000-01-01  1   1
2000-01-02  1   2 
2000-01-03  1   3
2000-01-01  2   1
2000-01-01  2   1 
2000-01-01  3   1 
2000-01-10  3   3
2000-01-04  3   2

CodePudding user response：

We may use match on the sorted unique values of 'Date' after grouping by 'ID'

library(dplyr)
df1 %>% 
   group_by(ID) %>%
   mutate(count = match(Date, sort(unique(Date)))) %>%
   ungroup

-output

# A tibble: 8 × 3
  Date          ID count
  <date>     <int> <int>
1 2000-01-01     1     1
2 2000-01-02     1     2
3 2000-01-03     1     3
4 2000-01-01     2     1
5 2000-01-01     2     1
6 2000-01-01     3     1
7 2000-01-10     3     3
8 2000-01-04     3     2

Or another option is dense_rank

df1 %>% 
  group_by(ID) %>%
  mutate(count = dense_rank(Date)) %>%
  ungroup

data

df1 <- structure(list(Date = structure(c(10957, 10958, 10959, 10957, 
10957, 10957, 10966, 10960), class = "Date"), ID = c(1L, 1L, 
1L, 2L, 2L, 3L, 3L, 3L)), row.names = c(NA, -8L), class = "data.frame")