CODEWITHSUNDEEP
r
Obed
Obed

Reputation: 423

Number observations by group according to date of observation

I have a simple data frame consisting of dated observations for a series of IDs.

df<- data.frame(ID = c(1,1,1,1,2,2,2,2,3,3), Date = c(1981,1947,1981,1992,1999,2001,2000,2001,2017,2019))

   ID Date
1   1 1981
2   1 1947
3   1 1981
4   1 1992
5   2 1999
6   2 2001
7   2 2000
8   2 2001
9   3 2017
10  3 2019

I'd simply like to number the observations for each ID based on the date of the observation. Earliest observation = 1, then 2, etc.

   ID Date order
1   1 1981     2
2   1 1947     1
3   1 1981     2
4   1 1992     3
5   2 1999     1
6   2 2001     3
7   2 2000     2
8   2 2001     3
9   3 2017     1
10  3 2019     2

I'm sure a question like this has been asked and answered before but I haven't been able to find it. I'd be grateful for any help or direction.

Upvotes: 1

Views: 106

Answers (3)

akrun
akrun

Reputation: 887118

Using data.table

library(data.table)
setDT(df)[, order := frank(Date, ties.method = 'dense'), ID]

Upvotes: 1

zimia
zimia

Reputation: 932

df %>%
  group_by(ID) %>%
  mutate(order = dense_rank(Date)) %>%
  ungroup()
## A tibble: 10 x 3
#      ID  Date order
#   <dbl> <dbl> <int>
# 1     1  1981     2
# 2     1  1947     1
# 3     1  1981     2
# 4     1  1992     3
# 5     2  1999     1
# 6     2  2001     3
# 7     2  2000     2
# 8     2  2001     3
# 9     3  2017     1
#10     3  2019     2

Upvotes: 2

MR_MPI-BGC
MR_MPI-BGC

Reputation: 285

What about

> df %>% group_by(ID) %>% mutate(order=rank(Date, ties.method = "min")) %>% ungroup()
# A tibble: 10 x 3
      ID  Date order
   <dbl> <dbl> <int>
 1     1  1981     2
 2     1  1947     1
 3     1  1981     2
 4     1  1992     4
 5     2  1999     1
 6     2  2001     3
 7     2  2000     2
 8     2  2001     3
 9     3  2017     1
10     3  2019     2

Upvotes: 1

Related Questions