Create ID within a group by two factors

user_3452294 0
Viewed 49

I have the following code:

set.seed(6)
players<-rep(c("bc","cd","ab","bd"), c(3,3,3,3))
game<-rep(rep(1:2,c(3,3)),2)
my_decs<-(c(0,0,0,0,0,4,0,0,0,0,0,9))
gamematrix<-data.frame(cbind(players,game))

   players game
1       bc    1
2       bc    1
3       bc    1
4       cd    2
5       cd    2
6       cd    2
7       ab    1
8       ab    1
9       ab    1
10      bd    2
11      bd    2
12      bd    2

Now, I want to create a new numbering based on both factors:

   players game player_id
1       bc    1         1
2       bc    1         1
3       bc    1         1
7       ab    1         2
8       ab    1         2
9       ab    1         2
4       cd    2         1
5       cd    2         1
6       cd    2         1
10      bd    2         2
11      bd    2         2
12      bd    2         2

That is, within each game the players are numbered from 1 to N

I tried to use dplyr:

gamematrix %>% 
      group_by(game) %>%
      mutate(player_id = group_indices(., players))

But apparently I am doing something wrong. Please help!

dplyr grouping r
2 Answers

We can use match on 'players' after grouping by 'game'

library(dplyr)
gamematrix %>% 
       group_by(game) %>%
       arrange(game) %>% 
       mutate(player_id = match(players, unique(players)))

-output

# A tibble: 12 x 3
# Groups:   game [2]
#   players game  player_id
#   <chr>   <chr>     <int>
# 1 bc      1             1
# 2 bc      1             1
# 3 bc      1             1
# 4 ab      1             2
# 5 ab      1             2
# 6 ab      1             2
# 7 cd      2             1
# 8 cd      2             1
# 9 cd      2             1
#10 bd      2             2
#11 bd      2             2
#12 bd      2             2

Or convert to factor with levels specified as unique values of 'players' after grouping by 'game' and then coerce the factor to integer with as.integer

gamematrix %>% 
   group_by(game) %>%
   mutate(player_id = as.integer(factor(players, levels = unique(players))))
user_37322710

Here might be another option

transform(
  gamematrix,
  player_id = as.numeric(ave(players,game,FUN = function(x) cumsum(!duplicated(x))))
)

giving

   players game player_id
1       bc    1         1
2       bc    1         1
3       bc    1         1
4       cd    2         1
5       cd    2         1
6       cd    2         1
7       ab    1         2
8       ab    1         2
9       ab    1         2
10      bd    2         2
11      bd    2         2
12      bd    2         2
user_121587570
Related