CODEWITHSUNDEEP
r
MrCPT
MrCPT

Reputation: 37

Count active items per month

I have an object that contains the creation and end date of user accounts:

> user
ID  Create    End
1   2014-01-03 2015-01-02
2   2012-03-10 2014-10-24
3   2013-09-10 2015-01-04

I need to determine the number of active accounts per month, for example:

> active accounts
Month Count
2013-12 2
2014-01 3
2014-02 3

Upvotes: 1

Views: 145

Answers (1)

Jaap
Jaap

Reputation: 83215

Using:

library(data.table)
setDT(dat)[, .(mon = format(seq.Date(Create,End,'month'), '%Y-%m')), by = ID
           ][order(mon), .N, by = mon]

you get:

        mon N
 1: 2012-03 1
 2: 2012-04 1
 3: 2012-05 1
 4: 2012-06 1
 5: 2012-07 1
 6: 2012-08 1
 7: 2012-09 1
 8: 2012-10 1
 9: 2012-11 1
10: 2012-12 1
11: 2013-01 1
12: 2013-02 1
13: 2013-03 1
14: 2013-04 1
15: 2013-05 1
16: 2013-06 1
17: 2013-07 1
18: 2013-08 1
19: 2013-09 2
20: 2013-10 2
21: 2013-11 2
22: 2013-12 2
23: 2014-01 3
24: 2014-02 3
25: 2014-03 3
26: 2014-04 3
27: 2014-05 3
28: 2014-06 3
29: 2014-07 3
30: 2014-08 3
31: 2014-09 3
32: 2014-10 3
33: 2014-11 2
34: 2014-12 2

A dplyr equivalent of the above:

library(dplyr)
dat %>% 
  group_by(ID) %>% 
  do(data.frame(., mon = format(seq.Date(.$Create,.$End,'month'), '%Y-%m'))) %>% 
  group_by(mon) %>% 
  tally()

Upvotes: 1

Related Questions