R data.table get agregated ids

Question

This is similar to this question.

For every row, I have an ID and a Date. I wish to create a new column where the first Date of each ID is 1, the second Date of each ID is 2, etc...

Example:

library(data.table)
data = data.table(
    id = c(1,1,1,2,2,3),
    Row = c(1,2,3,4,5,6),
    Date1 = c("2018-01-01", 
               "2018-01-05",
                "2018-01-21",
                "2018-02-01",
                "2018-03-15",
                "2018-04-01"))

The desired output would be a columns with

1,2,3,1,2,1

Not my downvote, but plenty of reasons. For example, no reproducible example, no attempt to solve it from your side, no expected output, ... — Sotos, Mar 09 '20 at 10:40
If you want to improve your question, here is some information on [how to ask a good question](https://stackoverflow.com/help/how-to-ask). Also the [MRE](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) (as requested in the header of [r]) — dario, Mar 09 '20 at 10:42
I added the MRE. As for the answer elsewhere, I think it is not the case (as the MRE I provided should now show). Thanks — Diogo Santos, Mar 09 '20 at 11:11
Could you fix your example data so it is reproducible? (explicitly state `library` calls, fix the definition of `data` so it doesn't raise an error? (The submitted links regarding MREs contain all the information you need ;) — dario, Mar 09 '20 at 11:31

score 1 · Accepted Answer · answered Mar 09 '20 at 11:35

library(data.table)
DT <- data.table(
  id = c(1,1,1,2,2,3),
  Row = 1:6,
  Date1 = c("2018-01-01", 
            "2018-01-05",
            "2018-01-21",
            "2018-02-01",
            "2018-03-15",
            "2018-04-01"))

Solution using data.table to

create a new column where the first Date of each ID is 1, the second Date of each ID is 2, etc...

DT[, n_date := seq_len(.N), by = id]

DT

Returns:

   id Row      Date1 n_date
1:  1   1 2018-01-01      1
2:  1   2 2018-01-05      2
3:  1   3 2018-01-21      3
4:  2   4 2018-02-01      1
5:  2   5 2018-03-15      2
6:  3   6 2018-04-01      1

R data.table get agregated ids

1 Answers1