How to merge observations with close dates in r

Gómez-Buendía A. 0
Viewed 29

I have a database where animals in a herd are tested every 6 months (number of animals can change over the time). The issue is that all the animals in a herd are not tested on the same day but within a period of time of 2 months.

I would like to know who I can create a new column that merges all these close dates (grouping by herd), so I can calculate the number of times a herd has been tested.

This is an example of a herd that has been tested 8 times, but at different dates. Each dot represents an animal:

Here is an example of the data:

df <- data.frame(
        animal = c("Animal1", "Animal2", "Animal3", "Animal4", "Animal5", "Animal6", "Animal1", "Animal2", "Animal3", "Animal4", "Animal5", "Animal6", "Animal7", "Animal8", "Animal9", "Animal10", "Animal11", "Animal12", "Animal7", "Animal8", "Animal9", "Animal10", "Animal11", "Animal12"),
        herd = c("Herd1","Herd1","Herd1", "Herd1","Herd1","Herd1", "Herd1","Herd1","Herd1", "Herd1","Herd1","Herd1","Herd2","Herd2", "Herd2","Herd2","Herd2","Herd2", "Herd2","Herd2", "Herd2","Herd2","Herd2","Herd2"),
        date = c("2017-01-01", "2017-01-01", "2017-01-17","2017-02-04", "2017-02-04", "2017-02-05", "2017-06-01" , "2017-06-03", "2017-07-01", "2017-06-21", "2017-06-01", "2017-06-15", "2017-02-01", "2017-02-01", "2017-02-15", "2017-02-21", "2017-03-05", "2017-03-01", "2017-07-01", "2017-07-01", "2017-07-15", "2017-07-21", "2017-08-05", "2017-08-01"))

So the desired outcome will be:

     animal  herd       date  testing
1   Animal1 Herd1 2017-01-01  1
2   Animal2 Herd1 2017-01-01  1
3   Animal3 Herd1 2017-01-17  1
4   Animal4 Herd1 2017-02-04  1
5   Animal5 Herd1 2017-02-04  1
6   Animal6 Herd1 2017-02-05  1
7   Animal1 Herd1 2017-06-01  2
8   Animal2 Herd1 2017-06-03  2
9   Animal3 Herd1 2017-07-01  2
10  Animal4 Herd1 2017-06-21  2
11  Animal5 Herd1 2017-06-01  2
12  Animal6 Herd1 2017-06-15  2
13  Animal7 Herd2 2017-02-01  1
14  Animal8 Herd2 2017-02-01  1
15  Animal9 Herd2 2017-02-15  1
16 Animal10 Herd2 2017-02-21  1
17 Animal11 Herd2 2017-03-05  1
18 Animal12 Herd2 2017-03-01  1
19  Animal7 Herd2 2017-07-01  2
20  Animal8 Herd2 2017-07-01  2
21  Animal9 Herd2 2017-07-15  2
22 Animal10 Herd2 2017-07-21  2
23 Animal11 Herd2 2017-08-05  2
24 Animal12 Herd2 2017-08-01  2

I would like to apply something like this but considering that dates closed to each other are the same testing

df %>% 
  group_by(herd) %>% 
  mutate(testing = dense_rank(date))

Thanks!

ranking date r
1 Answers

You can group_by every 5 months and apply dense_rank. Since your smallest gap between two dates from the same animal is 5 months, the unit has to be 5 months.

library(dplyr)
library(lubridate)
df %>% 
  group_by(testing = dense_rank(floor_date(ymd(date), unit = "5 months")))
Maël0
Related