Assign number based on date and unique identifier

I have a dataframe that look like this:

Date Tag
1/21/22 001
1/22/22 001
1/23/22 001
1/21/22 002
1/22/22 002
1/23/22 002

The entire dataframe is grouped by tag number and then sorted by date oldest to newest. I want to create a new column that assigns 1 for first capture 2 for second capture etc. and should look like this.

Date Tag CaptureOrder
1/21/22 001 1
1/22/22 001 2
1/23/22 001 3
1/21/22 002 1
1/22/22 002 2
1/23/22 002 3

>Solution :

You can use the consecutive_id function from the package dplyr version >= 1.1.0 while have the group on the Tag column.

library(dplyr)

df %>% mutate(CaptureOrder = consecutive_id(Date), .by = Tag)

     Date Tag CaptureOrder
1 1/21/22   1            1
2 1/22/22   1            2
3 1/23/22   1            3
4 1/21/22   2            1
5 1/22/22   2            2
6 1/23/22   2            3

Leave a Reply