CODEWITHSUNDEEP
rdateintervals
Goran Košutić
Goran Košutić

Reputation: 37

R: Count number of days before specific date by ID

I have data frame with multiple columns, most importantly id and date. I would like to make another column in R which will fill in every row which day of date interval is by id. Something like this. 

id  date 
1   12/31/2019
1   12/30/2019
2   12/26/2019
2   12/25/2019
2   12/24/2019
3   12/22/2019
3   12/21/2019
3   12/20/2019
3   12/19/2019
4   12/15/2019
4   12/14/2019
4   12/13/2019

to make like this

id  date        date count
1   12/31/2019  2
1   12/30/2019  1
2   12/26/2019  3
2   12/25/2019  2
2   12/24/2019  1
3   12/22/2019  4
3   12/21/2019  3
3   12/20/2019  2
3   12/19/2019  1
4   12/15/2019  3
4   12/14/2019  2
4   12/13/2019  1

Upvotes: 1

Views: 134

Answers (3)

chinsoon12
chinsoon12

Reputation: 25223

Another data.table option:

DT[order(id, as.IDate(date, format="%m/%d/%Y")), dc := rowid(id)]

Upvotes: 0

akrun
akrun

Reputation: 887851

We can use data.table methods

library(data.table)
setDT(df)[, date_count := frank(as.IDate(date, format = "%m/%d/%Y"), 
              ties.method = 'dense'), id][]
#    id       date date_count
# 1:  1 12/31/2019          2
# 2:  1 12/30/2019          1
# 3:  2 12/26/2019          3
# 4:  2 12/25/2019          2
# 5:  2 12/24/2019          1
# 6:  3 12/22/2019          4
# 7:  3 12/21/2019          3
# 8:  3 12/20/2019          2
# 9:  3 12/19/2019          1
#10:  4 12/15/2019          3
#11:  4 12/14/2019          2
#12:  4 12/13/2019          1

data

df <- structure(list(id = c(1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 4L, 
4L, 4L), date = c("12/31/2019", "12/30/2019", "12/26/2019", "12/25/2019", 
"12/24/2019", "12/22/2019", "12/21/2019", "12/20/2019", "12/19/2019", 
"12/15/2019", "12/14/2019", "12/13/2019")),
  class = "data.frame", row.names = c(NA, 
-12L))

Upvotes: 1

tmfmnk
tmfmnk

Reputation: 40171

One dplyr possibility could be:

df %>%
 group_by(id) %>%
 mutate(date_count = dense_rank(as.Date(date, format = "%m/%d/%Y")))

      id date       date_count
   <int> <chr>           <int>
 1     1 12/31/2019          2
 2     1 12/30/2019          1
 3     2 12/26/2019          3
 4     2 12/25/2019          2
 5     2 12/24/2019          1
 6     3 12/22/2019          4
 7     3 12/21/2019          3
 8     3 12/20/2019          2
 9     3 12/19/2019          1
10     4 12/15/2019          3
11     4 12/14/2019          2
12     4 12/13/2019          1

Upvotes: 1

Related Questions