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

`df %>% mutate(ord=1:n()) %>% arrange(Date) %>% group_by(ID) %>% mutate(n=1:n()) %>% arrange(ord) %>% select(-ord)` — January, Jul 22 '19 at 10:49
Hi, this throws an error. Error: n() should only be called in a data context — crm_analytics, Jul 22 '19 at 11:44
`df %>% arrange(Date) %>% group_by(ID) %>% mutate(n = row_number()) ` — Ronak Shah, Jul 22 '19 at 12:14
Ronak, thank you but same error here: Error: row_number() should only be called in a data context — crm_analytics, Jul 22 '19 at 12:31
Both of these constructions are correct. Try `dplyr::n()` or `dplyr::row_number()`. — January, Jul 22 '19 at 14:54

score 0 · Answer 1 · answered Jul 22 '19 at 10:54

0

Does this do what you want?

df<-data.frame(ID,Date,TransNo) %>%
  arrange(Date) %>%
  group_by(ID) %>%
  mutate(dummy = 1) %>%
  mutate(times = cumsum(dummy)) %>%
  select(-dummy)

returns

 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

answered Jul 22 '19 at 10:54

nogbad

435
4
15

Thank you nogbad, but it counts from 1 to n and not per ID and TransNo – crm_analytics Jul 22 '19 at 11:45

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

1 Answers1