Creating new id by group of two columns with possibility of the same values

Question

I would appreciate some help I am stuck. I would like to create a new id based on two columns where the last column can have the same value. I have to maintain the current order which is done by a third column ORDER.

    df <- data.frame(id = c(1, 1, 1, 1, 1, 1,1,2,2,2),order=c(1,2,3,4,5,6,7,1,2,3), 
value = c(999, 999, 999, 250, 250, 999,999,250,250,999))

   id order value
1   1     1   999
2   1     2   999
3   1     3   999
4   1     4   250
5   1     5   250
6   1     6   999
7   1     7   999
8   2     1   250
9   2     2   250
10  2     3   999

I would like to have

      id value counter
 1     1   999       1
 2     1   999       1
 3     1   999       1
 4     1   250       2
 5     1   250       2
 6     1   999       3
 7     1   999       3
 8     2   250       4
 9     2   250       4
10     2   999       5

Every bit of code I tried is grouping regardless of order.

Thanks

This question is asked a lot: https://stackoverflow.com/questions/12925063/numbering-rows-within-groups-in-a-data-frame — David J. Bosak, Oct 27 '20 at 13:23

score 2 · Answer 1 · answered Oct 27 '20 at 13:25

2

What you want is a run-length type id column. Try this

df$counter <- data.table::rleidv(df[, c("id", "value")])

answered Oct 27 '20 at 13:25

ekoam

8,744
1
9
22

s_baldur · Accepted Answer · 2020-10-27T13:51:29.933

2

CAVEAT: The rle() solution assumes the data is ordered.

Using rle():

df$counter <- with(rle(with(df, paste(id, value))), rep(seq_along(lengths), lengths))

Adding pipes for readability:

library(magrittr)
df$counter <- df %>% 
  {paste(.$id, .$value)} %>% 
  rle() %>% 
  with(rep(seq_along(lengths), lengths))

With data.table

library(data.table)
df$counter <- with(df, rleid(id, value))

edited Oct 27 '20 at 13:51

answered Oct 27 '20 at 13:34

s_baldur

29,441
4
36
69

Thanks, I will use base option with rle(). – Primož Dolžan Oct 27 '20 at 13:40
Data is ordered. – Primož Dolžan Oct 27 '20 at 13:57

Allan Cameron · Answer 3 · 2020-10-27T13:23:10.857

1

You could do

df$counter <- cumsum(!c(FALSE, 
                apply(head(df[c(1, 3)], -1) == tail(df[c(1, 3)], -1), 1, all)))

df[-2]
#>    id month counter
#> 1   1   999       1
#> 2   1   999       1
#> 3   1   999       1
#> 4   1   250       2
#> 5   1   250       2
#> 6   1   999       3
#> 7   1   999       3
#> 8   2   250       4
#> 9   2   250       4
#> 10  2   999       5

edited Oct 27 '20 at 13:23

answered Oct 27 '20 at 13:18

Allan Cameron

147,086
7
49
87

Duck · Answer 4 · 2020-10-27T13:27:28.963

Maybe try this with a loop:

#Loop
df$Var <- NA
df$Var[1] <- 1
for(i in 2:nrow(df))
{
  df$Var[i] <- ifelse(df$month[i]==df$month[i-1],df$Var[i-1],df$Var[i-1]+1)
}

Output:

df
   id order month Var
1   1     1   999   1
2   1     2   999   1
3   1     3   999   1
4   1     4   250   2
5   1     5   250   2
6   1     6   999   3
7   1     7   999   3
8   2     1   250   4
9   2     2   250   4
10  2     3   999   5

Creating new id by group of two columns with possibility of the same values

4 Answers4