0

I'm trying to create a unique ID number but I want to group by two different variables. The PVC value can potentially be in both years, but I still want them to have different ID values because they stand for different individuals.

Here is what I have already tried.

individual <- example %>%
  group_by(PVC, Year) %>%
  mutate(ID = row_number())


 PVC     Year     ID
   <chr>   <chr> <int>
 1 24N     2020      1
 2 24N     2020      2
 3 532     2020      1
 4 532     2020      2
 5 532     2020      3
 6 6144403 2020      1
 7 6144403 2020      2
 8 6144403 2020      3
 9 619763- 2020      1
10 694     2020      1

I want it instead to look like:

 PVC     Year     ID
   <chr>   <chr> <int>
 1 24N     2020      1
 2 24N     2020      1
 3 532     2020      2
 4 532     2020      2
 5 532     2020      2
 6 6144403 2020      3
 7 6144403 2020      3
 8 6144403 2020      3
 9 24N     2021      4

my data:

> dput(example)
structure(list(PVC = c("24N", "24N", "532", "532", "532", "6144403", 
"6144403", "6144403", "619763-", "694", "694", "694", "AA2X", 
"AA2X", "AA2X", "AB96", "AB96", "AB96", "AD1J", "AD1J", "AD1J", 
"ALB4", "ALB4", "ALB4", "AMU4", "ANUA", "ANUA", "ANUA", "AR17", 
"AR17", "AR17", "AR2X", "AR76", "AR76", "AR76", "AR7H", "AS1J", 
"AS2R", "AS2R", "AS2R", "ATZ5", "ATZ5", "ATZ5", "AUMD", "AW0F", 
"AWJR", "AWJR", "AWJR", "AYLM", "AYN5", "AYN5", "AYN5", "AYVV", 
"AZ2B", "AZ2B", "AZT9", "AZT9", "AZT9", "BA90", "BA90", "BA90", 
"BBHP", "BBHP", "BC78", "BHDP", "BHDT", "BHDY", "BL0F", "BL0F", 
"BMP4", "BMP4", "BMP4", "BN1X", "BN1X", "BN1X", "BN22", "BN22", 
"BNSR", "BNSR", "BNSR", "BPS4", "BPYY", "BPYY", "BPYY", "BS2W", 
"BS2W", "BS2W", "BSAT", "BSAT", "BT0Y", "BT1J", "BT1J", "BT1J", 
"BT5R", "BT5R", "BT5R", "BTPJ", "BTPJ", "BTYY", "BUDC", "BUDD", 
"BUDD", "BUDD", "BUJJ", "BUJJ", "BUTT", "BUTT", "BUTT", "BV0Z", 
"BV0Z", "BV0Z", "BV7W", "BV7W", "BVUA", "BVUA", "BVUA", "BVW0", 
"BVW0", "BVW0", "BW0C", "BW0C", "BW0C", "BW4L", "BW4L", "BWY5", 
"BWY5", "BWY5", "BX1F", "BX6V", "BX6V", "BX93", "BX93", "BX9Z", 
"BXC1", "BXC1", "BXC1", "BXFD", "BXFD", "BXFD", "BXHF", "BXHF", 
"BXHF", "BXMU", "BYBC", "BYW8", "BYW8", "BZ0X", "BZ39", "BZ73", 
"BZ7B", "BZ8R", "BZ9D", "BZ9U", "CDD8", "CLF1", "CLF1", "CLF1", 
"CLF2", "CLF2", "CLF2", "CLF3", "CLF3", "CLF3", "CLF4", "CLF4", 
"CLF5", "CLF5", "CLF5", "CLF6", "CLF6", "CLF6", "UZJ", "UZJ", 
"UZJ", "24N", "AA2N", "AC9F", "AFUD", "AL8F", "ALC5", "ALTM", 
"APXR", "AR4F", "AR72", "AR7H", "AR87", "ATAL", "ATDF", "ATFR", 
"ATHH", "AUMD", "AUSD", "AV8J", "AVFC", "AVRJ", "AVV9", "AW0F", 
"AWAX", "AZUU", "BA0X", "BBPC", "BCZ8", "BMA2", "BMWZ", "BN22", 
"BNSL", "BNU6", "BPV7", "BRP8", "BT49", "BTRJ", "BTWN", "BUA7", 
"BUB9", "BV11", "BV12", "BVVY", "BVW0", "BVWP", "BW4N", "BW8P", 
"BWCJ", "BWCX", "BWSZ", "BWYN", "BX3X", "BX9Z", "BXPL", "BXPV", 
"BXWR", "BXX2", "BXZ1", "BYNF", "BYNR", "BYXF", "BZ4L", "BZ6T", 
"BZ7Z", "BZ8H", "BZF4", "BZR0", "BZWY", "BZZW", "CA0D", "CA0N", 
"CA1H", "CAM5", "CAN8", "CANM", "CATA", "CAUD", "CAUF", "CAV0", 
"CAWM", "CC37", "CCS3", "CCSS", "CDDW", "CDFH", "CDHD", "CDZN", 
"CFC7", "CFM4", "CH6S", "CH8S", "ZX9", NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
), Year = c("2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021")), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -416L), groups = structure(list(Year = c("2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2020", "2020", "2020", "2020", "2020", "2020", "2020", 
"2020", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021"), PVC = c("24N", 
"532", "6144403", "619763-", "694", "AA2X", "AB96", "AD1J", "ALB4", 
"AMU4", "ANUA", "AR17", "AR2X", "AR76", "AR7H", "AS1J", "AS2R", 
"ATZ5", "AUMD", "AW0F", "AWJR", "AYLM", "AYN5", "AYVV", "AZ2B", 
"AZT9", "BA90", "BBHP", "BC78", "BHDP", "BHDT", "BHDY", "BL0F", 
"BMP4", "BN1X", "BN22", "BNSR", "BPS4", "BPYY", "BS2W", "BSAT", 
"BT0Y", "BT1J", "BT5R", "BTPJ", "BTYY", "BUDC", "BUDD", "BUJJ", 
"BUTT", "BV0Z", "BV7W", "BVUA", "BVW0", "BW0C", "BW4L", "BWY5", 
"BX1F", "BX6V", "BX93", "BX9Z", "BXC1", "BXFD", "BXHF", "BXMU", 
"BYBC", "BYW8", "BZ0X", "BZ39", "BZ73", "BZ7B", "BZ8R", "BZ9D", 
"BZ9U", "CDD8", "CLF1", "CLF2", "CLF3", "CLF4", "CLF5", "CLF6", 
"UZJ", "24N", "AA2N", "AC9F", "AFUD", "AL8F", "ALC5", "ALTM", 
"APXR", "AR4F", "AR72", "AR7H", "AR87", "ATAL", "ATDF", "ATFR", 
"ATHH", "AUMD", "AUSD", "AV8J", "AVFC", "AVRJ", "AVV9", "AW0F", 
"AWAX", "AZUU", "BA0X", "BBPC", "BCZ8", "BMA2", "BMWZ", "BN22", 
"BNSL", "BNU6", "BPV7", "BRP8", "BT49", "BTRJ", "BTWN", "BUA7", 
"BUB9", "BV11", "BV12", "BVVY", "BVW0", "BVWP", "BW4N", "BW8P", 
"BWCJ", "BWCX", "BWSZ", "BWYN", "BX3X", "BX9Z", "BXPL", "BXPV", 
"BXWR", "BXX2", "BXZ1", "BYNF", "BYNR", "BYXF", "BZ4L", "BZ6T", 
"BZ7Z", "BZ8H", "BZF4", "BZR0", "BZWY", "BZZW", "CA0D", "CA0N", 
"CA1H", "CAM5", "CAN8", "CANM", "CATA", "CAUD", "CAUF", "CAV0", 
"CAWM", "CC37", "CCS3", "CCSS", "CDDW", "CDFH", "CDHD", "CDZN", 
"CFC7", "CFM4", "CH6S", "CH8S", "ZX9", NA), .rows = structure(list(
    1:2, 3:5, 6:8, 9L, 10:12, 13:15, 16:18, 19:21, 22:24, 25L, 
    26:28, 29:31, 32L, 33:35, 36L, 37L, 38:40, 41:43, 44L, 45L, 
    46:48, 49L, 50:52, 53L, 54:55, 56:58, 59:61, 62:63, 64L, 
    65L, 66L, 67L, 68:69, 70:72, 73:75, 76:77, 78:80, 81L, 82:84, 
    85:87, 88:89, 90L, 91:93, 94:96, 97:98, 99L, 100L, 101:103, 
    104:105, 106:108, 109:111, 112:113, 114:116, 117:119, 120:122, 
    123:124, 125:127, 128L, 129:130, 131:132, 133L, 134:136, 
    137:139, 140:142, 143L, 144L, 145:146, 147L, 148L, 149L, 
    150L, 151L, 152L, 153L, 154L, 155:157, 158:160, 161:163, 
    164:165, 166:168, 169:171, 172:174, 175L, 176L, 177L, 178L, 
    179L, 180L, 181L, 182L, 183L, 184L, 185L, 186L, 187L, 188L, 
    189L, 190L, 191L, 192L, 193L, 194L, 195L, 196L, 197L, 198L, 
    199L, 200L, 201L, 202L, 203L, 204L, 205L, 206L, 207L, 208L, 
    209L, 210L, 211L, 212L, 213L, 214L, 215L, 216L, 217L, 218L, 
    219L, 220L, 221L, 222L, 223L, 224L, 225L, 226L, 227L, 228L, 
    229L, 230L, 231L, 232L, 233L, 234L, 235L, 236L, 237L, 238L, 
    239L, 240L, 241L, 242L, 243L, 244L, 245L, 246L, 247L, 248L, 
    249L, 250L, 251L, 252L, 253L, 254L, 255L, 256L, 257L, 258L, 
    259L, 260L, 261L, 262L, 263L, 264L, 265L, 266L, 267:416), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), row.names = c(NA, -175L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE))
Cassidy
  • 395
  • 1
  • 11
  • 3
    Use `cur_group_id` instead of `row_number` – Jonathan Jun 12 '22 at 15:20
  • See [this answer](https://stackoverflow.com/questions/6112803/how-to-create-a-consecutive-group-number) for various options, including `cur_group_id` as suggested... – Ben Jun 12 '22 at 20:25

1 Answers1

0

data.table option using the .GRP function:

library(data.table)
setDT(example)[, ID := .GRP, by = list(PVC, Year)]

Output:

      PVC Year  ID
  1:  24N 2020   1
  2:  24N 2020   1
  3:  532 2020   2
  4:  532 2020   2
  5:  532 2020   2
 ---              
412: <NA> 2021 175
413: <NA> 2021 175
414: <NA> 2021 175
415: <NA> 2021 175
416: <NA> 2021 175
Quinten
  • 35,235
  • 5
  • 20
  • 53