在R中基于另一张表的双条件生成新列的实现方法

需求：现有两个数据框df1和df2，需要给df1新增grade列——当df1的value大于df2中对应team和gender的point值时标记为"Yes"，否则标记为"No"，需同时匹配team与gender条件。

原始数据集：

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))

方法一：使用tidyverse工具集（dplyr + tidyr） #

这种方法更直观，适合处理数据格式转换和连接操作：

library(dplyr)
library(tidyr)

# 将df2从宽格式转换为长格式，统一gender和point列
df2_long <- df2 %>%
  pivot_longer(
    cols = ends_with("_point"), 
    names_to = "gender", 
    names_prefix = "(.)_point", 
    values_to = "point"
  )

# 连接两个数据框，生成grade列
result <- df1 %>%
  left_join(df2_long, by = c("team", "gender")) %>%
  mutate(grade = ifelse(value > point, "Yes", "No")) %>%
  select(ID, gender, value, team, grade) # 调整列顺序与期望输出一致

print(result)

方法二：使用Base R #

如果不想加载额外包，可以用Base R的函数实现：

# 转换df2为长格式
df2_long <- reshape(
  df2, 
  varying = c("M_point", "F_point"),
  v.names = "point",
  timevar = "gender",
  times = c("M", "F"),
  direction = "long"
)
row.names(df2_long) <- NULL
df2_long <- df2_long[, c("team", "gender", "point")]

# 合并数据并生成grade列
result_base <- merge(df1, df2_long, by = c("team", "gender"), all.x = TRUE)
result_base$grade <- ifelse(result_base$value > result_base$point, "Yes", "No")

# 调整列顺序并按原始ID排序
result_base <- result_base[, c("ID", "gender", "value", "team", "grade")]
result_base <- result_base[order(match(result_base$ID, LETTERS[1:8])), ]
row.names(result_base) <- NULL

print(result_base)

两种方法都能得到符合需求的结果：

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

内容的提问来源于stack exchange，提问作者Alegría