create new observations based on grouped ids

Question

I want to create a new id variable based on another id variable. here is what my data look like:

ID
250
250
340
340
340
650
650
650

I want:

ID      New_ID
250       1
250       1
340       2
340       2
340       2
650       3
650       3
650       3

I tried using this using "group_by" in tidyverse, but I am running into errors. Can someone help?

Answer 1

One option is to use rleid from data.table:

data.table::rleid(d$ID)

# [1] 1 1 2 2 2 3 3 3

Adding that column to the data:

d %>%
    mutate(new_id = data.table::rleid(ID))

   ID new_id
1 250      1
2 250      1
3 340      2
4 340      2
5 340      2
6 650      3
7 650      3
8 650      3

Another option would be to exploit the factor function. This will work even if your data is not sorted by ID:

d %>%
    mutate(new_id = as.numeric(factor(ID)))

   ID new_id
1 250      1
2 250      1
3 340      2
4 340      2
5 340      2
6 650      3
7 650      3
8 650      3

data

d <- structure(list(ID = c(250L, 250L, 340L, 340L, 340L, 650L, 650L, 
                           650L)), 
               class = "data.frame", 
               row.names = c(NA, -8L))