Rolling index for semi-unique values in two columns R

user_9974638 0
Viewed 44

I'm looking to add a rolling ID column. Here's an example of what the dataframe looks like.

df <- data.frame("participant" = c("a", "a", "a", "a", "b", "b", "b", "b", "c", "c"), 
                 "item" = c("X", "X", "Y", "X", "X", "X", "Y", "Z", "Z", "Z"))

Here's what I'm trying to do (the item_ID column), but there are 2 tricky things:

  1. The items are non-unique, so the solution needs to go row by row assigning a new number at each new value (e.g. participant 'a' has two 'X' items, item_ID = 1 & 3).

  2. The last item for one participant might be identical to the first item of the next participant (e.g. see items 'X' in participant 'a' and 'b'); these need to get unique IDs. Any ideas? Really appreciative of any and all help!

       participant item  item_ID
1            a    X        1
2            a    X        1
3            a    Y        2
4            a    X        3
5            b    X        4
6            b    X        4
7            b    Y        5
8            b    Z        6
9            c    Z        7
10           c    Z        7
r indexing
2 Answers

Method-1 baseR way

df <- data.frame("participant" = c("a", "a", "a", "a", "b", "b", "b", "b", "c", "c"), 
                 "item" = c("X", "X", "Y", "X", "X", "X", "Y", "Z", "Z", "Z"))

transform(df, item_id = with(rle(paste(participant, item)), rep(seq_len(length(lengths)), lengths)))
#>    participant item item_id
#> 1            a    X       1
#> 2            a    X       1
#> 3            a    Y       2
#> 4            a    X       3
#> 5            b    X       4
#> 6            b    X       4
#> 7            b    Y       5
#> 8            b    Z       6
#> 9            c    Z       7
#> 10           c    Z       7

Created on 2021-05-21 by the reprex package (v2.0.0)

Method-2 data.table::rleid()

df <- data.frame("participant" = c("a", "a", "a", "a", "b", "b", "b", "b", "c", "c"), 
                 "item" = c("X", "X", "Y", "X", "X", "X", "Y", "Z", "Z", "Z"))
library(data.table)
library(tidyverse)
df %>% mutate(item_id = rleid(participant, item))
#>    participant item item_id
#> 1            a    X       1
#> 2            a    X       1
#> 3            a    Y       2
#> 4            a    X       3
#> 5            b    X       4
#> 6            b    X       4
#> 7            b    Y       5
#> 8            b    Z       6
#> 9            c    Z       7
#> 10           c    Z       7

Created on 2021-05-21 by the reprex package (v2.0.0)

user_28848590 
tmp=rle(paste(df$participant,df$item))
df$item_id=rep(1:length(tmp$lengths),tmp$lengths)

   participant item item_id
1            a    X       1
2            a    X       1
3            a    Y       2
4            a    X       3
5            b    X       4
6            b    X       4
7            b    Y       5
8            b    Z       6
9            c    Z       7
10           c    Z       7
user_29749510
Related