Is there a way in data.table to assign ID's by group based upon an identifier?

Question

Suppose I have data that looks like:

Name      Date
Bank1     1/1/2005
Bank1     6/1/2005
Bank1     1/1/2006
Bank1     6/1/2006
Bank1     1/1/2007
Bank2     6/1/2006
Bank2     1/1/2007
Bank3     6/1/2005
Bank3     1/1/2006
Bank3     6/1/2006
Bank3     1/1/2007
....

Here the first date is 1/1/2005 and the last is 1/1/2007. I would like to assign an ID such that I have:

Name      Date         ID
Bank1     1/1/2005     1
Bank1     6/1/2005     2
Bank1     1/1/2006     3
Bank1     6/1/2006     4
Bank1     1/1/2007     5
Bank2     6/1/2006     4
Bank2     1/1/2007     5
Bank3     6/1/2005     2
Bank3     1/1/2006     3
Bank3     6/1/2006     4
Bank3     1/1/2007     5
....

I basically want to assign an ID for the date corresponding to its order of all the dates. Furthermore, Bank1 has the complete list of all dates. I was wondering if there was a simple way in data.table to do this. I have tried things like loops but at higher dimensions it becomes harder. Any suggestions would be greatly appreciated, thanks!

Answer 1

I think you're looking for the special symbol .GRP:

dt[, id := .GRP, by=Date]
#      Name     Date id
#  1: Bank1 1/1/2005  1
#  2: Bank1 6/1/2005  2
#  3: Bank1 1/1/2006  3
#  4: Bank1 6/1/2006  4
#  5: Bank1 1/1/2007  5
#  6: Bank2 6/1/2006  4
#  7: Bank2 1/1/2007  5
#  8: Bank3 6/1/2005  2
#  9: Bank3 1/1/2006  3
# 10: Bank3 6/1/2006  4
# 11: Bank3 1/1/2007  5

Answer 2

Here is a dplyr solution:

library(dplyr)
df %>% 
  select(Date) %>% 
  slice(unique(Date)) %>% 
  mutate(ID = row_number(as.Date(Date, format = "%d/%m/%Y"))) %>% 
  left_join(df, .) %>%
  arrange(Name)

Which gives:

#Joining by: "Date"
#    Name     Date ID
#1  Bank1 1/1/2005  1
#2  Bank1 6/1/2005  2
#3  Bank1 1/1/2006  3
#4  Bank1 6/1/2006  4
#5  Bank1 1/1/2007  5
#6  Bank2 6/1/2006  4
#7  Bank2 1/1/2007  5
#8  Bank3 6/1/2005  2
#9  Bank3 1/1/2006  3
#10 Bank3 6/1/2006  4
#11 Bank3 1/1/2007  5