CODEWITHSUNDEEP
r
warrenjb
warrenjb

Reputation: 25

Finding R data frame values between two points in a separate reference data frame

I have 2 dataframes in R:

df1:

Category Min Max ID
First 1 3 A
First 4 6 B
First 7 9 C
Second 100 300 D
Second 400 600 E
Second 700 900 F

DF2:

Name First Second
Alpha 5 200
Beta 8 500

I am trying to compare column values in DF2 for each category to their respective min/max in DF1 to create a new dataframe with values replaced by their ID from df1 (see below):

Desired Output:

Name First Second
Alpha B D
Beta C E

I have been experimenting with different functions but since the dataframes are structured differently with one being a data table and one being a reference table I'm thinking I may need a combination of functions and nested loops however I'm hoping there is an easier way. Sincerely appreciate any thoughts and assistance.

Upvotes: 0

Views: 96

Answers (1)

jkatam
jkatam

Reputation: 3447

Please try the below code

library(tidyverse)

df2 %>% pivot_longer(-Name) %>% left_join(df1, join_by(name==Category)) %>% 
  filter(between(value,Min,Max)) %>% 
  pivot_wider(Name, names_from = name, values_from = ID)

Created on 2023-08-12 with reprex v2.0.2

# A tibble: 2 × 3
  Name  First Second
  <chr> <chr> <chr> 
1 Alpha B     D     
2 Beta  C     E

Upvotes: 1

Related Questions