Look up table based on integer values

Question

Consider the following dataframe:

lookup <- 
data.frame(
level = 1:5,
points_needed = c(150, 250, 420, 580, 620)
)

lookup

  level points_needed
1     1           150
2     2           250
3     3           420
4     4           580
5     5           620

Now consider the following observations:

data <-
data.frame(
person_name = c("Bob", "Bob", "Bob", "Jim", "Jim"),
match = c(1, 2, 3, 1, 2),
total_points = c(100, 300, 430, 200, 600)
)

data
  person_name match total_points
1         Bob     1          100
2         Bob     2          300
3         Bob     3          430
4         Jim     1          200
5         Jim     2          600

I would like to create new columns in data. This column would indicate the level of the person_name - this changes based on the total_points in relation to the lookup table. Desired result:

data
  person_name match total_points level
1         Bob     1          100     0
2         Bob     2          300     2
3         Bob     3          430     3
4         Jim     1          200     1
5         Jim     2          600     5

I can't use a join here since there's nothing to join on. Any suggestions?

manotheshark · Accepted Answer · 2020-02-03 16:40:51Z

3

This uses findInterval, using the actual level isn't required since it is sorted and continuous

data$level <- findInterval(data$total_points, lookup$points_needed)

> data
  person_name match total_points level
1         Bob     1          100     0
2         Bob     2          300     2
3         Bob     3          430     3
4         Jim     1          200     1
5         Jim     2          600     4

Minor note that the level for the last row appears to be 4

Adding text label to lookup table

lookup <- 
  data.frame(
    level = 1:5,
    points_needed = c(150, 250, 420, 580, 620),
    label = c("blue", "green", "yellow", "orange", "red")
  )

data$level <- findInterval(data$total_points, lookup$points_needed)
data$level2[data$level > 0] <- as.character(lookup$label[data$level])

> data
  person_name match total_points level level2
1         Bob     1          100     0   <NA>
2         Bob     2          300     2  green
3         Bob     3          430     3 yellow
4         Jim     1          200     1   blue
5         Jim     2          600     4 orange

Using as.character wouldn't be required if the labels weren't saved as factors

edited Feb 3, 2020 at 16:40

answered Feb 3, 2020 at 16:16

manotheshark

4,36520 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Phillip Black Over a year ago

Let's pretend level was a string value c("blue", "green", "yellow", "orange"). How does the solution change?

manotheshark Over a year ago

@PhillipBlack Added string labels. It's possible to write over the original column, but a second column was created to make it easier to confirm results.

Collectives™ on Stack Overflow

Look up table based on integer values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related