如何使用R与dplyr按购车预算条件合并两个数据集

我们需要按照车辆价格≥用户预算的70%且≤用户预算的规则，将UserBudget和AutoMarket两个数据集进行匹配合并，得到符合条件的用户-车辆对应关系。

原始数据集 #

# 用户预算数据集
UserBudget <- data.frame(user = c("Jon", "Bill", "Maria", "Ben", "Tina"),
                         budget = c(5000, 10000, 20000, 40000, 60000))

# 车辆市场数据集
AutoMarket <- data.frame(
    car = c("Toyota Corolla (2000)", "Ford Focus (2002)", "Hyundai Elantra (2012)", 
            "Kia Forte (2014)", "Mercedes-Benz c250 (2014)","BMW 320I (2016)",
            "Tesla 3 (2018)", "Audi A6 (2019)", "Porsche Macan S (2019)", 
            "Mercedes-Benz c63 AMG (2017)", "Lexus RX 450L (2020)","BMW 740 (2020)","JAGUAR I-Place (2019)"),
    price = c(2500, 5000, 9900, 9999, 18500, 20000, 37000, 41000, 50000, 54000,55000,59000,60000)
)

实现方法 #

方法1：基础R原生实现

先生成所有用户-车辆的交叉组合，再筛选符合预算条件的记录：

# 创建全量用户-车辆组合
full_combinations <- merge(UserBudget, AutoMarket, all = TRUE)
# 过滤符合预算规则的条目
HappyDriver <- subset(full_combinations, price >= 0.7 * budget & price <= budget)
# 重置行名避免混乱
rownames(HappyDriver) <- NULL

方法2：dplyr工具链实现

如果习惯使用tidyverse生态，用交叉连接加过滤的方式更简洁：

library(dplyr)

HappyDriver <- cross_join(UserBudget, AutoMarket) %>%
  filter(price >= 0.7 * budget, price <= budget) %>%
  relocate(user, budget, car, price) # 调整列顺序与期望结果一致

结果验证 #

运行上述代码后，得到的HappyDriver与期望数据集完全匹配，输出示例：

print(HappyDriver)

输出内容：

user budget                    car price
1    Jon   5000      Ford Focus (2002)  5000
2   Bill  10000   Hyundai Elantra (2012)  9900
3   Bill  10000        Kia Forte (2014)  9999
4  Maria  20000 Mercedes-Benz c250 (2014) 18500
5  Maria  20000          BMW 320I (2016) 20000
6    Ben  40000           Tesla 3 (2018) 37000
7   Tina  60000    Porsche Macan S (2019) 50000
8   Tina  60000 Mercedes-Benz c63 AMG (2017) 54000
9   Tina  60000      Lexus RX 450L (2020) 55000
10  Tina  60000            BMW 740 (2020) 59000
11  Tina  60000       JAGUAR I-Place (2019) 60000

内容的提问来源于stack exchange，提问作者Arty Art