CODEWITHSUNDEEP
rdataframe
Matthew Crews
Matthew Crews

Reputation: 4305

R Looking up closest value in data.frame less than equal to another value

I have two data.frames, lookup_df and values_df. For each row in lookup_df I want to lookup the closest value in the values_df that is less than or equal to an index value.

Here's my code so far:

lookup_df <- data.frame(ids = 1:10)
values_df <- data.frame(idx = c(1,3,7), values = c(6,2,8))

What I'm wanting for the result_df is the following:

> result_df
   ids values
1    1      6
2    2      6
3    3      2
4    4      2
5    5      2
6    6      2
7    7      8
8    8      8
9    9      8
10  10      8

I know how to do this with SQL fairly easily but I'm curious if there is an R way that is straightforward. I could iterate the the rows of the lookup_df and then loop through the rows of the values_df but that is not computationally efficient. I'm open to using dplyr library if someone knows how to use that to solve the problem.

Upvotes: 2

Views: 1508

Answers (1)

Chris Holbrook
Chris Holbrook

Reputation: 2636

If values_df is sorted by idx ascending, then findInterval will work:

lookup_df <- data.frame(ids = 1:10)
values_df <- data.frame(idx = c(1,3,7), values = c(6,2,8))
lookup_df$values <- values_df$values[findInterval(lookup_df$ids,values_df$idx)]
lookup_df

>   ids values
 1    1      6
 2    2      6
 3    3      2  
 4    4      2
 5    5      2
 6    6      2
 7    7      8
 8    8      8
 9    9      8
 10  10      8

Upvotes: 2

Related Questions