R statistics, change ranked tables to paired

Question

I have data for many tables like:

event_id   player   finish
1          a        1
1          b        2
1          c        3
1          d        4
2          b        1
2          e        2
2          f        3
2          a        3
2          g        5

Many event_id's, each from 5 to 20 players, finish may be tied.

In order to use the PlayerRatings package in R I would like to reformat the tables to be like:

event_id   player1   player2 result
1          a         b       1
1          a         c       1
1          a         d       1
1          b         c       1
1          b         d       1
1          c         d       1
2          b         e       1
2          b         f       1
2          b         a       1
2          b         g       1
2          e         f       1
2          e         a       1
2          e         g       1
2          f         a       0.5
2          f         g       1
2          a         g       1

An event_id of 4 players will have 4*3/2 = 6 records in the new table, 5 players will have 5*4/2 = 10 records and so on. If player "a" has "finish" less than player "b" the "result" is 1. If "finish" is equal the "result" is 0.5. If player "a" has finish greater than player "b" then the "result" would be 0.

Any help appreciated!

Answer 1

Here a data.table solution. I am using it for the grouping and syntax features. The code is a little bit complicated so I give here the idea.

1. group per event_id
2. for each event, create a combinations of player , suing combn
3. for each combinations of player computer the finish score using a nested ifelse

Here the whole code:

library(data.table)
DT <- as.data.table(dat)
DT[,{ids <- do.call(rbind,combn(seq_along(player),2,simplify=FALSE))
      z <- mapply(function(x,y){
            z <- ifelse(finish[x]>finish[y],0,
                   ifelse(finish[x]<finish[y],1,0.5))
            data.frame(player[x],player[y],z)
            },
            ids[,1],
            ids[,2])
      data.frame(t(z))

     },event_id]

    event_id player.x. player.y.   z
 1:        1         a         b   1
 2:        1         a         c   1
 3:        1         a         d   1
 4:        1         b         c   1
 5:        1         b         d   1
 6:        1         c         d   1
 7:        2         b         e   1
 8:        2         b         f   1
 9:        2         b         a   1
10:        2         b         g   1
11:        2         e         f   1
12:        2         e         a   1
13:        2         e         g   1
14:        2         f         a 0.5
15:        2         f         g   1
16:        2         a         g   1

Answer 2

Here is a merge solution: The second line is all it's really about.

a<-data.frame(event_id=c(1,1,1,1,1,2,2,2,2,2,2),player=letters[c(1:5,3:8)],finish=c(1,1,3:5,1:6))
b<-merge(a,a,by.x="event_id",by.y="event_id",suffixes = c(".x",".y"))
b$score<-b$finish.x<b$finish.y
b$score[b$finish.x==b$finish.y]<-0.5
c<-b[b$player.x!=b$player.y & as.character(b$player.x)<as.character(b$player.y),c("event_id","player.x","player.y","score")]