Incrementing grouped identifiers

Question

I have example data as follows:

library(data.table)
dat <- fread("Survey Variable_codes_2022
              D       D1
              A       A1
              B       B1
              B       B3
              B       B2
              E       E1
              B       NA
              E       NA")

For the two rows that have Variable_codes_2022==NA, I would like to increment the variable code so that it becomes:

dat <- fread("Survey Variable_codes_2022
              D       D1
              A       A1
              B       B1
              B       B3
              B       B2
              E       E1
              B       B4
              E       E2"

Because the column Variable_codes_2022 is a string variable, the numbers are not in numerical order.

I have no idea where to start and I was wondering if someone could help me on the right track.

Answer 1

We could do it this way:

1. grouping

2. arranging and

3. mutate.

To keep the original order we could first create and id and then rearrange:

library(dplyr)

dat %>% 
  group_by(Survey) %>% 
  arrange(.by_group = TRUE) %>% 
  mutate(Variable_codes_2022 = paste0(Survey, row_number()))

  Survey Variable_codes_2022
  <chr>  <chr>              
1 A      A1                 
2 B      B1                 
3 B      B2                 
4 B      B3                 
5 B      B4                 
6 D      D1                 
7 E      E1                 
8 E      E2 

Answer 2

data.table option using rleid like this:

library(data.table)
dat[, Variable_codes_2022 := paste0(Survey, rleid(Variable_codes_2022)), by = Survey]
dat
#>    Survey Variable_codes_2022
#> 1:      D                  D1
#> 2:      A                  A1
#> 3:      B                  B1
#> 4:      B                  B2
#> 5:      B                  B3
#> 6:      E                  E1
#> 7:      B                  B4
#> 8:      E                  E2

^{Created on 2022-12-01 with reprex v2.0.2}