如何在DF1不满足条件时，用DF2替换其Price与Points字段值？

需求概述 #

当DF1中某行的Points值与指定基准值（示例为6.5）不匹配时，将该行的Price和Points替换为DF2中匹配ID、Book、OU且Points等于基准值的对应字段值，最终得到目标数据框DF3。

示例数据 #

DF1生成代码与结构 #

DF1 <- structure(list(
  ID = c("ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1"),
  Book = c("Book_A", "Book_A", "Book_B", "Book_B", "Book_C", "Book_C", "Book_D", "Book_D", "Book_E", "Book_E"),
  OU = c("Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under", "Over", "Under"),
  Price = c(102, -114, 100, -120, 102, -113, -102, -119, -120, 105),
  Points = c(6.5, 6.5, 6.5, 6.5, 6.5, 6.5, 6.5, 6.5, 6, 6)
), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

DF1结构：

# A tibble: 10 × 5
ID    Book   OU    Price Points
<chr> <chr>  <chr> <dbl>  <dbl>
1 ID_1  Book_A Over    102    6.5
2 ID_1  Book_A Under  -114    6.5
3 ID_1  Book_B Over    100    6.5
4 ID_1  Book_B Under  -120    6.5
5 ID_1  Book_C Over    102    6.5
6 ID_1  Book_C Under  -113    6.5
7 ID_1  Book_D Over   -102    6.5
8 ID_1  Book_D Under  -119    6.5
9 ID_1  Book_E Over   -120    6  
10 ID_1  Book_E Under   105    6 

DF2生成代码与结构 #

DF2 <- structure(list(
  ID = c("ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1", "ID_1"),
  Book = c("Book_E", "Book_E", "Book_E", "Book_E", "Book_E", "Book_E", "Book_E", "Book_E", "Book_E", "Book_E"),
  OU = c("Over", "Over", "Over", "Over", "Over", "Under", "Under", "Under", "Under", "Under"),
  Price = c(-303, -150, 107, 176, 372, 261, 133, -120, -200, -447),
  Points = c(5, 5.5, 6.5, 7, 8.5, 5, 5.5, 6.5, 7, 8.5)
), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

DF2结构：

# A tibble: 10 × 5
ID    Book   OU    Price Points
<chr> <chr>  <chr> <dbl>  <dbl>
1 ID_1  Book_E Over   -303    5  
2 ID_1  Book_E Over   -150    5.5
3 ID_1  Book_E Over    107    6.5
4 ID_1  Book_E Over    176    7  
5 ID_1  Book_E Over    372    8.5
6 ID_1  Book_E Under   261    5  
7 ID_1  Book_E Under   133    5.5
8 ID_1  Book_E Under  -120    6.5
9 ID_1  Book_E Under  -200    7  
10 ID_1  Book_E Under  -447    8.5

目标结果（DF3） #

# A tibble: 10 × 5
ID    Book   OU    Price Points
<chr> <chr>  <chr> <dbl>  <dbl>
1 ID_1  Book_A Over    102    6.5
2 ID_1  Book_A Under  -114    6.5
3 ID_1  Book_B Over    100    6.5
4 ID_1  Book_B Under  -120    6.5
5 ID_1  Book_C Over    102    6.5
6 ID_1  Book_C Under  -113    6.5
7 ID_1  Book_D Over   -102    6.5
8 ID_1  Book_D Under  -119    6.5
9 ID_1  Book_E Over    107    6.5  
10 ID_1  Book_E Under  -120   6.5 

通用解决方案 #

使用dplyr包实现动态替换逻辑，适用于每次API返回的新数据：

library(dplyr)

# 1. 定义基准Points值，可根据实际场景动态修改
base_points <- 6.5

# 2. 从DF2中提取基准Points对应的参考数据（匹配ID/Book/OU的替换值）
df2_reference <- DF2 %>%
  filter(Points == base_points) %>%
  select(ID, Book, OU, ref_Price = Price, ref_Points = Points)

# 3. 处理DF1，替换不符合基准值的行
DF3 <- DF1 %>%
  # 关联参考数据
  left_join(df2_reference, by = c("ID", "Book", "OU")) %>%
  # 替换逻辑：仅当原Points不等于基准值时，用参考值覆盖
  mutate(
    Price = if_else(Points != base_points, ref_Price, Price),
    Points = if_else(Points != base_points, ref_Points, Points)
  ) %>%
  # 移除临时关联字段
  select(-ref_Price, -ref_Points)

方案说明 #

• 动态适配：只需修改base_points变量即可适配不同基准值需求，无需调整核心逻辑
• 自动匹配：通过left_join确保仅替换ID/Book/OU完全匹配的行，避免错误替换
• 容错处理：若DF2中无对应匹配项，会保留DF1原数据（如需处理缺失情况，可添加coalesce或默认值逻辑）

内容的提问来源于stack exchange，提问作者Aaron Morris