How to do a join and only select a specific row from a related Group?

Question

I don't know how to exactly phrase this Question. I hope it gets clearer when I provide you with an example:

Match <- data.frame(PlayerID=c(1,2,2,3,3), Date = c("01.04.2012","05.04.2012","20.04.2012","23.04.2012","30.04.2012"))     
Playerrating <- data.frame(PlayerID = c(1,1,1,2,2,2), Date= c("01.03.2012","02.04.2012","01.05.2012","01.03.2012","19.04.2012","25.04.2012"),Rating=c(64,71,55,59,73,81))

> Match
PlayerID         Date
1          01.04.2012
2          05.04.2012
2          20.04.2012
3          23.04.2012
3          30.04.2012

> Playerrating
PlayerID       Date   Rating
1          01.03.2012     64
1          02.04.2012     71
1          01.05.2012     55
2          01.03.2012     59
2          19.04.2012     73
2          25.04.2012     81

I want to assign to the Match table the right player rating from the Playerrating table, which ist the first rating for the specific player after the date specified in the Match table. In this case it should look like the following:

Match
PlayerID       Date   Rating
1         01.04.2012     71
2         05.04.2012     73
2         20.04.2012     81
3         23.04.2012     NA
3         30.04.2012     NA

Can anybode help me out with this problem?

(This is my first question here so please forgive me, if I have not phrased this very comprehensible)

Answer 1

You could do a join with data.table, using roll=-Inf to roll the next value back.

library( data.table )

First convert your data frames to data tables.

setDT(Match)
setDT(Playerrating)

Convert the dates to Date class so they'll be treated appropriately.

Match[ , Date := as.Date( Date, format = "%d.%m.%Y" ) ]
Playerrating[ , Date := as.Date( Date, format = "%d.%m.%Y" ) ]

Make sure the order is appropriate. This isn't necessary in your example, but is good practice.

setorder( Match, PlayerID, Date )
setorder( Playerrating, PlayerID, Date )

Do the join. Note the roll=-Inf. This is what brings the next available value into the join.

Playerrating[ Match, on = .( PlayerID, Date ), roll = -Inf ]

The result:

   PlayerID       Date Rating
1:        1 2012-04-01     71
2:        2 2012-04-05     73
3:        2 2012-04-20     81
4:        3 2012-04-23     NA
5:        3 2012-04-30     NA