Count Dates (in order) by ID [in R]

Question

I want to compute a column that counts the orders by Date of customers.

Here some toy code:

Date <- as.Date(c('2006-08-30','2006-08-23', '2006-09-06', '2006-09-13', '2006-09-20')) 

ID <- c("x1","x1","X2","X3","x1") 

TransNo<-c("123","124","125","126","127")

df<-data.frame(ID,Date,TransNo) 

My expected result looks like this, where "Times" is the desired var:

ID       Date TransNo Times

1 x1 2006-08-30     123     2

2 x1 2006-08-23     124     1

3 X2 2006-09-06     125     1

4 X3 2006-09-13     126     1

5 x1 2006-09-20     127     3

I tried some aggregate and dplyr solutions with count / n or length. Not sure what solves this problem but it cant be rocket-science.

Any help would be great! Thank you all in advance

Answer 1

To count the dates in order by id, you can use the cumsum() function and the dplyr package as follows:

Date <- as.Date(c('2006-08-30','2006-08-23', '2006-09-06', '2006-09-13', '2006-09-20')) 

ID <- c("x1","x1","X2","X3","x1") 

TransNo<-c("123","124","125","126","127")

df<-data.frame(ID,Date,TransNo)

df%>%

  arrange(Date) %>%

  group_by(ID) %>%

  mutate(dummy = 1) %>%

  mutate(times = cumsum(dummy)) %>%

  select(-dummy)

Output:

# A tibble: 5 x 4

# Groups:   ID [3]

  ID    Date       TransNo times

  <fct> <date>     <fct>   <dbl>

1 x1    2006-08-23 124         1

2 x1    2006-08-30 123         2

3 X2    2006-09-06 125         1

4 X3    2006-09-13 126         1

5 x1    2006-09-20 127         3