Cross joining for the computation of a new variable

Question

I have a game data set and I observe the number of points of one player.

da = data.frame(points = c(144,186,220,410,433))

da                
  points
1    144
2    186
3    220
4    410
5    433

I also now, in which the level the player was, because I know the ranges of points for different levels.

ranges = data.frame(level = c(1,2,3,4,5), points_from = c(0,100,200,300,430), points_to = c(100,170,300,430,550))

ranges
  level points_from points_to
1     1           0       100
2     2         100       170
3     3         200       300
4     4         300       430
5     5         430       550

Now I want to compute a new variable, that indicates how far away the player was from the next level. It is computed by da$points/ranges$points_to of this specific level.

For example, if the player has 144 points and the next elvel is reached when achieving 170 points, the levle progress is 144/170.

Thus, the data set I want to have looks like this:

da_new = data.frame(points = c(144,186,220,410,433), points_to = c(170,300,300,430,550), level_progress = c(144/170,186/300,220/300,410/430,433/550))

da_new
  points points_to level_progress
1    144       170         0.8471
2    186       300         0.6200
3    220       300         0.7333
4    410       430         0.9535
5    433       550         0.7873

How can I now compute this variable?

how do you know which level 186 is in? the range goes from 100-170 to 200-300 — bouncyball, Apr 02 '20 at 14:30

score 3 · Answer 1 · answered Apr 02 '20 at 14:32

Here is a base R solution using findInterval

da_new <- da
da_new$points_to <- ranges$points_to[findInterval(da_new$points,c(0,ranges$points_to))]
da_new$level_progress <- da_new$points/da_new$points_to

such that

> da_new
  points points_to level_progress
1    144       170      0.8470588
2    186       300      0.6200000
3    220       300      0.7333333
4    410       430      0.9534884
5    433       550      0.7872727

bouncyball · Accepted Answer · 2020-04-02T14:52:19.367

The main idea is to use merge(da, ranges, all = T) to do a "cross join" between the data. Then, we filter to where points is between points_from and points_to (meaning 186 is not in the final data).

library(dplyr)
merge(da, ranges, all = T) %>%
    # keep only where points fall between points_from and points_to
    filter(points >= points_from & points <= points_to) %>%
    mutate(level_progress = points / points_to)

  points level points_from points_to level_progress
1    144     2         100       170      0.8470588
2    220     3         200       300      0.7333333
3    410     4         300       430      0.9534884
4    433     5         430       550      0.7872727

Another option is to filter where points <= point_to, and find where points is closest to points_to (this method keeps 186):

merge(da, ranges, all = T) %>%
    filter(points <= points_to) %>%
    group_by(points) %>%
    slice(which.min(abs(points - points_to))) %>%
    mutate(level_progress = points / points_to)

  points level points_from points_to level_progress
   <dbl> <dbl>       <dbl>     <dbl>          <dbl>
1    144     2         100       170          0.847
2    186     3         200       300          0.62 
3    220     3         200       300          0.733
4    410     4         300       430          0.953
5    433     5         430       550          0.787

Thank you, I like dplyr the most, so this is a very helpful solution! — Scijens, Apr 02 '20 at 14:44

Cross joining for the computation of a new variable

2 Answers2