Mutate new clumn based on another table with two conditions in R

I have two dataframe. I want to mutate new column to label "Yes" if value > df2_point. To label new column, it needs to consider two conditions of team and gender. Here is my dateframe:

df1 <- data.frame(ID = LETTERS[1:8],
                 gender = c("F", "M", "F", "F", "M", "M", "F", "M"),
                 value = c(25.2,31.1,21.5,18.6,27.77,18.52,18.52,26.3),
                 team = c("A","C","B","A","A","C","C","B"))

df2 <- data.frame(team = c("A", "B", "C"), 
                  M_point = c(17.6, 20.3, 24.5), 
                  F_point = c(17.0, 18.2, 23.7))

And I want to get like this :

 ID gender value team   grade
1  A      F 25.20    A  Yes (geder is F and team is A, need to according to df2 team A and F_point 17.0)
2  B      M 31.10    C  Yes (geder is M and team is C, need to according to df2 team C and M_point 24.5)
3  C      F 21.50    B  Yes (geder is F and team is B, need to according to df2 team B and F_point 18.2)
4  D      F 18.60    A  Yes
5  E      M 27.77    A  Yes
6  F      M 18.52    C  No
7  G      F 18.52    C  No
8  H      M 26.30    B  Yes

How can I achieve this in R? Thanks.

You can stack df2 to the long format, and merge it into df1.

library(dplyr)
library(tidyr)

df1 %>%
  left_join(
    pivot_longer(df2, -team, names_to = "gender", names_pattern = "(.)", values_to = "thres"),
    by = join_by(team, gender)
  ) %>%
  mutate(grade = if_else(value > thres, "Yes", "No")) %>%
  select(-thres)

#   ID gender value team grade
# 1  A      F 25.20    A   Yes
# 2  B      M 31.10    C   Yes
# 3  C      F 21.50    B   Yes
# 4  D      F 18.60    A   Yes
# 5  E      M 27.77    A   Yes
# 6  F      M 18.52    C    No
# 7  G      F 18.52    C    No
# 8  H      M 26.30    B   Yes

Here is a data.table with a non-equi join by reference, using a molten (i.e. long) version of df2.

library(data.table)
# set df's to data.table format
setDT(df1); setDT(df2)
# initialise grade column with "no" value
df1[, grade := "no"]
# update value to yes when necessairy
df1[melt(df2, id.vars = "team")[, gender := gsub("(.).*", "\", variable)],
    grade := "yes",
    on = .(team, gender, value > value)][]
#    ID gender value team grade
# 1:  A      F 25.20    A   yes
# 2:  B      M 31.10    C   yes
# 3:  C      F 21.50    B   yes
# 4:  D      F 18.60    A   yes
# 5:  E      M 27.77    A   yes
# 6:  F      M 18.52    C    no
# 7:  G      F 18.52    C    no
# 8:  H      M 26.30    B   yes

I prefer Darren Tsai's solution for it's shortness. Here is another - more explicit way to do it :

`library(tidyverse)
df3 <- df1 %>%
    left_join(df2, by = "team") %>%
    mutate(grade = ifelse(
      gender == "F" & team == "A" & value > F_point, "Yes",
      ifelse(
        gender == "M" & team == "C" & value > M_point, "Yes",
        ifelse(
          gender == "F" & team == "B" & value > F_point, "Yes",
          ifelse(
            value > ifelse(gender == "M", M_point, F_point), "Yes", "No"
          )
        )
      )
    )) 
    
print(df_3)` 
ID gender value team grade
A      F 25.20    A   Yes
B      M 31.10    C   Yes
C      F 21.50    B   Yes
D      F 18.60    A   Yes
E      M 27.77    A   Yes
F      M 18.52    C    No
G      F 18.52    C    No
H      M 26.30    B   Yes